MySQL 4.1.7 allows non-unique values in a unique index?!
WOW! This is a really bad bug... mysql SHOW INDEX FROM LITERAL; +-++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+---+-+--++--++-+ | LITERAL | 0 | PRIMARY |1 | ID | A | 14331755 | NULL | NULL | | BTREE | | | LITERAL | 0 | VALUE|1 | VALUE | A | 14331755 | NULL | NULL | | BTREE | | +-++--+--+-+---+-+--++--++-+ Notice the unique index on VALUE ? Now take a look at this: mysql SELECT *, MD5(LITERAL.VALUE) AS MD5_VALUE FROM LITERAL WHERE ID=567344 OR ID=14076840; +--+---+--+ | ID | VALUE | MD5_VALUE| +--+---+--+ | 567344 | Law | 81588d326cebe6416d3904db93603af1 | | 14076840 | Law | 81588d326cebe6416d3904db93603af1 | +--+---+--+ 2 rows in set (0.00 sec) Seems like a fatal bug to me! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Kevin A. Burton wrote: WOW! This is a really bad bug... So now only that... but check this out: mysql SELECT * FROM LITERAL WHERE VALUE = 'Law'; +--+---+ | ID | VALUE | +--+---+ | 14076840 | Law | +--+---+ 1 row in set (0.00 sec) Which only returns ONE row even though there are two in the database with the same value! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Hi, This could also be a badly corrupted table, what does CHECK TABLE / REPAIR TABLE report ? Regards, Jocelyn - Original Message - From: Kevin A. Burton [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 03, 2005 10:04 PM Subject: MySQL 4.1.7 allows non-unique values in a unique index?! WOW! This is a really bad bug... mysql SHOW INDEX FROM LITERAL; +-++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-++--+--+-+--- +-+--++--++-+ | LITERAL | 0 | PRIMARY |1 | ID | A |14331755 | NULL | NULL | | BTREE | | | LITERAL | 0 | VALUE|1 | VALUE | A |14331755 | NULL | NULL | | BTREE | | +-++--+--+-+--- +-+--++--++-+ Notice the unique index on VALUE ? Now take a look at this: mysql SELECT *, MD5(LITERAL.VALUE) AS MD5_VALUE FROM LITERAL WHERE ID=567344 OR ID=14076840; +--+---+--+ | ID | VALUE | MD5_VALUE| +--+---+--+ | 567344 | Law | 81588d326cebe6416d3904db93603af1 | | 14076840 | Law | 81588d326cebe6416d3904db93603af1 | +--+---+--+ 2 rows in set (0.00 sec) Seems like a fatal bug to me! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.7 allows non-unique values in a unique index?!
Jocelyn Fournier wrote: Hi, This could also be a badly corrupted table, what does CHECK TABLE / REPAIR TABLE report ? Thats next on our list... going to take care of that tonight. Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]