MySQL Bugs: #112852: insert and insert onduplicate are executed concurrently, data update error

Bug #112852	insert and insert onduplicate are executed concurrently, data update error
Submitted:	27 Oct 2023 7:42	Modified:	6 Nov 2023 4:28
Reporter:	lynn feng	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
Condition: There are auto-incrementing primary key and unique key constraints in the table structure
trigger:
1）Specify the maximum auto-increment primary key to insert in the insert statement
如：insert into question set id = 7297883585, content = '<div><img src="189ab0a017f09e2.jpg+7297883585"></div>', fingerprint = -2724991434728162742
2）If insert on duplicate has a unique key conflict, it will trigger the update of historical unique key conflict data
如：insert into question set content = '<div><img src="189ab09946fe332.jpg"></div>', answer = 'See answer above', fingerprint = 5590874729754171300 on duplicate key update content = '<div><img src="189ab09946fe302.jpg+5590874729754171300"></div>', answer = 'See answer above+5590874729754171300+77813'

When 1) and 2) are executed concurrently, an update exception will be triggered, that is:
The only conflicting data row in 2) was not updated, but the newly inserted data in 1) was updated.

How to repeat:
1）Create table
CREATE TABLE `question` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `content` text,
  `answer` text,
  `fingerprint` bigint(20) DEFAULT NULL,
  `ctime` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'creation time',
  `utime` timestamp(6) NOT NULL DEFAULT '1970-01-01 08:00:01.000000' ON UPDATE CURRENT_TIMESTAMP(6) COMMENT 'Real time update time',
  PRIMARY KEY (`id`),
  UNIQUE KEY `fingerprint` (`fingerprint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
 
CREATE TABLE `tmp_id` (
  `id` bigint(20) DEFAULT NULL,
  `fingerprint` bigint(20) DEFAULT NULL,
  `batch` int(11) DEFAULT NULL,
  `tag` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

recurrent

Attachment: 1、Create table (application/octet-stream, text), 42.62 KiB.

Hi Mr. feng,

Thank you for your bug report.

However, we were not able to repeat what you are reporting.

These are results that we get and they are quite fine:

+-----+---------------------------------------------------------------+-------------------------------------------+----------------------+----------------------------+----------------------------+
| id  | content                                                       | answer                                    | fingerprint          | ctime                      | utime                      |
+-----+---------------------------------------------------------------+-------------------------------------------+----------------------+----------------------------+----------------------------+
| 224 | <div><img src="189ab09946fe302.jpg+719975359453870400"></div> | See answer above+719975359453870400+7751  | -2247241073967007283 | 2023-10-27 13:36:11.477071 | 2023-10-27 13:36:11.476663 |
| 230 | <div><img src="189ab09946fe302.jpg+698859341221583013"></div> | See answer above+698859341221583013+2725  | -7452004675098436078 | 2023-10-27 13:36:11.494638 | 2023-10-27 13:36:11.497764 |
| 232 | <div><img src="189ab09946fe302.jpg+698859341222101263"></div> | See answer above+698859341222101263+16837 | -5671355973865306157 | 2023-10-27 13:36:11.497253 | 2023-10-27 13:36:11.497950 |
| 238 | <div><img src="189ab09946fe302.jpg+698859341219378038"></div> | See answer above+698859341219378038+8176  | -7303606844652800355 | 2023-10-27 13:36:11.514343 | 2023-10-27 13:36:11.517408 |
| 246 | <div><img src="189ab09946fe302.jpg+698859341220277438"></div> | See answer above+698859341220277438+13628 | -1589798219956855267 | 2023-10-27 13:36:11.534845 | 2023-10-27 13:36:11.537582 |
| 376 | <div><img src="189ab09946fe302.jpg+698859341219774263"></div> | See answer above+698859341219774263+16605 | -3081336288759770229 | 2023-10-27 13:36:11.953405 | 2023-10-27 13:36:11.998287 |
| 386 | <div><img src="189ab09946fe302.jpg+698859341221224988"></div> | See answer above+698859341221224988+9762  | -4960396468119259571 | 2023-10-27 13:36:12.044095 | 2023-10-27 13:36:12.043622 |
+-----+---------------------------------------------------------------+-------------------------------------------+----------------------+----------------------------+----------------------------+

Can't repeat.

Thanks for your attention to this issue
In my script, I only inserted the IDs greater than 200, and the answer column was empty. However, after concurrent insertion and insert on duplicate updates, there was a value in the answer column, indicating that the rows with IDs greater than 200 had data updated by others. Not as expected

Hi,

We are heppy to learn that the problem is resolved.

Hello
The problem has not been solved, and the data update is disordered, which seriously affects the use.
Through my script, the problem can be completely reproduced. I hope to continue to pay attention to this problem and fix it.

Hi Mr. Feng,

We would have preferred to get a fully reproducible test case from you .....

That would enable us to check whether it is a bug or not.

Hello 
According to my script, the problem can be reproduced stably.
The script mainly simulates the concurrent scenarios of insert (specifying auto-increment id) and insert update (unique key conflict).
Because in our online business use, we encountered this scenario and triggered a serious problem of abnormal online data update.
Please use my script to reproduce the problem

Hi,

But we have sent you the output from the script on our side and as you could see, it works just fine on our MySQL installations.

hello
According to the results you output, it means that the data update is abnormal.
If the data has not been updated by mistake, the result set will not be found.

Please look carefully at the sql statement in the script. In the statement inserted based on the auto-incremented ID, the answer field is not specified at all. However, in the query result set, why are all the answer fields updated with data?

Hi,

Sorry but we do not understand what you are saying.

Please analyse our results and let us know exactly and very precisely what is wrong there.

According to the results of your last reproduction, you can see that the IDs greater than 200 are all new data added by insert. However, you can open the general log or binlog and see that this data is not only added, but also updated once, and the script There is no update statement for this data in

Hi,

We just ran your SQL file and your bash script.

We got the results that we got.

This could be due to the fact that we use latest 8.0 release or a different bash version.

It is our opinion that your script is not written well.

Try to write what you want to achieve in C API, for example.

Actually, you can do fully in SQL, with user variables or within a stored routine !!!!!

Hence, your test case is not reliable.

Can't repeat.

By obtaining the script results, it is shown that in this scenario, the MySQL database update is chaotic and the data is unreliable;
I feel that our focus is different. We want to improve the product and focus on the product problem itself, rather than the method to reproduce it;

Hi,

We think that there is a possible error in your bash script.

We accept test cases in SQL, not in bash scripts. Not when there are tools in SQL that can do the same job.

You can write your entire test case in SQL , by using stored procedure.

When we get such a test case and repeat it, we shall be very happy to verify your report.

We are eagerly waiting on your new test case.

Hi,

We shall try to generate SQL from your bash script . SQL with number of INSERT's .......

Stay tuned !!!!!!

Hi,

We have made SQL script out of your bash and got the following result:

1	<div><img src="189ab09946fe302.jpg+698859341222985088"></div>	See answer above+698859341222985088+92104	698859341222985088	2023-09-05 18:32:26.457972	2023-10-08 15:28:25.458286
2	<div><img src="189ab09946fe302.jpg+698859341222070538"></div>	See answer above+698859341222070538+11996	698859341222070538	2023-09-05 17:44:58.056251	2023-09-07 19:44:47.653207
3	<div><img src="189ab0a017f09e2.jpg+7297748351"></div>	NULL	698859341221854388	2023-09-05 17:44:45.041058	2023-09-07 18:49:36.138632
4	<div><img src="189ab09946fe302.jpg+698859341219923788"></div>	See answer above+698859341219923788+39204	698859341219923788	2023-08-15 17:18:09.693945	2023-09-07 19:44:47.449329
5	<div><img src="189ab09946fe302.jpg+698859341220645388"></div>	See answer above+698859341220645388+2716	698859341220645388	2023-09-05 17:40:25.606946	2023-11-03 13:51:08.015660
6	<div><img src="189ab09946fe302.jpg+698859341219273513"></div>	See answer above+698859341219273513+8109	698859341219273513	2023-08-14 18:30:44.775196	2023-11-03 13:51:08.149357
7	<div><img src="189ab09946fe302.jpg+698859341222886288"></div>	See answer above+698859341222886288+14528	698859341222886288	2023-09-05 17:46:02.843500	2023-11-03 13:51:08.112067
8	<div><img src="189ab09946fe302.jpg+698859341220708213"></div>	See answer above+698859341220708213+33408	698859341220708213	2023-09-05 17:40:32.403540	2023-10-08 15:28:25.584390
9	<div><img src="189ab09946fe302.jpg+698859341219746638"></div>	See answer above+698859341219746638+48895	698859341219746638	2023-08-14 19:02:32.571480	2023-10-08 15:28:25.613523
10	<div><img src="189ab09946fe302.jpg+698859341222357138"></div>	See answer above+698859341222357138+86888	698859341222357138	2023-09-05 17:45:17.538188	2023-10-08 15:28:25.627171
11	<div><img src="189ab09946fe302.jpg+698859341219325163"></div>	See answer above+698859341219325163+34592	698859341219325163	2023-08-14 18:32:27.747098	2023-09-07 19:44:47.635405
12	<div><img src="189ab09946fe302.jpg+698859341222541713"></div>	See answer above+698859341222541713+6292	698859341222541713	2023-09-05 17:45:31.332849	2023-11-03 13:51:08.147481
13	<div><img src="189ab09946fe302.jpg+698859341222723713"></div>	See answer above+698859341222723713+14586	698859341222723713	2023-09-05 17:45:46.428179	2023-11-03 13:51:07.972677
14	<div><img src="189ab09946fe302.jpg+698859341220468238"></div>	See answer above+698859341220468238+11802	698859341220468238	2023-09-05 17:40:06.352991	2023-11-03 13:51:08.103413
15	<div><img src="189ab09946fe302.jpg+698859341221288663"></div>	See answer above+698859341221288663+36461	698859341221288663	2023-09-05 17:41:35.888135	2023-09-07 19:44:47.643343
16	<div><img src="189ab0a017f09e2.jpg+7297686815"></div>	NULL	698859341221085188	2023-09-05 17:41:13.321419	2023-09-07 18:49:36.138632
17	<div><img src="189ab0a017f09e2.jpg+7297731911"></div>	NULL	698859341221648888	2023-09-05 17:42:17.220748	2023-09-07 18:49:36.138632
18	<div><img src="189ab09946fe302.jpg+698859341221523988"></div>	See answer above+698859341221523988+19979	698859341221523988	2023-09-05 17:42:02.390459	2023-11-03 13:51:08.122491
19	<div><img src="189ab0a017f09e2.jpg+7297562103"></div>	NULL	698859341219526288	2023-08-14 18:44:42.486630	2023-09-07 18:49:36.138632
20	<div><img src="189ab09946fe302.jpg+698859341219716588"></div>	See answer above+698859341219716588+76524	698859341219716588	2023-08-14 19:02:29.625329	2023-10-08 15:28:25.500643
21	<div><img src="189ab0a017f09e2.jpg+7297623383"></div>	NULL	698859341220292288	2023-09-05 16:40:50.427518	2023-09-07 18:49:36.138632
22	<div><img src="189ab09946fe302.jpg+698859341221608538"></div>	See answer above+698859341221608538+95723	698859341221608538	2023-09-05 17:42:12.362389	2023-09-07 19:44:47.595772
23	<div><img src="189ab09946fe302.jpg+698859341219378038"></div>	See answer above+698859341219378038+24522	698859341219378038	2023-08-14 18:32:33.671140	2023-11-03 13:51:08.128736
<skip 100 rows>
398	<div><img src="189ab0a017f09e2.jpg+398"></div>	NULL	-5849472380298657561	2023-11-03 13:51:08.150515	1970-01-01 08:00:01.000000
400	<div><img src="189ab0a017f09e2.jpg+400"></div>	NULL	-7102641966071135650	2023-11-03 13:51:08.151177	1970-01-01 08:00:01.000000

this is definitely wrong.

This is now a verified bug.

This is obviously not the result of step 4 of my script

In step 4, only result sets with ID greater than 200 will be found.
1. Data rows with ID greater than 200 are newly inserted with specified ID. When inserting, the answer field is set to null value.
2. If the id is greater than 200 and the answer is not null, it means that there is an exception inside MySQL, causing the data update to be disordered.

Hi,

Your comments have been forwarded to the Development team in charge.