Example Analysis of InnoDB row_id Boundary overflow Verification in MySQL

Editor to share with you the example analysis of InnoDB row_id boundary overflow verification in MySQL, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

Background

I talked to my classmates about a boundary problem in row_id, which is explained in detail here.

If the InnoDB table does not define a primary key, it uses a default incremental row_id of the system (dict_sys- > row_id) as the primary key. Insert a row plus 1 at a time to reach the maximum cyclic reuse.

It should be noted that although dict_sys- > row_id is defined as a unsigned long long, because the primary key value is only 6 bytes, the maximum value is 2 ^ 48. If the row_id exceeds this value, it will still increase incrementally, but only take the low bit when writing, which can be considered as a modularization operation.

problem

This involves a problem, in a long-running MySQL, if delete rows are inserted frequently (like log-like tables), value row_id reuse may occur even if the final table size is not very large. And we know that as a primary key value, it can't be repeated.

Suppose that in a table, the row_id of the newly inserted row conflicts with the row_id of an older row.

Verification

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

1) create an empty table without primary key

2) gdb sets dict_sys- > row_id to 1

3) insert several rows into the empty table

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

5) insert a few more rows

6) look at the results

Conclusion

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

A more reasonable solution should be like MySQL's in-table self-increasing primary key, report a duplicate-key error.

The above is all the content of the article "sample Analysis of InnoDB row_id Boundary overflow Verification in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!