| Bug #113219 | Wrong value of auto_increment while executing load data replace | ||
|---|---|---|---|
| Submitted: | 25 Nov 2023 13:05 | Modified: | 6 Dec 2023 8:02 |
| Reporter: | Wenliang Xu | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | MySQL8.0.34，MySQL8.2.0 | OS: | Linux |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[26 Nov 2023 0:54]
Wenliang Xu
I have installed a MySQL 8.0.34 instance and make the same tests. The results are as follows:
Master:
mysql> select id from my_test order by id desc limit 5;
+--------+
| id |
+--------+
| 100000 |
| 99999 |
| 99998 |
| 99997 |
| 99996 |
+--------+
5 rows in set (0.00 sec)
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=115536 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql>
mysql>
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='my_test';
+----------------+
| AUTO_INCREMENT |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
Slave:
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select id from my_test order by id desc limit 5;
+--------+
| id |
+--------+
| 100000 |
| 99999 |
| 99998 |
| 99997 |
| 99996 |
+--------+
5 rows in set (0.00 sec)
mysql> select auto_increment from information_schema.tables where table_schema='test' and table_name='my_test';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 100001 |
+----------------+
1 row in set (0.00 sec)
[27 Nov 2023 11:01]
MySQL Verification Team
HI Mr. Xu, Thank you for your bug report. However, you have set your category wrong. This is a problem in replication and not with DML. Next, your test case is not complete as your table does not have any rows. Last, but not least, MySQL 5.7 is not supported, so your entire reprlication setup has to consist of 8.0 servers or higher, like 8.1.0 and 8.2.0. Unsupported.
[27 Nov 2023 12:14]
Wenliang Xu
As you suggested, I installed the latest version of MySQL 8.2.0 and set up a replication relationship between a master and a slave. I performed the following operations:
1.Created a table as follows:
CREATE TABLE my_test(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
school VARCHAR(50),
address VARCHAR(50),
UNIQUE KEY idx_uni(name,school)
);
2.On Master,Inserted 50,000 records and executed "show create table my_test\G" to check the value of auto_increment. At this point, the auto_increment value is equal to max(id)+1, and it is consistent between the master and slave.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select count(*) from my_test;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.01 sec)
mysql> select * from my_test order by id desc limit 5;
+-------+----------------------------------------------------+-------------------------------------------------+----------------------------------------------------+
| id | name | school | address |
+-------+----------------------------------------------------+-------------------------------------------------+----------------------------------------------------+
| 50000 | quod veniam cumque sit alias aliquam quam. | ab corrupti dicta consectetur magnam occaecati. | ea repellendus dicta! |
| 49999 | alias sed atque voluptatem provident. | enim illum ea occaecati iste amet nobis quod. | eum repudiandae quaerat omnis incidunt commodi qui |
| 49998 | magni ut dolorem sunt enim. | doloribus eveniet voluptatem asperiores et. | dicta nisi sed facere cumque voluptas vitae! |
| 49997 | veniam explicabo quo et ducimus. | dolorum omnis laboriosam eius aut. | magni excepturi iure voluptates nemo sapiente. |
| 49996 | dolor rerum saepe beatae architecto porro aut qui | sed voluptas est iste aut est voluptate! | nostrum reprehenderit vitae et provident aperiam i |
+-------+----------------------------------------------------+-------------------------------------------------+----------------------------------------------------+
5 rows in set (0.00 sec)
3.On Master,I exported the data to a CSV file and imported it using "load data infile replace".
SELECT name, school, address
INTO OUTFILE '/root/test/my_test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_test;
LOAD DATA INFILE '/root/test/my_test.csv'
REPLACE INTO TABLE my_test
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, school, address);
mysql> SELECT name, school, address
-> INTO OUTFILE '/root/test/my_test.csv'
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> FROM my_test;
Query OK, 50000 rows affected (0.03 sec)
mysql> LOAD DATA INFILE '/root/test/my_test.csv'
-> REPLACE INTO TABLE my_test
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> (name, school, address);
Query OK, 100000 rows affected (2.82 sec)
Records: 50000 Deleted: 50000 Skipped: 0 Warnings: 0
4.Upon executing "show create table my_test\G" again, the auto_increment value is no longer equal to max(id)+1, and the auto_increment values in the table structure of the master and slave are inconsistent.
Master:
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=115536 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select count(*) from my_test;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.00 sec)
Slave:
mysql> show create table my_test\G;
*************************** 1. row ***************************
Table: my_test
Create Table: CREATE TABLE `my_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
`address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uni` (`name`,`school`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> select count(*) from my_test;
+----------+
| count(*) |
+----------+
| 50000 |
+----------+
1 row in set (0.02 sec)
5.Therefore, I believe that the "load data replace" operation caused two issues.
First, it led to the auto_increment value not being equal to max(id)+1.
Second, it caused the auto_increment values of the master and slave to be inconsistent.
[28 Nov 2023 1:17]
Wenliang Xu
I sincerely suggest you test according to my instructions.
[6 Dec 2023 7:14]
MySQL Verification Team
Hi, I am not sure what you consider a bug here? > the auto_increment value is no longer equal to max(id)+1 Nothing in SQL standard or in MySQL promises this to be true. Auto-increment value on main server just guarantee if you have inserts that they will get non-existing id in case you are only inserting with auto values, even if you manually insert a value we do not any more guarantee this will be true. The max(id)+1 is the value set when the server is restarted and table used for the first time, it is not value guaranteed at any point and even that (restart->first use) can change without notice as that is not part of any standard and is not promised by the system. So value of this metadata is not guaranteed. On main the insert should pass. If you can show that insert on master do not pass we can see if that is maybe a bug. That same insert is propagated to replica with ID value so metadata on replica is irrelevant. Again, if you can show us the scenario where this is relevant we can consider if it is a bug or not. Thanks for the report and all the testing done.
[6 Dec 2023 7:53]
Wenliang Xu
Indeed, in MySQL 8.2.0, the inconsistency of auto_increment values between master and slave instances does not affect master-slave synchronization. However, if the auto_increment values between master and slave can be made consistent, it will provide a better experience feeling. This can be achieved with just one command, similar to "alter table table_name auto_increment= max(id)+1".
[6 Dec 2023 8:02]
MySQL Verification Team
Hi, > This can be achieved with just one command, similar to "alter table table_name auto_increment= max(id)+1". That is a very high performance killing procedure hence doing it just for a "feeling" makes not much sense.
Description: The execution of load data infile replace into operation in MySQL results in the following issues: 1.When execute "show create table ..." on Master and Slave,you will find the value of auto_increment is inconsistent. 2.the max value of the auto-increament column is 115536,while the value of auto_increment is 50001 when you execute "show create table ..." How to repeat: 1. Set up semi-sync replication with 1 master and 1 slave. 2. On master, create table CREATE TABLE my_test( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), school VARCHAR(50), address VARCHAR(50), UNIQUE KEY idx_uni(name,school) ); 3.On master ,insert some data,and execute "show create table" to look the value of auto_increment For example: mysql> show create table my_test\G; *************************** 1. row *************************** Table: my_test Create Table: CREATE TABLE `my_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_uni` (`name`,`school`) ) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) ERROR: No query specified mysql> select count(*) from my_test; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.01 sec) mysql> select id from my_test order by id desc limit 5; +-------+ | id | +-------+ | 50000 | | 49999 | | 49998 | | 49997 | | 49996 | +-------+ 5 rows in set (0.00 sec) Now,Master and Slave is normal 4.On master,execute: SELECT name, school, address INTO OUTFILE '/root/test/my_test.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM my_test; LOAD DATA INFILE '/root/test/my_test.csv' REPLACE INTO TABLE my_test FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (name, school, address) 5.On Master and Slave, you can find: Master: mysql> show create table my_test\G; *************************** 1. row *************************** Table: my_test Create Table: CREATE TABLE `my_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_uni` (`name`,`school`) ) ENGINE=InnoDB AUTO_INCREMENT=115536 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) ERROR: No query specified mysql> select id from my_test order by id desc limit 5; +--------+ | id | +--------+ | 100000 | | 99999 | | 99998 | | 99997 | | 99996 | +--------+ 5 rows in set (0.01 sec) Slave: mysql> show create table my_test\G; *************************** 1. row *************************** Table: my_test Create Table: CREATE TABLE `my_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `school` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `address` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `idx_uni` (`name`,`school`) ) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) ERROR: No query specified mysql> select id from my_test order by id desc limit 5; +--------+ | id | +--------+ | 100000 | | 99999 | | 99998 | | 99997 | | 99996 | +--------+ 5 rows in set (0.00 sec) 6.the replication seems normal when you execute "show slave status\G",indeed abnormal more ,I only make a test for MySQL 5.7.28,You can make a test for other versions.