Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Tuesday September 11, 2007
09:48 AM

More MySQL "fun"

[ #34417 ]

A colleague had lots of fun debugging this table:

CREATE TABLE `mail` (
  `mail_id` int(11) unsigned NOT NULL auto_increment,
  `user` varchar(64) default NULL,
  `mail_type_id` int(10) unsigned NOT NULL,
  `domain_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`mail_id`),
  KEY `mail_type_id` (`mail_type_id`),
  KEY `domain_id` (`domain_id`,`user`),
  CONSTRAINT `mail_ibfk_2` FOREIGN KEY (`mail_type_id`) REFERENCES
`mail_type` (`mail_type_id`) ON UPDATE CASCADE,
  CONSTRAINT `mail_ibfk_4` FOREIGN KEY (`domain_id`) REFERENCES `domain`
(`domain_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And after much debugging of a particular problem, ran into this:

mysql> select * from mail where mail_type_id = 6 and user = '';
+---------+------+--------------+-----------+
| mail_id | user | mail_type_id | domain_id |
+---------+------+--------------+-----------+
|   25006 | NULL |            6 |        28 |
|   25008 | NULL |            6 |      3728 |
...
| 4603476 | NULL |            6 |     13579 |
| 4603507 | NULL |            6 |      3511 |
+---------+------+--------------+-----------+
364 rows in set (0.00 sec)

How the heck is a NULL value equal to the empty string?

NULL values are not allowed in indexes, so it sort of looks like MySQL is using the empty string for the index value but returning the correct 'NULL' value for the field when displayed. Damn.

$ mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using
readline 5.0


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

More MySQL "fun" 10 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • NULL values are not allowed in indexes, so it sort of looks like MySQL is using the empty string for the index value but returning the correct 'NULL' value for the field when displayed. Damn.

    You don't have index for 'user' column so it must be something else.

    --

    Ilya Martynov (http://martynov.org/ [martynov.org])

    • There's a key with the user column.

      KEY `domain_id` (`domain_id`,`user`)

      What am I misunderstanding?

      • This key is only used when you have domain_id in where condition. Order of columns in key is important [blogspot.com] (see point #3).
        --

        Ilya Martynov (http://martynov.org/ [martynov.org])

        • BTW you can see this with 'explain select':

          mysql> explain select * from mail where mail_type_id = 6 and user = '';
          +----+-------------+-------+------+---------------+--------------+--------- +-------+------+-------------+
          | id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra       |
          +----+-------------+-------+------+---------------+--------------+---------+ -------+------+-------------+
          |  1 | SIMPLE

          --

          Ilya Martynov (http://martynov.org/ [martynov.org])

  • BTW I tried to reproduce this and I cannot:

    mysql> show create table mail\G
    *************************** 1. row ***************************
    Table: mail
    Create Table: CREATE TABLE `mail` (
    `mail_id` int(11) unsigned NOT NULL auto_increment,
    `user` varchar(64) default NULL,
    `mail_type_id` int(10) unsigned NOT NULL,
    `domain_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`mail_id`),
    KEY `mail_type_id` (`mail_type_id`),
    KEY `domain_id` (`domain_id`,`user`)
    ) ENGINE=InnoDB AUTO_INCREMENT=25

    --

    Ilya Martynov (http://martynov.org/ [martynov.org])

    • It's certainly weird. The database is not corrupted - check table and even using the extended check it says all is ok.

      I can reproduce by the following:

      mysql> CREATE TABLE `foo` (
              -> `mail_id` int(11) unsigned NOT NULL auto_increment,
          `user` varchar(64) default NULL,
              -> `user` varchar(64) default NULL,
              -> `mail_type_id` int(10) unsigned NOT NULL,
              -> `domain_id`
      • Interesting. I tried exactly the same SQL statements and I get different result:

        mysql> CREATE TABLE `foo` (
            ->         `mail_id` int(11) unsigned NOT NULL auto_increment,
            ->         `user` varchar(64) default NULL,
            ->         `mail_type_id` int(10) unsigned NOT NULL,
            ->         `domain_id` int(10) unsigned NOT NULL,
            -> 

        --

        Ilya Martynov (http://martynov.org/ [martynov.org])

  • I've just found that this is known mysql bug [mysql.com] which was fixed in 5.0.23.
    --

    Ilya Martynov (http://martynov.org/ [martynov.org])

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.