Invalid default value for type DATE '0000-00-00'

Invalid default value for type DATE '0000-00-00'

10.3.16-MariaDB

invalid default value

DATE DATETIME TIMESTAMP

Please write in whole sentences, and describe what you were doing.

Please write in whole sentences, and describe what you were doing.

create new table and update table with default value '0000-00-00'

screenshot

Not sure why the server rejects that change. I just could successfully do the same on MariaDB 10.3.12:

ALTER TABLE `test`
    CHANGE COLUMN `d` `d` DATE NOT NULL DEFAULT '0000-00-00' AFTER `Mt`;

'0000-00-00' is not allowed in TRADITIONAL/NO_ZERO_DATE SQL mode. Perhaps you have activated that.

Not sure why the server rejects that change. I just could successfully do the same on MariaDB 10.3.12:

ALTER TABLE `test`
  CHANGE COLUMN `d` `d` DATE NOT NULL DEFAULT '0000-00-00' AFTER `Mt`;

'0000-00-00' is not allowed in TRADITIONAL/NO_ZERO_DATE SQL mode. Perhaps you have activated that.

fix with sql mode

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';