This story is counter stop to use ON DUPLICATE KEY UPDATE with MySQL

f:id:R-Hack:20200131122933j:plain

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.

  1. “AUTO_INCREMENT” processed unintentionally
  2. Reach the maximum that can be stored with int
  3. 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 6 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!