diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/alter_table.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/innodb_mysql_sync.result | 181 | ||||
| -rw-r--r-- | mysql-test/r/mysqlcheck.result | 3 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/perfschema/r/innodb_table_io.result | 3 | ||||
| -rw-r--r-- | mysql-test/t/alter_table.test | 22 | ||||
| -rw-r--r-- | mysql-test/t/innodb_mysql_sync.test | 199 | ||||
| -rw-r--r-- | mysql-test/t/mysqlcheck.test | 3 |
8 files changed, 383 insertions, 52 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 207f6166fe0..622023d0e7d 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -1461,24 +1461,6 @@ t2 CREATE TABLE `t2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t2; DROP TABLE t1; -# -# Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1(a INT) engine=innodb; -INSERT INTO t1 VALUES (1), (2); -# This should not do anything -ALTER TABLE t1; -affected rows: 0 -# Check that we rebuild the table -ALTER TABLE t1 engine=innodb; -affected rows: 2 -info: Records: 2 Duplicates: 0 Warnings: 0 -# This should also rebuild the table -ALTER TABLE t1 FORCE; -affected rows: 2 -info: Records: 2 Duplicates: 0 Warnings: 0 -DROP TABLE t1; # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't # identify correct column name. # @@ -1888,8 +1870,8 @@ ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 ALTER TABLE ti1 FORCE; -affected rows: 2 -info: Records: 2 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tm1 FORCE; affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 0 diff --git a/mysql-test/r/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result index 0fecadf6de5..130b2273120 100644 --- a/mysql-test/r/innodb_mysql_sync.result +++ b/mysql-test/r/innodb_mysql_sync.result @@ -359,3 +359,184 @@ Note 1831 Duplicate index 'i4' defined on the table 'test.t1'. This is deprecate SET DEBUG_SYNC= 'RESET'; DROP TABLE t1; SET DEBUG_SYNC= 'RESET'; +# +#BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES +# +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +#Setting up INNODB table. +CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB; +INSERT INTO t1 VALUES (155, 45, 55); +#Concurrent INSERT, UPDATE, SELECT and DELETE is supported +#during OPTIMIZE TABLE operation for INNODB tables. +connection default; +#OPTIMIZE TABLE operation. +OPTIMIZE TABLE t1; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +# With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (10, 11, 12); +UPDATE t1 SET fld1= 20 WHERE fld1= 155; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +fld1 fld2 fld3 +10 11 12 +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; +#Concurrent INSERT, UPDATE, SELECT and DELETE is supported +#during OPTIMIZE TABLE operation for Partitioned table. +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +#Setup PARTITIONED table. +CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; +INSERT INTO t1 VALUES(10); +#OPTIMIZE TABLE operation. +OPTIMIZE TABLE t1; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +# With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +fld1 +30 +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; +#ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild +#of the table. +CREATE TABLE t1(fld1 INT, fld2 INT); +INSERT INTO t1 VALUES(10, 20); +ALTER TABLE t1 FORCE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ENGINE=INNODB; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +#ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses +#table copy when the old_alter_table enabled. +SET SESSION old_alter_table= TRUE; +affected rows: 0 +ALTER TABLE t1 FORCE; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ENGINE= INNODB; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; +affected rows: 0 +#OPTIMIZE TABLE operation using table copy. +OPTIMIZE TABLE t1; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +affected rows: 0 +INSERT INTO t1 VALUES(10, 20); +affected rows: 1 +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +affected rows: 2 +SET DEBUG_SYNC= 'RESET'; +affected rows: 0 +SET SESSION old_alter_table= FALSE; +affected rows: 0 +#ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy +#when ALGORITHM COPY is used. +ALTER TABLE t1 FORCE, ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +#OPTIMIZE TABLE on a table with FULLTEXT index uses +#ALTER TABLE FORCE using COPY algorithm here. This +#test case ensures the COPY table debug sync point is hit. +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; +#Setup a table with FULLTEXT index. +connection default; +CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB; +INSERT INTO t1 VALUES("String1"); +#OPTIMIZE TABLE operation. +OPTIMIZE TABLE t1; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +INSERT INTO t1 VALUES("String2"); +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize, doing recreate + analyze instead +test.t1 optimize status OK +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; +#Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION +#takes OPTIMIZE TABLE code path, hence does an online rebuild +#of the table with the patch. +connection default; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +#Setup PARTITIONED table. +CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; +INSERT INTO t1 VALUES(10); +#OPTIMIZE ALL PARTITIONS operation. +ALTER TABLE t1 OPTIMIZE PARTITION ALL; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +# With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +fld1 +30 +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. +test.t1 optimize status OK +SET DEBUG_SYNC= 'RESET'; +#OPTIMIZE PER PARTITION operation. +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +ALTER TABLE t1 OPTIMIZE PARTITION p0; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +# With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +fld1 +30 +30 +SET DEBUG_SYNC= 'now SIGNAL continue'; +connection default; +Table Op Msg_type Msg_text +test.t1 optimize note Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. +test.t1 optimize status OK +SET DEBUG_SYNC= 'RESET'; +# Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED). +# This should not do anything +ALTER TABLE t1; +affected rows: 0 +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; +# Check that we rebuild the table +ALTER TABLE t1 engine=innodb; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; +connection default; +SET DEBUG_SYNC= 'RESET'; +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; +# Check that we rebuild the table +ALTER TABLE t1 FORCE; +connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; +connection default; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 5abce8af9c9..ba9ab47ea95 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -297,10 +297,7 @@ CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text test.bug47205 check error Table rebuild required. Please do "ALTER TABLE `bug47205` FORCE" or dump/reload to fix it! # ALTER TABLE ... FORCE should rebuild the table -# and therefore output "affected rows: 1" ALTER TABLE bug47205 FORCE; -affected rows: 1 -info: Records: 1 Duplicates: 0 Warnings: 0 # Table should now be ok CHECK TABLE bug47205 FOR UPGRADE; Table Op Msg_type Msg_text diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index bc961ba1e46..f8020e080e6 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1691,7 +1691,7 @@ variable_value - @innodb_rows_deleted_orig 71 SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; variable_value - @innodb_rows_inserted_orig -1007 +964 SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; variable_value - @innodb_rows_updated_orig 866 diff --git a/mysql-test/suite/perfschema/r/innodb_table_io.result b/mysql-test/suite/perfschema/r/innodb_table_io.result index 9b5bd783f88..460518aeb65 100644 --- a/mysql-test/suite/perfschema/r/innodb_table_io.result +++ b/mysql-test/suite/perfschema/r/innodb_table_io.result @@ -106,9 +106,6 @@ wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL wait/io/table/sql/handler handler.cc: TABLE test marker insert NULL -wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL -wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL -wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL wait/io/table/sql/handler handler.cc: TABLE test marker insert NULL wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL wait/io/table/sql/handler handler.cc: TABLE test no_index_tab fetch NULL diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 3050fc0378d..4aeabf5f349 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1316,28 +1316,6 @@ SHOW CREATE TABLE t2; DROP TABLE t2; DROP TABLE t1; ---echo # ---echo # Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - -CREATE TABLE t1(a INT) engine=innodb; -INSERT INTO t1 VALUES (1), (2); - ---enable_info ---echo # This should not do anything -ALTER TABLE t1; ---echo # Check that we rebuild the table -ALTER TABLE t1 engine=innodb; ---echo # This should also rebuild the table -ALTER TABLE t1 FORCE; ---disable_info - -DROP TABLE t1; - --echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't --echo # identify correct column name. --echo # diff --git a/mysql-test/t/innodb_mysql_sync.test b/mysql-test/t/innodb_mysql_sync.test index b1e21837404..6b8aa6d0b89 100644 --- a/mysql-test/t/innodb_mysql_sync.test +++ b/mysql-test/t/innodb_mysql_sync.test @@ -592,6 +592,205 @@ DROP TABLE t1; SET DEBUG_SYNC= 'RESET'; +--echo # +--echo #BUG#13975225:ONLINE OPTIMIZE TABLE FOR INNODB TABLES +--echo # + +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +connect(con1,localhost,root,,); + +--echo #Setting up INNODB table. +CREATE TABLE t1(fld1 INT, fld2 INT, fld3 INT) ENGINE= INNODB; +INSERT INTO t1 VALUES (155, 45, 55); + +--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported +--echo #during OPTIMIZE TABLE operation for INNODB tables. +--enable_connect_log +--connection default +--echo #OPTIMIZE TABLE operation. +--send OPTIMIZE TABLE t1 + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +--echo # With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (10, 11, 12); +UPDATE t1 SET fld1= 20 WHERE fld1= 155; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--connection default +--reap +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; + +--echo #Concurrent INSERT, UPDATE, SELECT and DELETE is supported +--echo #during OPTIMIZE TABLE operation for Partitioned table. + +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +--echo #Setup PARTITIONED table. +CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; +INSERT INTO t1 VALUES(10); + +--echo #OPTIMIZE TABLE operation. +--send OPTIMIZE TABLE t1 + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +--echo # With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--connection default +--reap +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; + +--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses online rebuild +--echo #of the table. + +CREATE TABLE t1(fld1 INT, fld2 INT); +INSERT INTO t1 VALUES(10, 20); + +--enable_info +ALTER TABLE t1 FORCE; +ALTER TABLE t1 ENGINE=INNODB; + +--echo #ALTER TABLE FORCE, ALTER TABLE ENGINE and OPTIMIZE TABLE uses +--echo #table copy when the old_alter_table enabled. +SET SESSION old_alter_table= TRUE; +ALTER TABLE t1 FORCE; +ALTER TABLE t1 ENGINE= INNODB; + +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; +--echo #OPTIMIZE TABLE operation using table copy. +--send OPTIMIZE TABLE t1 + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +INSERT INTO t1 VALUES(10, 20); + +--connection default +--reap +SET DEBUG_SYNC= 'RESET'; +SET SESSION old_alter_table= FALSE; + +--echo #ALTER TABLE FORCE and ALTER TABLE ENGINE uses table copy +--echo #when ALGORITHM COPY is used. +ALTER TABLE t1 FORCE, ALGORITHM= COPY; +ALTER TABLE t1 ENGINE= INNODB, ALGORITHM= COPY; +--disable_info + +#cleanup +DROP TABLE t1; + +--echo #OPTIMIZE TABLE on a table with FULLTEXT index uses +--echo #ALTER TABLE FORCE using COPY algorithm here. This +--echo #test case ensures the COPY table debug sync point is hit. + +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded'; + +--echo #Setup a table with FULLTEXT index. +--connection default +CREATE TABLE t1(fld1 CHAR(10), FULLTEXT(fld1)) ENGINE= INNODB; +INSERT INTO t1 VALUES("String1"); + +--echo #OPTIMIZE TABLE operation. +--send OPTIMIZE TABLE t1 + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +INSERT INTO t1 VALUES("String2"); + +--connection default +--reap +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; + +--echo #Test which demonstrates that ALTER TABLE, OPTIMIZE PARTITION +--echo #takes OPTIMIZE TABLE code path, hence does an online rebuild +--echo #of the table with the patch. + +--connection default +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +--echo #Setup PARTITIONED table. +CREATE TABLE t1(fld1 INT) ENGINE= INNODB PARTITION BY HASH(fld1) PARTITIONS 4; +INSERT INTO t1 VALUES(10); + +--echo #OPTIMIZE ALL PARTITIONS operation. +--send ALTER TABLE t1 OPTIMIZE PARTITION ALL + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +--echo # With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--connection default +--reap +SET DEBUG_SYNC= 'RESET'; + +--echo #OPTIMIZE PER PARTITION operation. +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_downgrade SIGNAL downgraded WAIT_FOR continue'; +--send ALTER TABLE t1 OPTIMIZE PARTITION p0 + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR downgraded'; +--echo # With the patch, concurrent DML operation succeeds. +INSERT INTO t1 VALUES (30); +UPDATE t1 SET fld1= 20 WHERE fld1= 10; +DELETE FROM t1 WHERE fld1= 20; +SELECT * from t1; +SET DEBUG_SYNC= 'now SIGNAL continue'; + +--connection default +--reap +SET DEBUG_SYNC= 'RESET'; + +--echo # Test case for Bug#11938817 (ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED). +--enable_info +--echo # This should not do anything +ALTER TABLE t1; +--disable_info + +#Note that sync point is activated in the online rebuild code path. +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; + +--echo # Check that we rebuild the table +--send ALTER TABLE t1 engine=innodb + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; + +--connection default +--reap + +SET DEBUG_SYNC= 'RESET'; + +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuild'; + +--echo # Check that we rebuild the table +--send ALTER TABLE t1 FORCE + +--connection con1 +SET DEBUG_SYNC= 'now WAIT_FOR rebuild'; + +--connection default +--reap + +--disable_connect_log +--disconnect con1 + +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; + + # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index d7dab675dc6..d986b42fc91 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -263,10 +263,7 @@ let $MYSQLD_DATADIR= `select @@datadir`; CHECK TABLE bug47205 FOR UPGRADE; --echo # ALTER TABLE ... FORCE should rebuild the table ---echo # and therefore output "affected rows: 1" ---enable_info ALTER TABLE bug47205 FORCE; ---disable_info --echo # Table should now be ok CHECK TABLE bug47205 FOR UPGRADE; |