Another Parting Shot

October 10, 2008

Another Parting Shot

Filed under: MySQL Gotchas — snoyes @ 1:38 pm

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.

Comments (4)

4 Comments »

My guess is it has something to do with the data directory being part of ‘your’ home directory. I don’t have 5.1.x anywhere at work, I’ll see what I can figure out when I get home. Any chance anything shows up in the error logs to help figure out the issue?

Comment by Tom Krouper — October 10, 2008 @ 3:48 pm
Can’t reproduce:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-rc-community MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use test;
Database changed
mysql> 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
-> ENGINE = MyISAM
-> ) */;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (100);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT COUNT(*) FROM t1;
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+———-+
| COUNT(*) |
+———-+
| 1 |
+———-+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+——+
| id |
+——+
| 100 |
+——+
1 row in set (0.02 sec)

mysql>

Comment by pabloj — October 10, 2008 @ 4:01 pm
Tom: Nothing in the logs.

pabloj: This behavior only occurs when you deliberately cause it. The question is, how?

Comment by snoyes — October 10, 2008 @ 5:18 pm
“deliberately cause” ? What, copy .MYD file from one partition over the over ?

Comment by Sergei — October 11, 2008 @ 1:18 am