MySQL Bugs: #19604: CHECK TABLE with concurrent INSERT can reset auto

Bug #19604	CHECK TABLE with concurrent INSERT can reset auto_increment
Submitted:	8 May 2006 12:18	Modified:	10 Jun 2006 17:49
Reporter:	Ingo Strüwing
Status:	Closed
Category:	Server: General	Severity:	S1 (Critical)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	Target Version:

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.

My test script.

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

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

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

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.

Noted in 5.0.23, 5.1.12 changelogs.