A Little Noise

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.

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.

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)

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!

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();

Show Answer ▼

mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

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();

Show Answer ▼

mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

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 |
+----------+-------------+------+

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?

Show Answer ▼

 CREATE TABLE `test` (
   `data` varbinary(255) default NULL
 );