I didn't do a query. I used the UI to set the default to CURRENT_TIMESTAMP(6) as the initial migration was incorrect.
But, starting from scratch with this table creation SQL (FK's removed) ...
CREATE TABLE `fields` (
`fieldID` INT(11) NOT NULL AUTO_INCREMENT,
`ref` VARCHAR(128) NOT NULL,
`name` VARCHAR(255) NOT NULL,
`eventCatID` INT(11) NULL DEFAULT NULL,
`ticketID` INT(11) NULL DEFAULT NULL,
`type` ENUM('checkbox','radiobutton','select','textarea','dateofbirth','textbox') NOT NULL DEFAULT 'textbox',
`required` TINYINT(1) NOT NULL DEFAULT '0',
`requiredEpos` TINYINT(1) NOT NULL DEFAULT '0',
`navOrder` INT(11) NOT NULL DEFAULT '10000',
`status` ENUM('Active','Inactive','Archived','Deleted') NOT NULL DEFAULT 'Active',
`userID` INT(11) NOT NULL,
`updatedAt` TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`fieldID`)
COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;
There are a few issues with this migration. 1 - The column `addedAt` should have been called `createdAt` and the default for that column should have been NOT NULL DEFAULT CURRENT_TIMESTAMP(6).
So that is what I was manually attempting to make sure things would work prior to creating the migration.
So. Into HeidiSQL and open the table and I see a problem.
The column `addedAt` has a default of
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
and the column `updatedAt` has a default of
NULL ON UPDATE CURRENT_TIMESTAMP
The `addedAt` column should not have an ON UPDATE clause (it didn't in the above table creation SQL). And where there is a default or ON UPDATE clause, it should be
So trying to correct this by setting the default to be CURRENT_TIMESTAMP(6) in the UI and unchecking the ON UPDATE CURRENT_TIMESTAMP() checkbox, saving this results in ...
SQL Error (1067): Invalid default value for 'addedAt'
This is all via the UI, not via manually constructed SQL statements.
So. Actually 2 bugs.
Firstly, the duplicate ON UPDATE clause and the lack of acceptance of CURRENT_TIMESTAMP(6) as a default.