Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
-
None
Description
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.
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
MDEV-11369 Instant add column for InnoDB
-
- Closed
-
Activity
It looks mostly OK, but please test with a smaller table. You can stop purge by issuing START TRANSACTION WITH CONSISTENT SNAPSHOT from another connection before issuing the DELETE that empties the table. See git grep purge_control for examples.
Please fix also the Windows build failure.
thiru, I think that mysql_inplace_alter_table() must copy alter_ctx.error_if_not_empty to a new field of Alter_inplace_info. Then, ha_innobase::prepare_inplace_alter_table() would have to check this flag and return an error if the table is not empty.