mirror of
https://github.com/MariaDB/server.git
synced 2025-02-12 00:15:35 +01:00
2464ee758a
Remove alter_algorithm but keep the variable as no-op (with a warning). The reasons for removing alter_algorithm are: - alter_algorithm was introduced as a replacement for the old_alter_table that was used to force the usage of the original alter table algorithm (copy) in the cases where the new alter algorithm did not work. The new option was added as a way to force the usage of a specific algorithm when it should instead have made it possible to disable algorithms that would not work for some reason. - alter_algorithm introduced some cases where ALTER TABLE would not work without specifying the ALGORITHM=XXX option together with ALTER TABLE. - Having different values of alter_algorithm on master and slave could cause slave to stop unexpectedly. - ALTER TABLE FORCE, as used by mariadb-upgrade, would not always work if alter_algorithm was set for the server. - As part of the MDEV-33449 "improving repair of tables" it become clear that alter- algorithm made it harder to provide a better and more consistent ALTER TABLE FORCE and REPAIR TABLE and it would be better to remove it.
198 lines
5.5 KiB
Text
198 lines
5.5 KiB
Text
#
|
|
# sql_mode: STRICT_TRANS_TABLES alter_algorithm: COPY
|
|
#
|
|
CREATE TABLE t1(f1 INT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL, ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL, ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 TEXT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc', ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(2, 2, NULL);
|
|
SELECT * FROM t1;
|
|
f1 f2 f3
|
|
2 2 NULL
|
|
ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'f3' at row 1
|
|
SELECT * FROM t1;
|
|
f1 f2 f3
|
|
2 2 NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(10, NULL);
|
|
SELECT * FROM t1;
|
|
f1 b
|
|
10 NULL
|
|
ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), ALGORITHM=COPY;
|
|
ERROR 01000: Data truncated for column 'b' at row 1
|
|
SELECT * FROM t1;
|
|
f1 b
|
|
10 NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=COPY;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT, v INT AS (a), d INT NOT NULL, e INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 FORCE, ALGORITHM=COPY;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1, NULL);
|
|
ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'c2' at row 1
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
1 0
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-16126 Crash or ASAN heap-buffer-overflow in
|
|
# mach_read_from_n_little_endian upon ALTER TABLE with blob
|
|
#
|
|
CREATE TABLE t1(a INT, v INT AS (a), b INT, c BLOB) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=COPY;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
#
|
|
# sql_mode: STRICT_TRANS_TABLES alter_algorithm: INPLACE
|
|
#
|
|
CREATE TABLE t1(f1 INT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 INT NOT NULL, ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 CHAR(10))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 CHAR(10) NOT NULL, ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 VARCHAR(10))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 VARCHAR(20) NOT NULL, ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 TEXT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(NULL);
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
ALTER TABLE t1 CHANGE f1 f1 TEXT NOT NULL DEFAULT 'abc', ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'f1' at row 1
|
|
SELECT * FROM t1;
|
|
f1
|
|
NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 INT)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(2, 2, NULL);
|
|
SELECT * FROM t1;
|
|
f1 f2 f3
|
|
2 2 NULL
|
|
ALTER TABLE t1 CHANGE f3 f3 INT NOT NULL DEFAULT (f1 + f2), ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'f3' at row 1
|
|
SELECT * FROM t1;
|
|
f1 f2 f3
|
|
2 2 NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(f1 INT NOT NULL DEFAULT 0, b TINYINT)ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(10, NULL);
|
|
SELECT * FROM t1;
|
|
f1 b
|
|
10 NULL
|
|
ALTER TABLE t1 CHANGE b b TINYINT NOT NULL DEFAULT if(unix_timestamp()>1,1000,0), ALGORITHM=INPLACE;
|
|
ERROR 01000: Data truncated for column 'b' at row 1
|
|
SELECT * FROM t1;
|
|
f1 b
|
|
10 NULL
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT, v INT AS (a), c INT, d INT NOT NULL, e INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 DROP COLUMN c, CHANGE COLUMN e e INT NOT NULL, ALGORITHM=INPLACE;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT, v INT AS (a), d INT NOT NULL, e INT) ENGINE=InnoDB;
|
|
ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(c1 INT NOT NULL, c2 INT, PRIMARY KEY(c1))ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1, NULL);
|
|
ALTER IGNORE TABLE t1 CHANGE c2 c2 INT NOT NULL DEFAULT 2;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 1
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'c2' at row 1
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
1 0
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-16126 Crash or ASAN heap-buffer-overflow in
|
|
# mach_read_from_n_little_endian upon ALTER TABLE with blob
|
|
#
|
|
CREATE TABLE t1(a INT, v INT AS (a), b INT, c BLOB) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
DROP TABLE t1;
|