Bug #69332 InnoDB will overwrite the sam row_id rows in the same table
Submitted: 28 May 2013 3:22 Modified: 28 May 2013 7:12
Reporter: xiaobin lin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5+ OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, overwrite rows, row_id

[28 May 2013 3:22] xiaobin lin 
Description:
If an InnoDB table is defined without an primary key, the system will use the auto-increment system row_id(6 bytes) as the primary key. The row_id is increased for each row, and the maxnumber is 2^48. If exceeds, reset and reuse from 1.

So it will face the  problem when a new row_id is the same with an old one, if a server keeps running for long time.

The current strategy is just to overwrite the row that has the same row_id. This will lead to data lost and data inconsistency in master and slave.

How to repeat:
The code.

Suggested fix:
I think the stragety that auto-increment field uses now in MySQL is ok. Do not increase the row_id when it reach up to the maximum, and returns a duplicated-key error.
[28 May 2013 6:43] xiaobin lin 
Repeatable step

http://dl2.iteye.com/upload/attachment/0085/1069/55343d35-869c-3ab8-a304-10907116cdc0.jpg
[28 May 2013 7:10] MySQL Verification Team 
1.7 million per second for 5 years to exhaust 2^48 :)
[28 May 2013 7:12] MySQL Verification Team 
That number is feasible to reach with modern hardware :)