[MDEV-19611] INPLACE ALTER does not fail on bad implicit default value - Jira

XML

Word

Printable

In all below scripts we use ALTER for a table that:

is not empty
adds a new DATE NOT NULL field with no implicit default, so '0000-00-00' is implied as a default
set sql_mode in the way to disallow zero dates

Such ALTER should fail, because new fields in all existing rows should be assigned to '0000-00-00', which is not allowed.

It fails with MyISAM:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=MyISAM;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ADD b DATE NOT NULL;

ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column `test`.`t1`.`b` at row 1

It fails with INNODB, using COPY:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=INNODB;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ALGORITHM=COPY, ADD b DATE NOT NULL;

It does not fail with INNODB, using INPLACE:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE) ENGINE=INNODB;

INSERT INTO t1 VALUES (now());

SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE';

ALTER TABLE t1 ALGORITHM=INPLACE, ADD b DATE NOT NULL;

Query OK, 0 rows affected (0.007 sec)

Records: 0  Duplicates: 0  Warnings: 0

Notice, it silently performed the ALTER without any warnings.

causes

MDEV-26458 SIGSEGV's in fil_space_t::zip_size and btr_cur_open_at_index_side on ALTER TABLE

relates to

MDEV-11369 Instant add column for InnoDB

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.