Can't save CURRENT_TIMESTAMP ON UPDATE

NeHaLeM's profile image NeHaLeM posted 4 years ago in General Permalink

Can't save CURRENT_TIMESTAMP ON UPDATE of type field DATETIME

ansgar's profile image ansgar posted 4 years ago Permalink

Is there some error message?

NeHaLeM's profile image NeHaLeM posted 4 years 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

ansgar's profile image ansgar posted 4 years 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 4 years 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 4 years ago Permalink

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

NeHaLeM's profile image NeHaLeM posted 4 years ago Permalink

That

1 attachment(s):
  • 2020-04-23_17-11
ansgar's profile image ansgar posted 4 years 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 4 years 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 4 years 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;
NeHaLeM's profile image NeHaLeM posted 4 years ago Permalink

Yes, I see "DEFAULT_GENERATED on update CURRENT_TIMESTAMP"

1 attachment(s):
Code modification/commit 849a50c from Ansgar Becker <anse@heidisql.com>, 4 years ago, revision 11.0.0.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 4 years 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 4 years ago Permalink

I just updated, but... :-)

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

But with 5.*-th version all right

1 attachment(s):
  • unknown
ansgar's profile image ansgar posted 4 years 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 4 years 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>, 4 years ago, revision 11.0.0.5967
Forgotten file change for previous commit. See https://www.heidisql.com/forum.php?t=36100
ansgar's profile image ansgar posted 4 years ago Permalink

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

NeHaLeM's profile image NeHaLeM posted 4 years ago Permalink

Thanks! Works fine! :-)

rfiorav's profile image rfiorav posted 8 months ago Permalink

Hello, I think this problem was solved years ago, but it seems it "resuscitated".

-- I have this table

CREATE TABLE `Test_table` (
    `Last_Update` DATETIME NULL,
    `Creation_Date` DATETIME NULL,
    `Operator` VARCHAR(30) NULL DEFAULT NULL
)
;

-- Done to alter Datetime field defaults

ALTER TABLE `Test_table`
    CHANGE COLUMN `Last_Update` `Last_Update` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CHANGE COLUMN `Creation_Date` `Creation_Date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

-- Expected result

CREATE TABLE `Test_table` (
    `Last_Update` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `Creation_Date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    `Operator` VARCHAR(30) NULL DEFAULT NULL
)
;

-- Result as showed by HeidiSQL 12.5.0.6694 - Win 10 on MySQL 8.0.34

CREATE TABLE `Test_table` (
    `Last_Update` DATETIME NULL **DEFAULT 'CURRENT_TIMESTAMP'** ON UPDATE CURRENT_TIMESTAMP,
    `Creation_Date` DATETIME NULL **DEFAULT 'CURRENT_TIMESTAMP'**,
    `Operator` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
;

-- Same Result as showed by MySQL Workbench 8.0.34 - Win 10 on MySQL 8.0.34

CREATE TABLE `Test_table` (
  `Last_Update` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Creation_Date` datetime DEFAULT CURRENT_TIMESTAMP,
  `Operator` varchar(30) DEFAULT NULL
)
;
ansgar's profile image ansgar posted 8 months ago Permalink

I suppose the single quotes in DEFAULT 'CURRENT_TIMESTAMP' are inserted due to the missing parenthesis after CURRENT_TIMESTAMP, which may again only be valid for MariaDB. I'll go and try to find the cause.

rfiorav's profile image rfiorav posted 8 months ago Permalink

Thank you very much, Ansgar.

The MySQL 8 manual refers to the subject in https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

deepblue's profile image deepblue posted 8 months ago Permalink

MySQL 8.0.27 Heidi 12.5.0.6694

For some reason there is an inability to table copy in the front-end due to this CURRENT_TIMESTAMP default / update issue.

When I view any tables in the frontend that have a column with a default/update setup as CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , Heidi appears to think the initial default value is actually custom text and not an expression. As it shows up as 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP in the table editor. Rather than the expected CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

The SQL create code to test:

CREATE TABLE IF NOT EXISTS `test` (
  `gmt_datetime` datetime NOT NULL,
  `runtype` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`gmt_datetime`) USING BTREE,
  KEY `gmt_datetime` (`gmt_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

I get a 1064 syntax error if I try and manually correct by copying and pasting the CURRENT_TIMESTAMP from the Custom text box to the Expression box in the table editor. I have also tried replacing CURRENT_TIMESTAMP with CURRENT_TIMESTAMP() and NOW() in the create query resulting in the same issues.

To create an empty table copy I need to Export Database as SQL (Table(s): Create Checked ; No Data) to clipboard and then adjust the query. Since Create New > Table Copy through Heidi straight after creating the test table above errors due to syntax.

1 attachment(s):
  • test
hvanmegen's profile image hvanmegen posted 4 months ago Permalink

I'm having the same issue; Every time I have to edit a table which has a created_at and/or updated_at TIMESTAMP field with CURRENT_TIMESTAMP in the Default value, HeidiSQL is quoting it and turns it into a string, which is invalid.

ansgar's profile image ansgar posted 4 months ago Permalink

Thanks for the reports. Heard that several times now, so I will definitely have a look at that.

ansgar's profile image ansgar posted 3 months ago Permalink

Should be fixed in the next build, available in one hour.

See official ticket #1910 for more details.

deepblue's profile image deepblue posted 3 months ago Permalink

I can confirm that this is now fixed with HeidiSQL 12.6.0.6765. Thanks!

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