Solved SQL Error (1067): Invalid default value CURRENT_TIMESTAMP

Not sure if this issue has been solved but I found no solution in the latest. I ran across this when I tried making a copy of a table with CURRENT_TIMESTAMP as the default. Here's the code generated by heidi:

CREATE TABLE `sample_copy` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COLLATE 'utf8mb3_general_ci',
    `lastedit` TIMESTAMP NOT NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP,
    `notes` JSON NULL DEFAULT NULL,
    `data` JSON NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;

This query will fail because CURRENT_TIMESTAMP is inside single quotes. Remove the quotes and the query will succeed. The lastedit column should look like this:

`lastedit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

I'm having the same issue.. my MySQL version is 8.0.35-0ubuntu0.22.04.1

Whenever I edit a timestamp's default value and set it to the CURRENT_TIMESTAMP() expression and save and reload the table; it reverts back to a string 'CURRENT_TIMESTAMP' instead. It's asif HeidiSQL is not aware that CURRENT_TIMESTAMP is a reserved keyword.

I think that it's best resolved by retrieving the list of reserved keywords first with this query:

SELECT * FROM information_schema.KEYWORDS WHERE RESERVED=1;

and then deciding on how to handle generating the CREATE code based on what reserved keywords it finds.

This is Heidi's CREATE code:

CREATE TABLE `settings` (
    `key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `value` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `account_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP',
    `updated_at` TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`key`, `value`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

However, MySQL's way of rendering the CREATE code, shown with this query:

SHOW CREATE TABLE `email_responder`.`settings`;```:

.. results in this "Create Table" field value:

CREATE TABLE `settings` (
  `key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `account_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

This one has the correct default value for created_at and updated_at

Do you already have a solution? I thought you could add this to the "functions-mysql.ini" file, but that doesn't change anything. It's a little tiring to edit the CREATE code manually :-(

Right now I'm running 12.6.0.6788 and it doesn't seem to have this issue.

It seems to have been fixed!

Unfortunately it's still the same

on MySQL v8.0.036

I tested that once. HeidiSQL 12.6.0.6799

System mySQL 8.0.36-0ubuntu0.20.04.1

CREATE TABLE fuckingdates ( col1 DATETIME NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP, col2 TIMESTAMP NULL DEFAULT 'CURRENT_TIMESTAMP' ON UPDATE CURRENT_TIMESTAMP ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;

System Windows mySQL 10.4.10-MariaDB (XAMPP ;-() Sorry, I no longer have the 5.x DB version on Linux)

CREATE TABLE fuckingdates ( col1 DATETIME NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), col2 TIMESTAMP NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;

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

See official ticket #1910 for more details.

Thanx. It works now. 👍

Hi, I'm running 12.6.0.6765 which I believe is the latest release, and I still see this error. How is that possible?

Many thanks in advance,

Alex

Latest build is 6814, not 6765. Please look at the "nightly builds" section on the download page.

You can easily update in HeidiSQL, through Help > Check for updates > "Download and install build xyz".

Thank you ansgar for your patience with a silly question. I did not realize that I did not have the latest build. When I followed your instructions, the problem went away.

Alex