Hi, I am Toyonaga from Rakuma, so easy to get tan, by the way.
Well, this is quite sudden, but have you heard the script “INSERT ... ON DUPLICATE KEY UPDATE” of MySQL?
The script “INSERT, UPDATE” huge data at once.
We were using the script for objects which took too long for one by one process on system of Rakuma.
However, the script caused the following troubles.
- “AUTO_INCREMENT” processed unintentionally
- Reach the maximum that can be stored with int
- Couldn’t save any new data
This time, I would like to share solutions about #1.
About the script ““INSERT ... ON DUPLICATE KEY UPDATE”
[MySQL :: MySQL 5.6 Reference manual:: 13.2.5.3 the script ““INSERT ... ON DUPLICATE KEY UPDATE”] (https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html)
Once you use the script, you can execute INSERT and UPDATE with one SQL.
When I was searching the reason why “AUTO_INCREMENT” unintentionally executed, I found out that AUTO_INCREMENT could execute even when it became UPDATE statement.
Then, I will start a verification.
Testing environment
|
$ mysql --version mysql Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using EditLine wrapper |
Test
Preparation
|
CREATE DATABASE test; |
|
CREATE TABLE `tmp_a` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `uniq` varchar(20) NOT NULL, `cnt` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `idx01` (`uniq`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; |
Confirm current AUTO_INCREMENT
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) |
INSERT statement is issued （ No value for the id column ）
Before the changes
|
mysql> SELECT * FROM tmp_a; Empty set (0.00 sec) |
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec)
|
execute
|
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (null,'u1', 1), (null,'u2', 2), (null,'u3', 3) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 |
After the execute
You can confirm that the data is registered.
|
mysql> SELECT * FROM tmp_a; +----+------+-----+ | id | uniq | cnt | +----+------+-----+ | 1 | u1 | 1 | | 2 | u2 | 2 | | 3 | u3 | 3 | +----+------+-----+ 3 rows in set (0.00 sec) |
To proceed 3 in AUTO_INCREMENT
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) |
UPDATE statement is issued (Set value for the id column)
Before the execute
|
mysql> SELECT * FROM tmp_a; +----+------+-----+ | id | uniq | cnt | +----+------+-----+ | 1 | u1 | 1 | | 2 | u2 | 2 | | 3 | u3 | 3 | +----+------+-----+ 3 rows in set (0.00 sec) |
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.00 sec) |
Execute
Update cnt to 1000 times
|
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 6 rows affected (0.01 sec) Records: 3 Duplicates: 3 Warnings: 0 |
After the execute
“cnt” has been updated.
|
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | +----+------+------+ 3 rows in set (0.00 sec) |
AUTO_INCREMENT was not changed.
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.01 sec) |
The system is operating as intended.
UPDATE statement is issued (No value for the id column)
Before the execute
|
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | +----+------+------+ 3 rows in set (0.00 sec) |
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 4 | +----------------+ 1 row in set (0.01 sec) |
Execute
|
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (null,'u1', 1111), (null,'u2', 2222), (null,'u3', 3333) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 6 rows affected (0.01 sec) Records: 3 Duplicates: 3 Warnings: 0 |
After the execute
|
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1111 | | 2 | u2 | 2222 | | 3 | u3 | 3333 | +----+------+------+ 3 rows in set (0.01 sec) |
tep forward to AUTO_INCREMENT
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 7 | +----------------+ 1 row in set (0.00 sec) |
Let’s have a break now.
When you use the script “INSERT ... ON DUPLICATE KEY UPDATE”
I found out that I need to specify the following column value to not operate “AUTO_INCREMENT” when you update.
- id (AUTO_INCREMENT)
- uniq (DUPLICATE KEY)
INSERT, UPDATE statement is issued
To use a disputed point this time.
Before the changes
|
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1111 | | 2 | u2 | 2222 | | 3 | u3 | 3333 | +----+------+------+ 3 rows in set (0.01 sec) |
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 7 | +----------------+ 1 row in set (0.00 sec) |
Execute
|
mysql> INSERT INTO tmp_a (id, uniq, cnt) -> VALUES -> (1,'u1', 1000), (2,'u2', 2000), (3,'u3', 3000), -> (null,'u4', 4), (null,'u5', 5), (null,'u6', 6) -> ON DUPLICATE KEY UPDATE -> uniq=VALUES(`uniq`), -> cnt=VALUES(`cnt`) -> ; Query OK, 9 rows affected (0.02 sec) Records: 6 Duplicates: 3 Warnings: 0 |
After the execute
The date which is registered and updated is what I intended.
|
mysql> SELECT * FROM tmp_a; +----+------+------+ | id | uniq | cnt | +----+------+------+ | 1 | u1 | 1000 | | 2 | u2 | 2000 | | 3 | u3 | 3000 | | 7 | u4 | 4 | | 8 | u5 | 5 | | 9 | u6 | 6 | +----+------+------+ 6 rows in set (0.00 sec)
|
To proceed ６ in AUTO_INCREMENT
|
mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'test'; +----------------+ | AUTO_INCREMENT | +----------------+ | 13 | +----------------+ 1 row in set (0.01 sec) |
Apparently, don’t update all the data to register, AUTO_INCREMENT will proceed.
Solution
Delete identity column
Although it is not available to delete due to the limitation of data base user side, but you don’t have to think about “AUTO_INCREMENT” without “identity column”.
Divide into INSERT statement and UPDATE statement”
I found out that “AUTO_INCREMENT” is not updated UPDATE statement I it’s better to divide SQL.
Impression
I rarely encounter the issue like this in which the date amount reaches upper limit if I don’t handle huge data, so I think it was a good experience.
See you!
