MySQL InnoDB row_id boundary overflow verification method steps

background

I talked to my classmates about a boundary problem of row_id, and I will explain it in detail here.

If the InnoDB table does not define a primary key, a system default incrementing row_id (dict_sys->row_id) will be used as the primary key. Each time a row is inserted, add 1 and repeat the process until the maximum value is reached.

It should be noted that although dict_sys->row_id is defined as an unsigned long long, since this primary key value is only 6 bytes, the maximum value is 2^48. If row_id exceeds this value, it will still increase, but only the lower bits will be taken when writing, which can be considered as a modulo operation.

question

This involves a problem. In a long-running MySQL, if rows are frequently inserted and deleted (such as log tables), even if the final table size is not very large, row_id value reuse may still occur. And we know that as a primary key value, it cannot be repeated.

Suppose this happens. In a table, the row_id of a newly inserted row conflicts with the row_id of an older row. What will happen?

verify

In fact, only one conclusion is needed here. The purpose of this article 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 a primary key

2) gdb sets dict_sys->row_id to 1

3) Insert several rows into an empty table

4) gdb sets dict_sys->row_id to 2^48

5) Insert several more rows

6) View the results

in conclusion

As you can see, rows (1) and (2) are overwritten.

A more reasonable solution would be to report a duplicate-key error, just like the auto-increment primary key in the MySQL table.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.