MySQL InnoDB row_ Methods and steps of ID boundary overflow verification



Talk to your classmates about row_ ID is a boundary problem, which is described in detail here.

If the InnoDB table does not have a primary key defined, it will use a default increment row of the system_ id (dict_ sys->row_ ID) as the primary key. Add 1 to each insertion line to reach the maximum value of cyclic reuse.

It should be noted that although Dict_ sys->row_ ID is defined as an unsigned long long long, but since the primary key value is only 6 bytes, the maximum value is 2 ^ 48. row_ If the ID exceeds this value, it will be incremented. Only when the ID is written, only the low bit will be taken, which can be regarded as the operation of module fetching.


This involves a problem. In a long-running mysql, if you frequently insert and delete rows (such as log tables), even if the final table size is not very large, the value row may still appear_ ID reuse. And we know that as a primary key value, it can’t be repeated.

Suppose this happens, in a table, the row of the newly inserted row_ ID and row of an old row_ ID conflict, will swelling?


In fact, only one conclusion is needed here. The purpose of this paper is to discuss a verification method with you. With the above information, we can consider designing the following reproduction steps:

1) Create an empty table without primary key

2) GDB set Dict_ sys->row_ ID is 1

3) Insert rows into an empty table

4) GDB set Dict_ sys->row_ ID is 2 ^ 48

5) Insert a few more lines

6) Look at the results


As you can see, lines (1) and (2) are covered.

A more reasonable solution should be to report a duplicate key error like MySQL’s auto increment primary key


The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developeppaer.