MySQL Bugs: #102540: A updated-stmt can be repeated with no rand() or now()

Bug #102540	A updated-stmt can be repeated with no rand() or now()
Submitted:	9 Feb 2021 9:28	Modified:	9 Feb 2021 15:58
Reporter:	guozhi li	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	updated-stmt repeated

Description:
Instead of updating the fields that need to be updated, the fields that do not need to be updated are updated；

means：
  [step3] and [step4] 
  after execution, a line of modification is always returned

It will affect the binlog and IO threads, but in essence, this update can only be executed once。
Repetition should not be effective。 
We got a lot of binlog and full io stack，but it was nomeaningful。We can avoid it, but it should not happen.

How to repeat:
step1:
CREATE TABLE `nulltest` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT 'test',
  `age` int(11) DEFAULT '18',
  `created` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),  
`modified` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
step2:
insert into nulltest(id) select null;

step3:
update nulltest set name=null where id=1;
step4:
update nulltest set age=null where id=1;

Suggested fix:
Make it right

always Updated Rows = 1

Attachment: 截图录屏_选择区域_20210209173205.png (image/png, text), 34.17 KiB.

always Updated Rows = 1

Attachment: 截图录屏_选择区域_20210209173205.png (image/png, text), 34.17 KiB.

Hello, 

I'm not part of the verification team, my colleague will process this as he does all time of course, but I've a comment...

You add a record without specifying the name and the age (then default is used, isn't ?) Could you do a select after you have inserted ?

Then you change the value of these column to null (from default I would guess)... so it's normal that a row is updated, isn't it ?

Could you add select statement after each steps ?

Thank you.

this is very simple；
age or name is not updated，but in binlog，other field updated。
what ever age or name is not null；

update nulltest set name=null,age=18 where id=1;
Updated Rows	1

{
"select * from nulltest": [
	{
		"id" : 1,
		"name" : null,
		"age" : 18,
		"created" : "2021-02-09 17:30:43.832",
		"modified" : "2021-02-09 18:04:14.229"
	}
]}

update nulltest set name=null,age=18 where id=1;
Updated Rows	1
{
"select * from nulltest": [
	{
		"id" : 1,
		"name" : null,
		"age" : 18,
		"created" : "2021-02-09 17:30:43.832",
		"modified" : "2021-02-09 18:06:34.252"
	}
]}

OK, thank you for clarification, but in fact:

`modified` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

is part of the record so when you modify it (the record, even if other values don't change) it gets modified automatically, null or not null.

update nulltest set name='test',age=18 where id=1; Will not update twice！
if table field defined set Default null,update nulltest set name=null,age=18 where id=1,will not update twice!
So I think it is a bug!

Hi, 

Thank you for clarification. This helps to reproduce ;)

Binlog Position: 3930

update nulltest set name='test',age=18 where id=1;
Rows matched: 1  Changed: 0  Warnings: 0

Binlog Position: 3930

Hi Mr. li,

Thank you for your bug report.

Verified as reported.

Thank you. 

Do note that we do not process reports for products that are not ours.