Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Can't save CURRENT_TIMESTAMP ON UPDATE

NeHaLeM's profile image NeHaLeM posted 1 month ago in General Permalink

Can't save CURRENT_TIMESTAMP ON UPDATE of type field DATETIME

ansgar's profile image ansgar posted 1 month ago Permalink

Is there some error message?

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

No, but field "On update" always empty. If I copy code from tab Code ALTER and execute script then field updatable on change row, but field "On update" empty again.

Night build 5964

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 1 month ago Permalink

Perhaps you could post what HeidiSQL shows in the bottom SQL log. I just tried to reproduce that, and added an ON UPDATE clause:

ALTER TABLE `test_table`
    CHANGE COLUMN `ts` `ts` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP() AFTER `field1`;

And the table designer shows that afterwards:

Description

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink
ALTER TABLE `allowance_obrashenie_tmp` CHANGE COLUMN `date_modif` `date_modif` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP() COMMENT 'Дата обновления' AFTER `real_room_sym`;
1 attachment(s):
  • 2020-04-23_15-20
ansgar's profile image ansgar posted 1 month ago Permalink

And what does a SHOW CREATE TABLE allowance_obrashenie_tmp show, after that change?

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

That

1 attachment(s):
  • 2020-04-23_17-11
ansgar's profile image ansgar posted 1 month ago Permalink

Oh that's indeed a sign that HeidiSQL's ALTER query does what it should do. But the table definition is probably still cached. Pressing F5 in the left tree solves it I guess?

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

No, but it works. Then do not forget to set CURRENT_TIMESTAMP() when I edit this field) Thanks.

ansgar's profile image ansgar posted 1 month ago Permalink

Perhaps the detection is still wrong, for that particular server version.

I see you're on MySQL 8.0.19, and I tested the same on MySQL 8.0.11, but it works as expected here.

Could you please post the result of the following query:

SELECT * FROM `information_schema`.`COLUMNS`
WHERE TABLE_SCHEMA='uszn_group'
  AND TABLE_NAME='allowance_obrashenie_tmp'
ORDER BY ORDINAL_POSITION;
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

Yes, I see "DEFAULT_GENERATED on update CURRENT_TIMESTAMP"

1 attachment(s):
Code modification/commit 849a50c from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 5966
Make regular expressions for detecting attributes in EXTRA column of IS.COLUMNS slightly more lose, so a "default_generated" does not break detection of "on update ...". Also allow case insensitive searching. See https://www.heidisql.com/forum.php?t=36100
ansgar's profile image ansgar posted 1 month ago Permalink

That was it! I suppose.. I just pushed a change, which allows the two clauses besides each other in that extra field. Crossing fingers the next build works.

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

I just updated, but... :-)

2 attachment(s):
  • 1
  • 2
NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

But with 5.*-th version all right

1 attachment(s):
  • unknown
ansgar's profile image ansgar posted 1 month ago Permalink

You were not using the latest build, but the prior one:

Description

Please update once more and retry.

ansgar's profile image ansgar posted 1 month ago Permalink

Shit, I just see I have a compile error in the latest build, which is most likely the reason for your prior build. Have to fix that...

Code modification/commit b2ed802 from Ansgar Becker <anse@heidisql.com>, 1 month ago, revision 5967
Forgotten file change for previous commit. See https://www.heidisql.com/forum.php?t=36100
ansgar's profile image ansgar posted 1 month ago Permalink

So, got it now... hopefully. Please update a last time now.

NeHaLeM's profile image NeHaLeM posted 1 month ago Permalink

Thanks! Works fine! :-)

Please login to leave a reply, or register at first.