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:
None 
Category:MySQL Server: Embedded Library ( libmysqld ) Severity:S1 (Critical)
Version:5.1.17 OS:Mac OS X
Assigned to: Alexey Botchkov

[8 May 2007 16:49] Joerg Bruehe
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.
[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.