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

adyoi's profile image adyoi posted 5 years ago in General Permalink

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

adyoi's profile image adyoi posted 5 years ago Permalink

10.3.16-MariaDB

adyoi's profile image adyoi posted 5 years ago Permalink

invalid default value

DATE DATETIME TIMESTAMP

ansgar's profile image ansgar posted 5 years ago Permalink

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

adyoi's profile image adyoi posted 5 years ago Permalink

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

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

adyoi's profile image adyoi posted 5 years ago Permalink

screenshot

1 attachment(s):
  • err
ansgar's profile image ansgar posted 5 years ago Permalink

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.

adyoi's profile image adyoi posted 5 years ago Permalink

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';

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