Jose, thank you for the bug report. You are right: if there are several NULLs in a unique secondary index, CHECK TABLE will report table corruption because of a duplicate key in the index. I forgot to ease the check in CHECK TABLE.
I have now fixed this for 3.23.48. Regards, Heikki Innobase Oy ...... mysql> create table nulls (a int not null, b int, primary key (a), unique index (b)) type = innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into nulls values (1, NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into nulls values (2, NULL); Query OK, 1 row affected (0.00 sec) mysql> check table nulls; +------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------+ | test.nulls | check | error | Corrupt | +------------+-------+----------+----------+ 1 row in set (0.02 sec) mysql> ...... heikki@donna:~/mysqlm/sql > mysqld 011231 14:05:28 InnoDB: Started mysqld: ready for connections Error: duplicate key in index b InnoDB: prev record 0: SQL NULL; 1: len 4; hex 80000001; asc ....;; InnoDB: record RECORD: info bits 0 0: SQL NULL; 1: len 4; hex 80000002; asc .... ;; >Hi Heikki, > >I have proven this in MySQL 3.23.47 and I have found a problem with >"CHECK TABLE", that reports "error - corrupted table" if there are >several NULL's in a UNIQUE index. >Happy end of year to all!, >José Ceferino Ortega >-----Mensaje original----- >Hi! >I will change InnoDB so that it allows several NULL values in secondary >indexes even if that index would have been declared UNIQUE. Oracle has a >similar convention. >In the case of foreign keys, I advise against using NULL values, because >in SQL NULL really means 'an unknown value'. > >Actually, since NULL means an 'unknown value' it would be logical that >the SQL standard would ban the use of NULLs in any UNIQUE index. >Currently it has only been banned in a primary key. > >Regards, >Heikki >Innobase Oy--- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
