Troubleshooting and solutions for MySQL auto-increment ID oversize problem

introduction

Xiao A was writing code, and DBA Xiao B suddenly sent a message, "Hurry up and take a look at your user-specific information table T. The primary key, which is the auto-increment ID, has reached 1.6 billion. It has only been a short time. If it continues like this, it will soon be out of range and the insertion will fail, balabala..."

I don't remember it being that much, probably over 1k at most. I counted it and it was 11 million. It turns out that the operation and maintenance is looking at the auto_increment value, that is, there are a large number of delete and insert operations in the table, but I am updating most of the time. Why is this happening?

Let’s take a look at the detailed introduction.

Troubleshooting

This table is a simple interface service in use. Every day, big data will collect a large amount of information and then push it to Xiao A. Xiao A will update the information to the database. If it is new data, it will be inserted. If it is old data, the previous data will be updated. The only external interface is query.

Soon, Xiao A checked his own code. There was no deletion, nor was there any active insertion or update of the ID. How could this happen? Could it be Xiao B's fault? That's unlikely. The DBA manages many tables. If there was a problem, it would have been exposed long ago, but I have no idea what the problem is.

Xiao A carefully observed the more than 10 million existing data, taking the insertion time and id as the main observation fields. Soon, he found a problem. The first data inserted every day was always more than 10 million more than the previous day. Sometimes the increase was more, and sometimes it was less. Xiao A pointed the finger at DBA Xiao B and described the problem to Xiao B again.

Little B asked Little A, "Did you use the REPLACE INTO ..." statement? What's going on? It turns out that REPLACE INTO ... will affect the primary key.

Effect of REPLACE INTO ... on primary keys

Assume there is a table t1:

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID, auto-increment',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'User uid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'User nickname',
PRIMARY KEY (`id`),
UNIQUE KEY `u_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Test replace into';

If we create this table and execute the following statement, what will the final data record look like?

insert into t1 values(NULL, 100, "test1"),(NULL, 101, "test2");
replace into t1 values(NULL, 100, "test3"); 

It turns out that when REPLACE INTO ... is used each time an insertion is made, if the data corresponding to the unique index already exists, the original data will be deleted and then the new data will be re-inserted, which will cause the id to increase, but the actual expectation may be to update the data.

Xiao A said: "I know replace is like this, so I didn't use it." But he checked again and found that it was not his problem. He did not use REPLACE INTO ....

Xiao A checked again carefully but still found no problem, so he asked Xiao B to check the binlog log to see if there was anything strange. After checking, no problem was found. There was indeed a jump, but no substantial problem.

In the figure below, the value of @1 corresponds to the auto-increment primary key id, and (@2, @3) is used as the unique index.

After a long time, Xiao B pointed Xiao A in a direction. Xiao A began to doubt his insert update statement INSERT ... ON DUPLICATE KEY UPDATE .... After checking for a long time, he found that there was indeed a problem here.

Effect of INSERT ... ON DUPLICATE KEY UPDATE ... on primary keys

This statement is similar to REPLACE INTO ..., but it does not change the primary key of the record. It is still the t1 table above. What is the result of executing the following statement?

insert into t1 values(NULL, 100, "test4") on duplicate key update name = values(name); 

Yes, just as Xiao A expected, the primary key was not added, and the name field was updated to the desired value, but there was a prompt in the execution result that caught Xiao A's attention.

No errors; 2 rows affected, taking 10.7ms

Obviously one piece of data was updated, so why is the number of affected records 2? Xiao A looked at the auto_increment in the current table again

CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID, auto-increment',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'User uid',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'User nickname',
PRIMARY KEY (`id`),
UNIQUE KEY `u_idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Test replace into';

It's actually 5', it should be 4.

In other words, the above statement will increase the auto-increment ID by 1, similar to REPLACE INTO ..., but the actual record is not added. Why is this?

After checking the information, Xiao A found out that there is a parameter innodb_autoinc_lock_mode for MySQL primary key auto-increment. It has three possibilities: 0, 1, and 2. It was added after MySQL 5.1. The default value is 1. Previous versions can be regarded as 0.

You can use the following statement to see which mode is currently

select @@innodb_autoinc_lock_mode;

The default value of the database used by Xiao A is also 1. When doing a simple insert (the number of inserted rows can be determined), auto_increment is directly increased by 1 without locking the table, which improves performance. When inserting complex statements like insert into select ..., the number of rows to be inserted is not known in advance. In this case, the table must be locked (a special table lock called AUTO_INC) so that auto_increment is accurate and the lock is released only when the statement ends. There is also a type of insert called mixed-mode insert, such as INSERT INTO t1 (c1,c2) ​​VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'), in which some of the inserts explicitly specify the auto-increment primary key value and some do not. The INSERT ... ON DUPLICATE KEY UPDATE ... we are discussing here also belongs to this type. At this time, the statement will be analyzed and then auto_incrementid will be assigned according to as many situations as possible. How to understand this? Let me take a look at the following example:

truncate table t1;
insert into t1 values(NULL, 100, "test1"),(NULL, 101, "test2"),(NULL, 102, "test2"),(NULL, 103, "test2"),(NULL, 104, "test2"),(NULL, 105, "test2");

-- At this time, the next auto-increment id in the data table is 7

delete from t1 where id in (2,3,4);

-- At this point, only 1, 5, and 6 are left in the data table, and the auto-increment id is still 7

insert into t1 values(2, 106, "test1"),(NULL, 107, "test2"),(3, 108, "test2");

-- What is the auto-increment id here?

After the above example is executed, the next auto-increment id of the table is 10. Do you understand it correctly? Because the last statement executed is a Mixed-mode insert statement, InnoDB will analyze the statement and then assign three ids. At this time, the next id is 10, but not all three assigned ids are used. @Always late thanks for pointing out that I misunderstood the official documentation

Mode 0 means that the table lock is added no matter what the situation is, and it is released when the statement is executed. If a record is actually added, auto_increment is increased by 1.

As for mode 2, the AUTO_INC lock is not added under any circumstances, which poses a security problem. When the binlog format is set to Statement mode, the execution result may be inconsistent with the master database when the slave database is synchronized, which is a big problem. Because there may be a complex insertion, and another insertion comes in while it is still being executed. When restoring, it is executed one by one, and this concurrency problem cannot be reproduced, which may cause the record ID to not match.

At this point, the problem of ID jumping has been analyzed. Since the value of innodb_autoinc_lock_mode is 1, INSERT ... ON DUPLICATE KEY UPDATE ... is a simple statement, and the number of affected rows can be calculated in advance. Therefore, regardless of whether it is updated or not, auto_increment will be increased by 1 (greater than 1 if there are multiple rows).

If you change the innodb_autoinc_lock_mode value to 0 and execute INSERT ... ON DUPLICATE KEY UPDATE ... again, you will find that auto_increment does not increase, because this mode directly adds the AUTO_INC lock and releases it when the statement is executed. If it is found that the number of rows has not increased, the auto-increment id will not be increased.

INSERT ... ON DUPLICATE KEY UPDATE ... returns 2 when the number of rows affected is 1?

Why is this happening? Logically, the number of rows affected is 1. See the official documentation for details.

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

The official statement clearly states that insert affects 1 row, update affects 2 rows, and if it is 0, it means that the value exists and the value before and after the update is the same. Is it hard to understand?

In fact, you should think like this. This is to distinguish whether it is inserted or updated. Returning 1 means successful insertion and 2 means successful update.

Solution

Setting innodb_autoinc_lock_mode to 0 can definitely solve the problem, but in this case, the concurrency of insertion may be greatly affected, so Xiao A thinks that the DBA will not agree. After consideration, two possible solutions are currently prepared:

Modify business logic

Modify the business logic and separate the INSERT ... ON DUPLICATE KEY UPDATE ... statements. First query and then update. This ensures that the primary key does not increase uncontrollably, but increases complexity. The original one request may become two requests: first query and then update.

Delete the auto-increment primary key of the table

Delete the auto-increment primary key and use the unique index as the primary key. In this way, basically no changes are required. Just make sure that the current auto-increment primary key has no practical use. In this case, efficiency may be affected during insertion and deletion. However, for situations with many queries, Xiao A prefers the latter after comparing the two.

Conclusion

In fact, the number of rows affected by INSERT ... ON DUPLICATE KEY UPDATE ... is 2. Xiao A discovered it very early, but he did not maintain his curiosity and did not take it seriously. He did not delve into the problem. If he did, a lot of new knowledge would be revealed. It seems that Xiao A should still maintain his curiosity and sensitivity towards the outside world, so that he can make progress.

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. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.