| Bug #29860 | DELETE query fails on tables with a CHAR type column as a primary key. | ||
|---|---|---|---|
| Submitted: | 18 Jul 2007 0:22 | Modified: | 24 Jul 2007 12:13 |
| Reporter: | Charles Taylor | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S1 (Critical) |
| Version: | 5.0.42, 6.0-Falcon | OS: | Linux (Gentoo Linux) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
| Tags: | delete char primary key | ||
[18 Jul 2007 0:37]
MySQL Verification Team
Thank you for the bug report. I was not able to repeat with server built from today source, could you please provide the output of: show variables like "%char%"; Thanks in advance.
[19 Jul 2007 1:00]
Charles Taylor
+--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
[19 Jul 2007 14:21]
Sveta Smirnova
Thank you for the report. Verified as described with Falcon tree. With other trees bug is not repeatable, but exists in 5.0.42
[19 Jul 2007 14:22]
Sveta Smirnova
test case
Attachment: bug29860.test (application/octet-stream, text), 324 bytes.
[24 Jul 2007 12:13]
Sergey Vojtovich
Failed to repeat described problem. Regarding falcon tree - this likely happens because of irregular main->falcon merges.
Description: In an INNODB table with CHAR type columns that are also PRIMARY KEYS, one can not delete rows when using WHARE against the CHAR column in question. Similar SELECT queries work. How to repeat: /* * CREATE SIMPLE DEMONSTRATION TABLE `testit` */ CREATE TABLE testit ( a char(32), PRIMARY KEY ( a ) ) ENGINE=INNODB; /* * INSERT A TEST RECORD * -> This part works.... */ INSERT INTO testit (a) VALUE ('value1'); Query OK, 1 row affected (0.00 sec) /* * SELECT THE TEST RECORD * -> This works too... */ SELECT * FROM testit WHERE a = 'value1'; +--------+ | a | +--------+ | value1 | +--------+ 1 row in set (0.00 sec) /* * DELETE THE TEST RECORD * -> This doesn't work!!! WTF?? */ DELETE FROM testit WHERE a = 'value1'; Query OK, 0 rows affected (0.00 sec) Suggested fix: Make the WHERE comparison work correctly in the DELETE query context - Possibly related to bug #29449.