Solved SQL Error (1067): Invalid default value CURRENT_TIMESTAMP

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

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,

hvanmegen's profile image hvanmegen posted 4 weeks ago Permalink

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

jprinzler's profile image jprinzler posted 1 week ago Permalink

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 :-(

hvanmegen's profile image hvanmegen posted 1 week ago Permalink

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

Description

It seems to have been fixed!

jprinzler's profile image jprinzler posted 1 week ago Permalink

Unfortunately it's still the same Description

on MySQL v8.0.036

jprinzler's profile image jprinzler posted 1 week ago Permalink

Description

jprinzler's profile image jprinzler posted 1 week ago Permalink

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;

ansgar's profile image ansgar posted 5 days ago Permalink

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

See official ticket #1910 for more details.

jprinzler's profile image jprinzler posted 4 days ago Permalink

Thanx. It works now. 👍

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