Can't save CURRENT_TIMESTAMP ON UPDATE

Can't save CURRENT_TIMESTAMP ON UPDATE of type field DATETIME

Is there some error message?

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

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:

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`;

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

That

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?

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

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;

Yes, I see "DEFAULT_GENERATED on update CURRENT_TIMESTAMP"

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.

I just updated, but... :-)

But with 5.*-th version all right

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

Please update once more and retry.

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...

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

Thanks! Works fine! :-)

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
)
;

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.

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

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.

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.

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

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

See official ticket #1910 for more details.

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