Not respecting DEFAULT CURRENT_TIMESTAMP(6).

Hi.

Using HeidiSQL V9.1.0.4901 with a 5.6.12 mysql installation on Windows.

Normally I use migrations (Phinx) to alter my DB. Needed to do a uber-quick correction locally (before doing it via a migration for the staging and production servers) and tried to set the default of a TIMESTAMP(6) column to CURRENT_TIMESTAMP(6).

HeidiSQL rejected this with the error "

Invalid default value for `createdAt`

".

Also, trying to do this via an SQL query is rejected as a SQL Error 1064.

Do the same work in MySQL Workbench or via the Phinx Migrations, all OK.

The default value is perfectly fine for the column type and local database as we are currently using it in our API to produce millisecond delta change sets for pushing to the remote devices. Basically it works.

Regards,

Richard.

Please post the SQL query you have fired and where you got the error 1064 from.

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,
`addedAt` TIMESTAMP(6),
`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

CURRENT_TIMESTAMP(6)

.

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.

Regards,

Richard.

AFAIK the default clause has to be simply CURRENT_TIMESTAMP, it does not mention the display size again. Only the data type definition (directly after the fieldname, eg `addedAt` TIMESTAMP(6)) may contain this.

Back to this after 2 years.

I have a working table.

If I use DESCRIBE orders I get ...

COLUMNS

describe orders;

orderID	int(11)	NO	PRI	\N	auto_increment
thirdPartyID	varchar(128)	YES		\N
companyID	int(11)	NO	MUL	\N
userID	int(11)	YES	MUL	\N
deviceID	int(11)	YES	MUL	\N
sellersUserID	int(11)	YES		\N
usergroupID	int(11)	YES		\N
affiliateID	int(8)	YES		\N
bookingRef	varchar(14)	NO	UNI
originalRef	varchar(14)	NO	MUL
total	decimal(10,2)	NO		\N
dateOnly	date	NO	MUL	\N
date	datetime	NO	MUL	\N
customerContactID	int(11)	YES	MUL	\N
billingAddressID	int(11)	YES	MUL	\N
customerAccountID	int(11)	YES	MUL	\N
paid	tinyint(1)	NO	MUL	0
distributionChannelID	int(11)	YES		\N
paymentChannelID	int(11)	YES	MUL	\N
redeemed	tinyint(1)	NO		0
redemptionDate	datetime	YES	MUL	\N
giftaid	tinyint(1)	NO		0
notes	mediumtext	YES		\N
createdAt	timestamp(6)	NO	MUL	CURRENT_TIMESTAMP(6)
updatedAt	timestamp	YES	MUL	\N	on update CURRENT_TIMESTAMP
paidAt	timestamp(6)	YES		\N
cancelledAt	timestamp(6)	YES		\N
emailSentAt	timestamp(6)	YES		\N
testMode	int(11)	NO		0

If I view the CREATE code for this table ...

CREATE TABLE `orders` (
    `orderID` INT(11) NOT NULL AUTO_INCREMENT,
    `thirdPartyID` VARCHAR(128) NULL DEFAULT NULL,
    `companyID` INT(11) NOT NULL,
    `userID` INT(11) NULL DEFAULT NULL,
    `deviceID` INT(11) NULL DEFAULT NULL,
    `sellersUserID` INT(11) NULL DEFAULT NULL,
    `usergroupID` INT(11) NULL DEFAULT NULL,
    `affiliateID` INT(8) NULL DEFAULT NULL,
    `bookingRef` VARCHAR(14) NOT NULL DEFAULT '',
    `originalRef` VARCHAR(14) NOT NULL DEFAULT '',
    `total` DECIMAL(10,2) NOT NULL,
    `dateOnly` DATE NOT NULL,
    `date` DATETIME NOT NULL,
    `customerContactID` INT(11) NULL DEFAULT NULL,
    `billingAddressID` INT(11) NULL DEFAULT NULL,
    `customerAccountID` INT(11) NULL DEFAULT NULL,
    `paid` TINYINT(1) NOT NULL DEFAULT '0',
    `distributionChannelID` INT(11) NULL DEFAULT NULL,
    `paymentChannelID` INT(11) NULL DEFAULT NULL,
    `redeemed` TINYINT(1) NOT NULL DEFAULT '0',
    `redemptionDate` DATETIME NULL DEFAULT NULL,
    `giftaid` TINYINT(1) NOT NULL DEFAULT '0',
    `notes` MEDIUMTEXT NULL,
    `createdAt` TIMESTAMP(6) NOT NULL DEFAULT '',
    `updatedAt` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    `paidAt` TIMESTAMP(6) NULL DEFAULT NULL,
    `cancelledAt` TIMESTAMP(6) NULL DEFAULT NULL,
    `emailSentAt` TIMESTAMP(6) NULL DEFAULT NULL,
    `testMode` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`orderID`)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3486463
;

So, whether it is CURRENT_TIMESTAMP() or CURRENT_TIMESTAMP(6), Heidi isn't displaying anything.

Running 9.4.0.5151 (32 bit) on OSX using Wine. (which works REALLY nicely by the way!!!)

I see this one in your code:

`updatedAt` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

So CURRENT_TIMESTAMP is working, only CURRENT_TIMESTAMP(x) is not, or?

I'm experiencing the same issue where default CURRENT_TIMESTAMP(x) does not display in the CREATE code tab on a table.

Windows 10 64 Bit - HeidiSQL version 9.4.0.5168 64 Bit

Here's some various screenshots to demonstrate where CURRENT_TIMESTAMP(x) is not being support and where it's being shown accurately via "DESCRIBE".

When inserting a row it does function correctly it's only the editor UI that does not display right.

Here's some various screenshots to demonstrate where CURRENT_TIMESTAMP(x) is not being support and where it's being shown accurately via "DESCRIBE".

When inserting a row it does function correctly it's only the editor UI that does not display right.

Forgot to attach...

r5169 at least detects such columns with a CURRENT_TIMESTAMP default value.

Only the length value is neither displayed, nor can it be changed in the "default" popup editor. I think I have to extend that panel with a text box for showing and changing the length.

Good timing, thank you sir!