I created a table in HeidiSQL:
CREATE TABLE `publish_dt` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `TS` DATETIME NULL DEFAULT NULL, `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci', PRIMARY KEY (`ID`) USING BTREE ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=2 ;
Then I insert some data into the table, e.g. with
INSERT INTO publish_dt (`Text`) VALUES ('Hello')
Then I want to change
TS column to non-
NULL with a default of '1001-01-01 00:00:00'. I tried to do this in HeidiSQL, it produces the following
ALTER TABLE query:
ALTER TABLE `publish_dt` CHANGE COLUMN `TS` `TS` DATETIME NOT NULL DEFAULT '1001-01-01 00:00:00' AFTER `ID`;
However, when I try to save changes in HeidiSQL, I receive the following error from the DB:
SQL Error (1265): Data truncated for column 'TS' at row 1
Could you, please, clarify why this error happens (I couldn't find the same case with
DATETIME on the internet) and how to work it around?
Reason for asking: MariaDB documentation says
MariaDB stores values that use the DATETIME data type in a format that supports values between 1000-01-01 00:00:00.000000 and 9999-12-31 23:59:59.999999.