How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

Question

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.

Answer 1

The alter is failing because the row has a value of null for the TS column. If you remove the 'NOT NULL` from your alter statement it will work correctly though you will still end up with the row you first inserted with a value of null for TS.

You will get the same error if you do the alter without trying to set a default. The alter you are trying to use will not replace the null value and, as a result, you are getting the error.

You may change the value of the row before the alter but, as you have it written, the alter will not work as you've seen.