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
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.
Re:Index?
Ovid on 2007-09-11T15:09:33
There's a key with the user column.
KEY `domain_id` (`domain_id`,`user`)What am I misunderstanding?
Re:Index?
IlyaM on 2007-09-11T15:14:12
This key is only used when you have domain_id in where condition. Order of columns in key is important (see point #3).Re:Index?
IlyaM on 2007-09-11T15:18:53
BTW you can see this with 'explain select':Note that key `domain_id` is not listed as a possible key.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 | mail | ref | mail_type_id | mail_type_id | 4 | const | 1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+- ------+------+-------------+
1 row in set (0.00 sec)
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=25009 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from mail;
+---------+------+--------------+-----------+
| mail_id | user | mail_type_id | domain_id |
+---------+------+--------------+-----------+
| 25006 | NULL | 6 | 28 |
| 25008 | NULL | 6 | 3728 |
+---------+------+--------------+-----------+
2 rows in set (0.00 sec)
mysql> select * from mail where mail_type_id = 6 and user = '';
Empty set (0.00 sec)
ilya@denmark:~$ mysql --version
mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i486) using readline 5.2
Could it be you have a corrupted database? Have you tried 'REPAIR TABLE mail'?
Re:corrupted database?
jalh on 2007-09-12T08:55:19
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` int(10) unsigned NOT NULL,
-> PRIMARY KEY (`mail_id`),
-> KEY `mail_type_id` (`mail_type_id`),
-> KEY `domain_id` (`domain_id`,`user`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)
mysql> insert into foo set mail_type_id = 4, domain_id = 6;
Query OK, 1 row affected (0.00 sec)
mysql> insert into foo set mail_type_id = 4, domain_id = 6, user = '';
Query OK, 1 row affected (0.01 sec)
mysql> select * from foo;
+---------+------+--------------+-----------+
| mail_id | user | mail_type_id | domain_id |
+---------+------+--------------+-----------+
| 1 | NULL | 4 | 6 |
| 2 | NULL | 4 | 6 |
+---------+------+--------------+-----------+
2 rows in set (0.00 sec)
wtf? I guess after the given point, the program which accesses the database was changed to insert user as '' rather than null, but why is mysql displaying a '' varchar string as NULL?
Oracle like feature?
IlyaM on 2007-09-12T09:30:09
Interesting. I tried exactly the same SQL statements and I get different result:I know that Oracle has similar behavior as you are seeing: empty string is treated as NULL. Could it be there is some configuration parameter in MySQL to make it compatible with Oracle?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,
-> PRIMARY KEY (`mail_id`),
-> KEY `mail_type_id` (`mail_type_id`),
-> KEY `domain_id` (`domain_id`,`user`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into foo set mail_type_id = 4, domain_id = 6;
Query OK, 1 row affected (0.01 sec)
mysql> insert into foo set mail_type_id = 4, domain_id = 6, user = '';
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> select * from foo;
+---------+------+--------------+-----------+
| mail_id | user | mail_type_id | domain_id |
+---------+------+--------------+-----------+
| 1 | NULL | 4 | 6 |
| 2 | | 4 | 6 |
+---------+------+--------------+-----------+
2 rows in set (0.00 sec)Re:Oracle like feature?
jalh on 2007-09-12T10:26:22
The setup on my test box was a standard centos5 install with no extra config. Perhaps the version is buggy or something?Re:Oracle like feature?
IlyaM on 2007-09-12T12:51:54
Yep, it is buggy. See my other comment.
