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)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.5+
• OS: Any
• 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 :)