CODEWITHSUNDEEP
mysqlsqltimestampsql-updateprepared-statement
Udo Polder
Udo Polder

Reputation: 3

MySQL 5.7 prepared statements updating the wrong timestamp column

I'm having some issues with an update on table, when used with a prepared statement. Seems like mysql is updateing a wrong column which was not even specifed in the update command.

prepare:

drop table test1;
create table test1(
  id int not null,
  show_from timestamp not null,
  updated_at timestamp NULL DEFAULT NULL
);
insert into test1(id, show_from, updated_at) values(1, '2018-01-11 12:10:11.19808', '2019-04-15 11:50:00.704748');

do this in one batch:

UPDATE test1 SET show_from='2018-04-15 11:50:00.704748' WHERE id=1;
SELECT * FROM test1;

returns:

1    2018-04-15 13:50:01    2019-04-15 13:50:01

as expected.

Now do this in one batch:

PREPARE stmt1 FROM 'UPDATE test1 SET updated_at=? WHERE id=?';
SET @s1='2019-02-11 12:12:11.19808';
SET @s2=1;
EXECUTE stmt1 USING @s1, @s2;
DEALLOCATE PREPARE stmt1;
SELECT * FROM test1;

returns:

1    2019-04-15 12:54:27    2019-02-11 13:12:11

Why did mysql update the show_from column?

Upvotes: 0

Views: 198

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272406

The column is declared timestamp, not null:

show_from timestamp not null

In this mode, MySQL will update the column every time the row (any of its columns) is updated. From the manual:

If the explicit_defaults_for_timestamp system variable is disabled, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

The documentation suggests workarounds such as:

  • Define the column with a DEFAULT clause that specifies a constant default value.

  • Specify the NULL attribute. This also causes the column to permit NULL values, which means that you cannot assign the current timestamp by setting the column to NULL.

I would actually recommend this:

  • Enable the explicit_defaults_for_timestamp system variable.

Upvotes: 1

Related Questions