distal-attribute
distal-attribute
distal-attribute
distal-attribute

Not respecting DEFAULT CURRENT_TIMESTAMP(6).

Richard Quadling posted 3 years ago in General
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.
ansgar posted 3 years ago
Please post the SQL query you have fired and where you got the error 1064 from.
Richard Quadling posted 3 years ago
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.



jfalch posted 3 years ago
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.
Richard Quadling posted 6 months ago

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

ansgar posted 6 months ago

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?

aoverton posted 5 months ago

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

aoverton posted 5 months ago

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.

aoverton posted 5 months ago

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

1 attachment(s):
  • heidi-timestamp-issue
ansgar posted 5 months ago

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.

aoverton posted 5 months ago

Good timing, thank you sir!

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