LOAD DATA and recovery
A little two-part quiz. If you get the first one without peeking, you're worth your pay as a DBA. If you get the second one without peeking, you may tell your boss that some random guy on the Internet says you deserve a raise.
Start with a text file, 'test.txt', with these three lines:
1
1
2
Set up the test in MySQL:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int primary key);
LOAD DATA INFILE 'test.txt' INTO TABLE t1;
Careful with those required files in Ruby
Getting Ruby to talk to MySQL was pretty easy in irb:
C:\>irb
irb(main):001:0> require 'rubygems'
=> true
irb(main):002:0> require 'mysql'
=> true
irb(main):003:0> db = Mysql.connect('localhost', 'root', 'password', 'test')
=> #<Mysql:0x2cc5270>
irb(main):004:0>
But then I tried the very same code from a file:
C:\>ruby mysql.rb
./mysql.rb:3: uninitialized constant Mysql (NameError)
from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
from C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
from mysql.rb:2
That require 'mysql' line looks in the current directory first ... and I'd used the name mysql.rb for the file I was coding ... so my code just included itself, and not the gem I wanted. Doh!
Another Parting Shot
Related to that last quiz. Here's the SHOW CREATE TABLE output - I've only cleaned up the whitespace.
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (100)
ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '~/mysql/data/'
ENGINE = MyISAM
) */
Now, pay close attention to the rows inserted:
mysql> INSERT INTO t1 VALUES (100); Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec)
Ok, here's the tricky bit:
mysql> SELECT * FROM t1; Empty set (0.00 sec)
No triggers, no BLACKHOLE, no Proxy, no events or other scheduled tasks, no other connections deleting rows, no temporary tables, and I have all the necessary privileges (including to the data directory shown, which exists and isn't full). It's all MyISAM, so surely no transactions. No errors or warnings have been reported.
When you figure it out, don't do it in your production environment. Bad things happen.
A Parting Shot
mysql> INSERT IGNORE INTO trickyTable (id) VALUES (1); Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM trickyTable; Empty set (0.00 sec)
That's odd - what happened to the row?
I have the necessary privileges:
mysql> SHOW GRANTS; +---------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' | +---------------------------------------------------+ 1 row in set (0.00 sec)
The table exists:
mysql> SHOW TABLES; +------------------------+ | Tables_in_example | +------------------------+ | trickytable | +------------------------+ 1 row in set (0.00 sec)
The field is there and adequately typed for the value:
mysql> DESCRIBE trickyTable; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.02 sec)
Not using BLACKHOLE, and no transaction tricks:
mysql> SELECT engine FROM information_schema.tables WHERE table_name = 'trickyTable'; +--------+ | engine | +--------+ | MyISAM | +--------+ 1 row in set (0.00 sec)
No triggers to make things tricky:
mysql> SELECT * FROM information_schema.triggers WHERE event_object_table = 'trickyTable'; Empty set (0.00 sec)
Just take my word for it that I'm not using Proxy, and this has nothing to do with replication.
Two for the price of one
Haven't done any sneaky puzzles in a while. How would you accomplish this?
mysql> CREATE TABLE t1 (id int); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.02 sec) mysql> SELECT COUNT(*) FROM t1; +----------+ | COUNT(*) | +----------+ | 2 | +----------+ 1 row in set (0.00 sec)
Views and Social Engineering
CREATE TABLE secretData ( secretValue int COMMENT 'If this goes over 5, WWIII will start' ); CREATE SQL SECURITY DEFINER VIEW censoredData AS SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION; GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';
<telephone> ring ring
<sysadmin> "Hello?"
<evilFiend> "I'd like to create an insertable view on some tables I already have rights to. I don't know just yet what I'll use for my select statement."
<sysadmin> "Ok. I'll set it up so you can do what you'd like."
CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1; GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';
evilFiend connects to the server, while twiddling the end of his handlebar mustache.
ALTER VIEW evilFiendsView AS SELECT * FROM censoredData WITH LOCAL CHECK OPTION; INSERT INTO evilFiendsView VALUES (42);
Muhahaha!
Messing with LAST_INSERT_ID()
Time for another MySQL Community Quiz:
Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,
INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42)); SELECT LAST_INSERT_ID();
The INSERT inserts '42' into the table, and the SELECT returns 42.
So, here's the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?
CREATE TABLE test (id int auto_increment primary key, field int); INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42)); SELECT LAST_INSERT_ID();
And for a follow up, does it matter if you swap the insertion around?
INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL); SELECT LAST_INSERT_ID();
The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.
Access Control Quiz
First, the setup:
CREATE TABLE `user` (
`user` varchar(255) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`sort` int(11) DEFAULT NULL
);
INSERT INTO `user`
(`user`, `host`, `sort`)
VALUES
('','%',8),
('testUser','%',7),
('','%localhost',9),
('testUser','%localhost',5),
('','%localhost%',10),
('testUser','%localhost%',6),
('','localhost',2),
('testUser','localhost',1),
('','localhost%',4),
('testUser','localhost%',3);
Now, the quiz:
SELECT * FROM user ORDER BY ___
Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.
+----------+-------------+------+ | user | host | sort | +----------+-------------+------+ | testUser | localhost | 1 | | | localhost | 2 | | testUser | localhost% | 3 | | | localhost% | 4 | | testUser | %localhost | 5 | | testUser | %localhost% | 6 | | testUser | % | 7 | | | % | 8 | | | %localhost | 9 | | | %localhost% | 10 | +----------+-------------+------+
Capital. Just capital.
Culled from the Certification Study Guide:
mysql> SELECT * FROM test; +----------------+ | data | +----------------+ | This is a test | +----------------+ 1 row in set (0.00 sec) mysql> SELECT UPPER(data) FROM test; +----------------+ | UPPER(data) | +----------------+ | This is a test | +----------------+ 1 row in set (0.03 sec)
How's that work?
Here Be Dragons
I have a table of maps:
CREATE TABLE map (
mapName varchar(255),
mapType ENUM('continent', 'region', 'country'),
continent varchar(255) NULL,
region varchar(255) NULL,
country varchar(255) NULL
);
INSERT INTO map VALUES
('Europe', 'continent', 'Europe', NULL, NULL),
('North America', 'continent', 'North America', NULL, NULL),
('Northern Hemisphere', 'region', NULL, 'Northern Hemisphere', NULL),
('Sweden', 'country', NULL, NULL, 'Sweden'),
('Mexico', 'country', NULL, NULL, 'Mexico');
And a table of locations:
CREATE TABLE locations ( locationId int auto_increment primary key, continent varchar(255), region varchar(255), country varchar(255) ); INSERT INTO locations VALUES (NULL, 'Europe', 'Northern Hemisphere', 'Sweden'), (NULL, 'North America', 'Northern Hemisphere', 'Canada'), (NULL, 'North America', 'Pacific Northwest', 'USA');
Obviously, this example is denormalized - in the real world, I'd use a surrogate key and store continentId, regionId, and countryId. Even then, it's not the ideal layout for this application. But that's just part of the challenge.
Here's the goal: find the most specific map for each location. By most specific, I mean use a country map if available. If not, use a region map. Use a continent map as a last resort.
So, the result set should be:
+------------+---------------------+ | locationId | mapName | +------------+---------------------+ | 1 | Sweden | | 2 | Northern Hemisphere | | 3 | North America | +------------+---------------------+
Bonus points if it works pre-4.1