A Little Noise

Another Parting Shot

Written by

snoyes

in

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 responses to “Another Parting Shot”

  1. Tom Krouper Avatar
    Tom Krouper

    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?

  2. pabloj Avatar
    pabloj

    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>

  3. snoyes Avatar
    snoyes

    Tom: Nothing in the logs.

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

  4. Sergei Avatar
    Sergei

    “deliberately cause” ? What, copy .MYD file from one partition over the over ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More posts