Bug #19604 CHECK TABLE with concurrent INSERT can reset auto_increment
Submitted: 8 May 2006 10:18 Modified: 10 Jun 2006 15:49
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.19 OS:Linux (Linux)
Assigned to: Ingo Strüwing

[8 May 2006 10:18] Ingo Strüwing
Description:
When running one or more threads doing CHECK TABLE t1 and one or more threads doing (concurrent) INSERTS on t1, it can happen that the auto_increment value is reset (at least for one INSERT statement). With an unique auto_increment column this gives an error.

This is one of the problems I detected while working on Bug#17332 (changing key_buffer_size on a running server can crash under load). I report it as a new bug since it is independent from the key cache and can be fixed quickly.

How to repeat:
I will add a shell script later.

Suggested fix:
In the check table code, do not let update_auto_increment() work on the real auto_increment value. It must not be modified without an exclusively (write) locked table. CHECK TABLE does just use a shared (read) lock.
[11 May 2006 10:21] Ingo Strüwing
My test script.

Attachment: bug19604-1.sh (application/x-sh, text), 14.43 KiB.

[11 May 2006 10:24] 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/6239
[31 May 2006 8:25] 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/7076
[10 Jun 2006 14:45] Ingo Strüwing
CHECK TABLE did temporarily clear the auto_increment value.
  It runs with a read lock, allowing other readers and
  conurrent INSERTs. The latter could grab the wrong value
  in this moment.
  
  CHECK TABLE does no longer modify the auto_increment value.
  Not even for a short moment.

Pushed to 5.0.23 and 5.1.12.
[10 Jun 2006 15:49] Paul Dubois
Noted in 5.0.23, 5.1.12 changelogs.