--source include/have_debug.inc --source include/have_debug_sync.inc --source include/have_lowercase0.inc --source include/have_innodb_16k.inc --echo # Additional test cases to verify that ALTER ADD PRIMARY KEY works fine --echo # --echo # What this test is doing: --echo # --echo # Upgrade server to latest --echo # Keep a zipped copy of upgraded datadir --echo # --echo # For all possible primary key columns --echo # --let $pk_col = [c3, c7, c11, c5, c9, c13] { --echo # --echo # Shutdown server, unzip upgraded datadir --echo # Now we can process $pk_col --echo # --echo # For all row formats --echo # --let $row_format = [redundant, compact, dynamic] { --echo # --source suite/innodb/include/bug33788578_ddl_prepare.inc --echo # --echo # For all tables --echo # --let $table_name = [ta1b1, ta1b2, ta1b4, ta2b1, ta2b2, ta2b4, ta4b1 ta4b2, ta4b4] _$row_format { --echo # Add the primary key --echo # --source suite/innodb/include/bug33788578_ddl.inc --echo # } --echo # } --echo # --echo # } --echo # Check comments near end of file to see what a1b1, a2b4, etc., means --echo # This column will be made as the primary key. TODO: include cases for each possible pk_col - test may take too long --let $pk_col = c3 --echo # Upgrade previous tables to current --source include/shutdown_mysqld.inc --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/bug33788578_ddl.zip $MYSQL_TMP_DIR/bug33788578_ddl.zip --file_exists $MYSQL_TMP_DIR/bug33788578_ddl.zip --exec unzip -qo $MYSQL_TMP_DIR/bug33788578_ddl.zip -d $MYSQL_TMP_DIR let $DATADIR = $MYSQL_TMP_DIR/data; --echo # Restart the server --replace_result $DATADIR DATADIR --let $restart_parameters = restart: --datadir=$DATADIR --let $wait_counter=3000 --source include/start_mysqld.inc SHOW TABLES; --echo # For all REDUNDANT tables: --let $row_format = redundant --echo # Prepare the table --source suite/innodb/include/bug33788578_ddl_prepare.inc --echo # For each table, run the test --echo # Run for ta1b1 --let $table_name = ta1b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b2 --let $table_name = ta1b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b4 --let $table_name = ta1b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b1 --let $table_name = ta2b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b2 --let $table_name = ta2b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b4 --let $table_name = ta2b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b1 --let $table_name = ta4b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b2 --let $table_name = ta4b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b4 --let $table_name = ta4b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # For all COMPACT tables: --let $row_format = compact --echo # Prepare the table --source suite/innodb/include/bug33788578_ddl_prepare.inc --echo # For each table, run the test --echo # Run for ta1b1 --let $table_name = ta1b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b2 --let $table_name = ta1b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b4 --let $table_name = ta1b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b1 --let $table_name = ta2b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b2 --let $table_name = ta2b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b4 --let $table_name = ta2b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b1 --let $table_name = ta4b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b2 --let $table_name = ta4b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b4 --let $table_name = ta4b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # For all DYNAMIC tables: --let $row_format = dynamic --echo # Prepare the table --source suite/innodb/include/bug33788578_ddl_prepare.inc --echo # For each table, run the test --echo # Run for ta1b1 --let $table_name = ta1b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b2 --let $table_name = ta1b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta1b4 --let $table_name = ta1b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b1 --let $table_name = ta2b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b2 --let $table_name = ta2b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta2b4 --let $table_name = ta2b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b1 --let $table_name = ta4b1_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b2 --let $table_name = ta4b2_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Run for ta4b4 --let $table_name = ta4b4_$row_format --source suite/innodb/include/bug33788578_ddl.inc --echo # Cleanup --echo # Shutdown server --source include/shutdown_mysqld.inc --echo # Remove temp files used --file_exists $MYSQL_TMP_DIR/bug33788578_ddl.zip --force-rmdir $MYSQL_TMP_DIR/data --remove_file $MYSQL_TMP_DIR/bug33788578_ddl.zip --echo # Restart server to restore server state let $restart_parameters =; --source include/start_mysqld.inc # Tables created in 8.0.28: # ====== # 8.0.28 # ====== # # DROP DATABASE IF EXISTS test; CREATE DATABASE test; # USE test; # # -- ta1b1 # CREATE TABLE ta1b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta1b1 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta1b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- no alter in 8.0.28 -- # -- # # -- ta1b2 # CREATE TABLE ta1b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta1b2 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta1b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- no alter in 8.0.28 -- # -- # # -- ta1b4 # CREATE TABLE ta1b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta1b4 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta1b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- no alter in 8.0.28 -- # -- # # -- ta2b1 # CREATE TABLE ta2b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta2b1 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta2b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta2b1 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta2b1 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # -- # # -- ta2b2 # CREATE TABLE ta2b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta2b2 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta2b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta2b2 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta2b2 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # -- # # -- ta2b4 # CREATE TABLE ta2b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta2b4 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta2b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta2b4 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta2b4 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # -- # # -- ta4b1 # CREATE TABLE ta4b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta4b1 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta4b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta4b1 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta4b1 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # INSERT INTO ta4b1 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999); # INSERT INTO ta4b1 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999); # # -- # # -- ta4b2 # CREATE TABLE ta4b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta4b2 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta4b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta4b2 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta4b2 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # INSERT INTO ta4b2 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999); # INSERT INTO ta4b2 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999); # # -- # # -- ta4b4 # CREATE TABLE ta4b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB; # # INSERT INTO ta4b4 VALUES (1, "row1", 2, 3, 4, 5); # INSERT INTO ta4b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55); # # -- alter add in 8.0.28 -- # ALTER TABLE ta4b4 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT; # ALTER TABLE ta4b4 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT; # # INSERT INTO ta4b4 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999); # INSERT INTO ta4b4 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999); # # -- # # What does an, bn mean: # # [mysql-8.0.28-release] Table definition # a1. Table: instant rows: n & instant cols: n # a2. Table: instant rows: n & instant cols: y # a3. Table: instant rows: y & instant cols: n------------<<< not possible # a4. Table: instant rows: y & instant cols: y # # [mysql-trunk] Table definition # b1. Table: version rows: n & version cols: n # b2. Table: version rows: n & version cols: y # b3. Table: version rows: y & version cols: n------------<< not possible # b4. Table: version rows: y & version cols: y # # [Combinations of tables] Table after upgrade: # a1 [a1 - b1] Table: instant rows: n & instant cols: n && version rows: n & version cols: n # [a1 - b2] Table: instant rows: n & instant cols: n && version rows: n & version cols: y # [a1 - b3] Table: instant rows: n & instant cols: n && version rows: y & version cols: n------------<< not possible # [a1 - b4] Table: instant rows: n & instant cols: n && version rows: y & version cols: y # a2 [a2 - b1] Table: instant rows: n & instant cols: y && version rows: n & version cols: n # [a2 - b2] Table: instant rows: n & instant cols: y && version rows: n & version cols: y # [a2 - b3] Table: instant rows: n & instant cols: y && version rows: y & version cols: n------------<< not possible # [a2 - b4] Table: instant rows: n & instant cols: y && version rows: y & version cols: y # a3 [a3 - b1] Table: instant rows: y & instant cols: n && version rows: n & version cols: n------------<< not possible # [a3 - b2] Table: instant rows: y & instant cols: n && version rows: n & version cols: y------------<< not possible # [a3 - b3] Table: instant rows: y & instant cols: n && version rows: y & version cols: n------------<< not possible # [a3 - b4] Table: instant rows: y & instant cols: n && version rows: y & version cols: y------------<< not possible # a4 [a4 - b1] Table: instant rows: y & instant cols: y && version rows: n & version cols: n # [a4 - b2] Table: instant rows: y & instant cols: y && version rows: n & version cols: y # [a4 - b3] Table: instant rows: y & instant cols: y && version rows: y & version cols: n------------<< not possible # [a4 - b4] Table: instant rows: y & instant cols: y && version rows: y & version cols: y # # Final tables: [a1b1, a1b2, a1b4, a2b1, a2b2, a2b4, a4b1, a4b2, a4b4] # # End