Bug #46396 returned MAX value of primary integer key is less than actual max value
Submitted: 27 Jul 2009 9:20 Modified: 28 Jul 2009 5:14
Reporter: ALex Lysenko Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.11 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: MAX

[27 Jul 2009 9:20] ALex Lysenko
Description:
Let we have a table "table1" with primary key "MsgNum", which is autoincrement, and no other keys.

One application writes to table1 using statement 
"INSERT INTO table1 VALUES (...),(...)". 
Note: Sometimes up to 300 rows to be inserted within one statement.

Another application is reading new records,  executing
"SELECT * FROM table 1 WHERE MsgNum > [LastMsgNum] ORDER BY MsgNum"
After processing each record, last retrieved MsgNum is saved LastMsgNum local variable. 

Periodically application checks if table is recreated, executing 
"SELECT MAX(MsgNum) FROM table1" and comparing if the result is less than saved MsgNum.

So, the problem is - sometimes "SELECT MAX(MsgNum) FROM table1" returns result,
which is LESS than MsgNum of the record, which already has been processed by application. The difference is about several hundreds.

Probably it happens when large portion of records was inserted. It seems, these records are already avaiable to "SELECT * FROM table1", but for some reason key value is not yet updated, and SELECT MAX(MsgNum) using key, returns wrong value,  corresponding to the state of the table before records were inserted.
-----------

Following experiment was done - in addition to "SELECT MAX(MsgNum)", I tried retrieving autouincrement value using 
"SHOW TABLE STATUS LIKE 'table1'" 
and 
"SELECT MsgNum FROM table1 ORDER BY MsgNum DESC LIMIT 1"

Application performed following actions in a cycle:

1. execute "SELECT * FROM table1 WHERE MsgNum > LastMsgNum ORDER BY MsgNum"
2. process result set in order of records, saving LastMsgNum
3. calculate
   max1 = "SELECT MAX MsgNum FROM table1"
   max2 = "SHOW TABLE STATUS LIKE 'table1'"(value of autoincrement field) - 1
   max3 = "SELECT MsgNum FROM table1 ORDER BY MsgNum DESC LIMIT 1"
4. Checks max1 == max2 == max3.

Result is : 
  max1 can be less than max 2 and max3. 
  max2 and max 3 seem to be actual always.

How to repeat:
I have not try to repeat it on different servers, but it seems, that it happens when server is seriously loaded.
Actions are already described, the summary is:

1. Create table table1, FIELDS: MsgNum INTEGER Primary, Data TINYBLOB
2. Make one application frequently insert records, several hundreds with one query
3. Make another application frequently calculate
     max1 = "SELECT MAX MsgNum FROM table1"
     max2 = "SHOW TABLE STATUS LIKE 'table1'"(value of autoincrement field) - 1
     max3 = "SELECT MsgNum FROM table1 ORDER BY MsgNum DESC LIMIT 1"
4. Wait while max1 < max2
[27 Jul 2009 9:28] Sveta Smirnova
Thank you for the report.

But version 5.1.11 is very old and many bugs were fixed since. Please try current version 5.1.36 and inform us if problem still exists.

If you are using InnoDB tables check if inserted row was in uncommitted transaction also.
[27 Jul 2009 9:32] ALex Lysenko
Sorry. Both in description and how to repeat, I forgot: 

comparing max1, max2 and max3 is performed, when the bug is revealed, i.e. when 
max1 < LastMsgNum
[27 Jul 2009 9:33] ALex Lysenko
Thanks, I will try.
I am using MyISAM with no transactions.
[27 Jul 2009 9:45] Sveta Smirnova
Thank you for the feedback.

We will wait results of your tests.
[28 Jul 2009 4:40] ALex Lysenko
It seems that this bug is fixed in current version.
I tried 5.1.34 - bug was not detected.
[28 Jul 2009 5:14] Sveta Smirnova
Thank you for the feedback.

Closed as "Can't repeat" because last comment.