| Bug #28309 | First insert violates unique constraint - was "memory" table empty ? | ||
|---|---|---|---|
| Submitted: | 8 May 2007 16:49 | Modified: | 15 Jun 2007 9:20 |
| Reporter: | Joerg Bruehe | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Embedded Library ( libmysqld ) | Severity: | S1 (Critical) |
| Version: | 5.1.17 | OS: | MacOS |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[2 Jun 2007 17:44]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/27992 ChangeSet@1.2509, 2007-06-02 21:45:12+05:00, holyfoot@mysql.com +1 -0 bug #28309 First insert violates unique constant - was 'memory' table empty? If we have lower_case_table_names == 2 (on case insernsitive file systems) we not always make 'homedir' part turned into lowercase. More exactly, we set it as it is for the TABLE_SHARE and calling handler::open, though we make it lowercase when call ha_remove_table. So HEAP engine can't properly find and remove HP_SHARE, reuses removed one what produces that error. Here i propose just to turn mysql_real_home into lowercase if the filesystem is case insensitive
[6 Jun 2007 15:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28219 ChangeSet@1.2523, 2007-06-06 19:13:46+05:00, holyfoot@mysql.com +1 -0 bug #28309 First insert violates UNIQUE constraint - was "memory" table empty? If we have lower_case_table_names == 2 (usually on case insensitive file systems) we sometimes make 'homedir' part of the path sent to the handler into lowercase. So in this case HEAP engine couldn't properly find and remove HP_SHARE, what caused the bug.
[7 Jun 2007 10:03]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/28279 ChangeSet@1.2551, 2007-06-07 14:03:43+05:00, holyfoot@mysql.com +3 -0 bug #28309 First insert violates UNIQUE constraint - was "memory" table empty? If we have lower_case_table_names == 2 (usually on case insensitive file systems) we sometimes make 'homedir' part of the path sent to the handler into lowercase. So in this case HEAP engine couldn't properly find and remove HP_SHARE, what caused the bug.
[13 Jun 2007 16:59]
Joerg Bruehe
Sorry for the bad news: We just have exactly this symptom in the 4.1.23 release build. So maybe this 5.1 fix needs to be backported - that's why I have set the status back to "verified".
[14 Jun 2007 19:01]
Bugs System
Pushed into 5.1.20-beta
[15 Jun 2007 9:20]
MC Brown
Documented in the 5.1.20 changelog.
[20 Jun 2008 14:59]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/48246
[8 Jul 2008 14:40]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/49184 2635 Jonathan Perkin 2008-07-08 [merge] Merge from trunk.
Description: This bug was first observed in 5.1.17, but not yet reported. Environment: OS X machine, "embedded" build + test Symptoms: ===== ctype_utf8 [ fail ] Errors are (from /PATH/mysqltest-time) : mysqltest: At line 360: query 'insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f')' failed: 1582: Duplicate entry 'a' for key 'a' (the last lines may be the most important ones) Result from queries before failure can be found in /PATH/mysql-test/var/log/ctype_utf8.log ===== ===== heap [ fail ] Errors are (from /PATH/mysqltest-time) : mysqltest: At line 24: query 'insert into t1 values(1,1),(2,2),(3,3),(4,4)' failed: 1582: Duplicate entry '2' for key 'PRIMARY' (the last lines may be the most important ones) Result from queries before failure can be found in /PATH/mysql-test/var/log/heap.log ===== Similar messages from other tests as well, but I have not analyzed them in sufficient detail. How to repeat: Build + test "embedded". Suggested fix: In both cases, it was the first insert into a newly created "memory" table. However, a table with this name had existed previously, had contained similar data already, and had been dropped. When the table had been created for the first time, a similar insert had worked. I suspect that due to some platform-specific bug (C library and memory allocator ?), the second "create table" accessed a memory area that still contained the data inserted into the like-named table. It might be necessary to explicitly check that the sequence create table t1 (....); insert into t1 values ....; drop table t1; create table t1 (....); will really create an empty table.