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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 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])