0

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.

Serge Rogatch
  • 115
  • 6

1 Answers1

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.

Dave
  • 403
  • 5
  • 14