3906 Marko Mäkelä 2012-05-30
WL#6255 online table rebuild.
Enable the functionality, add and adjust tests.
TODO: Correct the online logging for ROW_FORMAT=REDUNDANT records.
TODO: Properly report duplicate key values when applying the online log.
ha_innobase::check_if_supported_inplace(): Remove the return value
HA_ALTER_INPLACE_SHARED_LOCK.
added:
mysql-test/suite/innodb/r/innodb-table-online.result
mysql-test/suite/innodb/t/innodb-table-online-master.opt
mysql-test/suite/innodb/t/innodb-table-online.test
modified:
mysql-test/suite/innodb/r/innodb-alter.result
mysql-test/suite/innodb/t/innodb-alter.test
storage/innobase/handler/ha_innodb.h
storage/innobase/handler/handler0alter.cc
3905 Marko Mäkelä 2012-05-30
WL#6255: Silence a Valgrind warning.
row_log_allocate(): Initialize log->trx_rb.
modified:
storage/innobase/row/row0log.cc
=== modified file 'mysql-test/suite/innodb/r/innodb-alter.result'
--- a/mysql-test/suite/innodb/r/innodb-alter.result revid:marko.makela@stripped530101337-cw4q5el7q1dw671r
+++ b/mysql-test/suite/innodb/r/innodb-alter.result revid:marko.makela@stripped530103217-tqj7to989nkmhg9g
@@ -415,12 +415,6 @@ CREATE TABLE t1o LIKE t1;
ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
ALGORITHM=INPLACE, LOCK=NONE;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
-CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
-ALGORITHM=INPLACE, LOCK=NONE'
-ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
-CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
-ALGORITHM=INPLACE, LOCK=SHARED;
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN
=== added file 'mysql-test/suite/innodb/r/innodb-table-online.result'
--- a/mysql-test/suite/innodb/r/innodb-table-online.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/r/innodb-table-online.result revid:marko.makela@strippedg9g
@@ -0,0 +1,313 @@
+call mtr.add_suppression("InnoDB: Warning: Small buffer pool size");
+call mtr.add_suppression("InnoDB: Error: table 'test/t1'");
+call mtr.add_suppression("MySQL is trying to open a table handle but the .ibd file for");
+SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table;
+SET GLOBAL innodb_file_per_table = on;
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 TEXT)
+ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
+SET GLOBAL innodb_monitor_enable = module_ddl;
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+SET DEBUG_SYNC = 'RESET';
+SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
+INSERT INTO t1 VALUES(1,2,3);
+SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
+SET lock_wait_timeout = 1;
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET DEBUG_SYNC = 'now SIGNAL go_ahead';
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+SET SESSION DEBUG = '+d,innodb_OOM_prepare_inplace_alter_rebuild';
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
+SET SESSION DEBUG = '-d,innodb_OOM_prepare_inplace_alter_rebuild';
+SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter';
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+ERROR HY000: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
+SET SESSION DEBUG = '-d,innodb_OOM_inplace_alter';
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` text,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
+BEGIN;
+INSERT INTO t1 VALUES(7,4,2);
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
+SET DEBUG_SYNC = 'row_log_table_apply2_before WAIT_FOR rollback_done';
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+COMMIT;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
+ERROR 23000: Duplicate entry '4' for key 'c2'
+DELETE FROM t1 WHERE c1 = 7;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ROW_FORMAT=COMPACT,
+LOCK = SHARED, ALGORITHM = INPLACE;
+ALTER TABLE t1 ADD UNIQUE INDEX(c2),
+LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` text,
+ UNIQUE KEY `c2` (`c2`),
+ UNIQUE KEY `c2_2` (`c2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
+ALTER TABLE t1 DROP INDEX c2, ALGORITHM = INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t1 DROP INDEX c2, ALGORITHM = INPLACE'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` text,
+ UNIQUE KEY `c2` (`c2`),
+ UNIQUE KEY `c2_2` (`c2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
+ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1);
+SET DEBUG_SYNC = 'now WAIT_FOR scanned';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+BEGIN;
+INSERT INTO t1 VALUES(4,7,2);
+SET DEBUG_SYNC = 'now SIGNAL insert_done';
+ROLLBACK;
+SET DEBUG_SYNC = 'now SIGNAL rollback_done';
+ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` text,
+ UNIQUE KEY `c2` (`c2`),
+ UNIQUE KEY `c2_2` (`c2`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ALGORITHM = INPLACE;
+ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
+ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1), ALGORITHM = INPLACE;
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+INSERT INTO t1 VALUES(6,3,1);
+ERROR 23000: Duplicate entry '3' for key 'c2_2'
+INSERT INTO t1 VALUES(7,4,2);
+ERROR 23000: Duplicate entry '4' for key 'c2_2'
+DROP INDEX c2_2 ON t1;
+BEGIN;
+INSERT INTO t1 VALUES(7,4,2);
+ROLLBACK;
+KILL QUERY @id;
+ERROR 70100: Query execution was interrupted
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR kill_done';
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+KILL QUERY @id;
+SET DEBUG_SYNC = 'now SIGNAL kill_done';
+ERROR 70100: Query execution was interrupted
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 80 Using where
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` text,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt2 WAIT_FOR dml2_done';
+SET lock_wait_timeout = 10;
+ALTER TABLE t1 ROW_FORMAT=COMPACT, ALGORITHM = INPLACE;
+INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
+UPDATE t1 SET c2 = c2 + 1;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt2';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+BEGIN;
+DELETE FROM t1;
+ROLLBACK;
+UPDATE t1 SET c2 = c2 + 1;
+BEGIN;
+UPDATE t1 SET c2 = c2 + 1;
+DELETE FROM t1;
+ROLLBACK;
+BEGIN;
+DELETE FROM t1;
+ROLLBACK;
+UPDATE t1 SET c2 = c2 + 1;
+BEGIN;
+UPDATE t1 SET c2 = c2 + 1;
+DELETE FROM t1;
+ROLLBACK;
+BEGIN;
+DELETE FROM t1;
+ROLLBACK;
+UPDATE t1 SET c2 = c2 + 1;
+BEGIN;
+UPDATE t1 SET c2 = c2 + 1;
+DELETE FROM t1;
+ROLLBACK;
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+SET DEBUG_SYNC = 'now SIGNAL dml2_done';
+ERROR HY000: Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
+ALTER TABLE t1 ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
+ERROR 42000: Multiple primary key defined
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
+ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c22f), CHANGE c2 c22f INT;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+BEGIN;
+INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
+DELETE FROM t1 WHERE c1 > 320;
+ROLLBACK;
+BEGIN;
+UPDATE t1 SET c2 = c2 + 1;
+DELETE FROM t1;
+ROLLBACK;
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 1
+ddl_pending_alter_table 1
+SET DEBUG_SYNC = 'now SIGNAL dml3_done';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+SELECT COUNT(c22f) FROM t1;
+COUNT(c22f)
+320
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5));
+ERROR 23000: Duplicate entry '' for key 'PRIMARY'
+UPDATE t1 SET c3 = NULL WHERE c3 = '';
+SET lock_wait_timeout = 1;
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done';
+ALTER TABLE t1 ADD PRIMARY KEY c3p5(c3(5));
+ERROR 42000: Multiple primary key defined
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
+ALGORITHM = INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
+ALGORITHM = INPLACE'
+UPDATE t1 SET c3=CONCAT(c1,REPEAT('foo',c1)) WHERE c3 IS NULL;
+ALTER TABLE t1 MODIFY c3 TEXT NOT NULL, ALGORITHM = INPLACE;
+ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE t1 MODIFY c3 TEXT NOT NULL, ALGORITHM = INPLACE'
+ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
+ALGORITHM = INPLACE;
+SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
+SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
+INSERT INTO t1 VALUES(347,33101,NULL);
+ERROR 23000: Column 'c3' cannot be null
+INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti');
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+name count
+ddl_background_drop_indexes 0
+ddl_background_drop_tables 0
+ddl_online_create_index 0
+ddl_pending_alter_table 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+321
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt4 WAIT_FOR dml4_done';
+SET lock_wait_timeout = 1;
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt4';
+SET lock_wait_timeout = 10;
+ALTER TABLE t1 DISCARD TABLESPACE;
+SET DEBUG_SYNC = 'now SIGNAL dml4_done';
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c22f` int(11) NOT NULL DEFAULT '0',
+ `c3` text NOT NULL,
+ PRIMARY KEY (`c1`,`c22f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM = INPLACE;
+SELECT * FROM t1;
+c1 c22f c3
+SET DEBUG_SYNC = 'RESET';
+SET DEBUG = '';
+SET GLOBAL innodb_monitor_disable = module_ddl;
+DROP TABLE t1;
+SET GLOBAL DEBUG = '';
+SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
+SET GLOBAL innodb_monitor_enable = default;
+SET GLOBAL innodb_monitor_disable = default;
=== modified file 'mysql-test/suite/innodb/t/innodb-alter.test'
--- a/mysql-test/suite/innodb/t/innodb-alter.test revid:marko.makela@stripped0101337-cw4q5el7q1dw671r
+++ b/mysql-test/suite/innodb/t/innodb-alter.test revid:marko.makela@stripped03217-tqj7to989nkmhg9g
@@ -179,18 +179,11 @@ ALTER TABLE t1 DROP INDEX c2, CHANGE c3
CREATE TABLE t1o LIKE t1;
-# This will implicitly add a DOC_ID column, copying the table while
-# holding a lock. The LOCK=NONE should thus fail.
---error ER_NOT_SUPPORTED_YET
+# This will implicitly add a FTS_DOC_ID column.
ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
ALGORITHM=INPLACE, LOCK=NONE;
-# Retry with LOCK=SHARED.
-ALTER TABLE t1 ADD FULLTEXT INDEX (ct),
-CHANGE c1 pk INT, ALTER c2 SET DEFAULT 42, RENAME TO tt,
-ALGORITHM=INPLACE, LOCK=SHARED;
-
# The output should be empty, because index->id was reassigned.
-- source suite/innodb/include/innodb_dict.inc
=== added file 'mysql-test/suite/innodb/t/innodb-table-online-master.opt'
--- a/mysql-test/suite/innodb/t/innodb-table-online-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-table-online-master.opt revid:marko.makela@oracle.com-20120530103217-tqj7to989nkmhg9g
@@ -0,0 +1 @@
+--innodb-sort-buffer-size=64k --innodb-online-alter-log-max-size=64k --innodb-buffer-pool-size=5M --innodb-log-buffer-size=256k
=== added file 'mysql-test/suite/innodb/t/innodb-table-online.test'
--- a/mysql-test/suite/innodb/t/innodb-table-online.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb/t/innodb-table-online.test revid:marko.makela@stripped
@@ -0,0 +1,348 @@
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+
+let $innodb_metrics_select=
+SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
+
+call mtr.add_suppression("InnoDB: Warning: Small buffer pool size");
+# these will be triggered by DISCARD TABLESPACE
+call mtr.add_suppression("InnoDB: Error: table 'test/t1'");
+call mtr.add_suppression("MySQL is trying to open a table handle but the .ibd file for");
+
+# DISCARD TABLESPACE needs file-per-table
+SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table;
+SET GLOBAL innodb_file_per_table = on;
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 TEXT)
+ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
+
+SET GLOBAL innodb_monitor_enable = module_ddl;
+eval $innodb_metrics_select;
+
+SET DEBUG_SYNC = 'RESET';
+SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
+--send
+INSERT INTO t1 VALUES(1,2,3);
+
+connect (con1,localhost,root,,);
+connection con1;
+
+# This should block at the end because of the INSERT in connection default
+# is holding a metadata lock.
+SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
+SET lock_wait_timeout = 1;
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+SET DEBUG_SYNC = 'now SIGNAL go_ahead';
+
+connection default;
+--error ER_DUP_ENTRY
+reap;
+eval $innodb_metrics_select;
+
+connection con1;
+SET SESSION DEBUG = '+d,innodb_OOM_prepare_inplace_alter_rebuild';
+--error ER_OUT_OF_RESOURCES
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+SET SESSION DEBUG = '-d,innodb_OOM_prepare_inplace_alter_rebuild';
+SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter';
+--error ER_OUT_OF_RESOURCES
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+SET SESSION DEBUG = '-d,innodb_OOM_inplace_alter';
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
+
+connection default;
+SHOW CREATE TABLE t1;
+# Insert a duplicate entry (4) for the upcoming UNIQUE INDEX(c2).
+BEGIN;
+INSERT INTO t1 VALUES(7,4,2);
+
+connection con1;
+# This DEBUG_SYNC should not kick in yet, because the duplicate key will be
+# detected before we get a chance to apply the online log.
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
+SET DEBUG_SYNC = 'row_log_table_apply2_before WAIT_FOR rollback_done';
+# This will be a lock wait timeout on the meta-data lock,
+# because the transaction inserting (7,4,2) is still active.
+--error ER_LOCK_WAIT_TIMEOUT
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
+connection default;
+COMMIT;
+connection con1;
+--error ER_DUP_ENTRY
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
+connection default;
+DELETE FROM t1 WHERE c1 = 7;
+connection con1;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ROW_FORMAT=COMPACT,
+LOCK = SHARED, ALGORITHM = INPLACE;
+
+# The previous DEBUG_SYNC should be ignored, because an exclusive lock
+# has been requested and the online log is not being allocated.
+ALTER TABLE t1 ADD UNIQUE INDEX(c2),
+LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
+
+SHOW CREATE TABLE t1;
+# We do not support plain DROP_PK_INDEX without ADD_PK_INDEX.
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t1 DROP INDEX c2, ALGORITHM = INPLACE;
+SHOW CREATE TABLE t1;
+# Now the previous DEBUG_SYNC should kick in.
+--send
+ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1);
+connection default;
+SET DEBUG_SYNC = 'now WAIT_FOR scanned';
+eval $innodb_metrics_select;
+
+# Insert a duplicate entry (4) for the already started UNIQUE INDEX(c1).
+BEGIN;
+INSERT INTO t1 VALUES(4,7,2);
+SET DEBUG_SYNC = 'now SIGNAL insert_done';
+# Wait for the log to be applied,
+# so that a duplicate key error would be triggered.
+let $wait_condition=
+ SELECT COUNT(*) = 1 FROM information_schema.processlist
+ WHERE state = 'System lock' and
+ info = 'ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1)';
+--source include/wait_condition.inc
+ROLLBACK;
+SET DEBUG_SYNC = 'now SIGNAL rollback_done';
+
+connection con1;
+# Because the modification log will be applied in order, there will be
+# a duplicate key error on the (4,7,2) if the rollback occurred after
+# the log apply.
+--error ER_DUP_ENTRY
+reap;
+SHOW CREATE TABLE t1;
+# Now, rebuild the table without any concurrent DML, while no duplicate exists.
+--error ER_CANT_DROP_FIELD_OR_KEY
+ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ALGORITHM = INPLACE;
+ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1), ALGORITHM = INPLACE;
+eval $innodb_metrics_select;
+
+connection default;
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES(6,3,1);
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES(7,4,2);
+DROP INDEX c2_2 ON t1;
+BEGIN;
+INSERT INTO t1 VALUES(7,4,2);
+ROLLBACK;
+
+connection con1;
+let $ID= `SELECT @id := CONNECTION_ID()`;
+--error ER_QUERY_INTERRUPTED
+KILL QUERY @id;
+
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR kill_done';
+--send
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+
+connection default;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt';
+eval $innodb_metrics_select;
+let $ignore= `SELECT @id := $ID`;
+KILL QUERY @id;
+SET DEBUG_SYNC = 'now SIGNAL kill_done';
+
+connection con1;
+--error ER_QUERY_INTERRUPTED
+reap;
+eval $innodb_metrics_select;
+
+connection default;
+CHECK TABLE t1;
+INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
+# Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
+--replace_result 6 5
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
+ANALYZE TABLE t1;
+
+connection con1;
+SHOW CREATE TABLE t1;
+
+# Exceed the configured innodb_online_alter_log_max_size.
+# The actual limit is a multiple of innodb_sort_buf_size,
+# because that is the size of the in-memory log buffers.
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt2 WAIT_FOR dml2_done';
+# Ensure that the ALTER TABLE will be executed even with some concurrent DML.
+SET lock_wait_timeout = 10;
+--send
+ALTER TABLE t1 ROW_FORMAT=COMPACT, ALGORITHM = INPLACE;
+
+# Generate some log (delete-mark, delete-unmark, insert etc.)
+# while the index creation is blocked. Some of this may run
+# in parallel with the clustered index scan.
+connection default;
+INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
+INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
+UPDATE t1 SET c2 = c2 + 1;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt2';
+# At this point, the clustered index scan must have completed,
+# but the modification log keeps accumulating due to the DEBUG_SYNC.
+eval $innodb_metrics_select;
+let $c= 3;
+while ($c)
+{
+ BEGIN;
+ DELETE FROM t1;
+ ROLLBACK;
+ UPDATE t1 SET c2 = c2 + 1;
+ BEGIN;
+ UPDATE t1 SET c2 = c2 + 1;
+ DELETE FROM t1;
+ ROLLBACK;
+ dec $c;
+}
+# Temporary table should exist until the DDL thread notices the overflow.
+eval $innodb_metrics_select;
+
+# Release con1.
+SET DEBUG_SYNC = 'now SIGNAL dml2_done';
+
+connection con1;
+# If the following fails with the wrong error, it probably means that
+# you should rerun with a larger mtr --debug-sync-timeout.
+--error ER_INNODB_ONLINE_LOG_TOO_BIG
+reap;
+# The table should have been dropped from the data dictionary
+# when the above error was noticed.
+eval $innodb_metrics_select;
+
+# Accumulate and apply some modification log.
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
+--error ER_MULTIPLE_PRI_KEY
+ALTER TABLE t1 ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
+--error ER_DUP_ENTRY
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
+--send
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c22f), CHANGE c2 c22f INT;
+
+connection default;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
+# Generate some log (delete-mark, delete-unmark, insert etc.)
+eval $innodb_metrics_select;
+BEGIN;
+INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
+DELETE FROM t1 WHERE c1 > 320;
+ROLLBACK;
+BEGIN;
+UPDATE t1 SET c2 = c2 + 1;
+DELETE FROM t1;
+ROLLBACK;
+eval $innodb_metrics_select;
+# Release con1.
+SET DEBUG_SYNC = 'now SIGNAL dml3_done';
+
+connection con1;
+reap;
+eval $innodb_metrics_select;
+SELECT COUNT(c22f) FROM t1;
+CHECK TABLE t1;
+
+# Create a column prefix index.
+--error ER_DUP_ENTRY
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5));
+UPDATE t1 SET c3 = NULL WHERE c3 = '';
+SET lock_wait_timeout = 1;
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done';
+--error ER_MULTIPLE_PRI_KEY
+ALTER TABLE t1 ADD PRIMARY KEY c3p5(c3(5));
+# We do not support changing a column to NOT NULL yet.
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
+ALGORITHM = INPLACE;
+
+UPDATE t1 SET c3=CONCAT(c1,REPEAT('foo',c1)) WHERE c3 IS NULL;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t1 MODIFY c3 TEXT NOT NULL, ALGORITHM = INPLACE;
+ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
+
+--send
+ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
+ALGORITHM = INPLACE;
+
+connection default;
+SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
+
+SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
+--error ER_BAD_NULL_ERROR
+INSERT INTO t1 VALUES(347,33101,NULL);
+--send
+INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti');
+
+connection con1;
+--error ER_LOCK_WAIT_TIMEOUT
+reap;
+SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
+
+eval $innodb_metrics_select;
+
+connection default;
+reap;
+SELECT COUNT(*) FROM t1;
+
+SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt4 WAIT_FOR dml4_done';
+# The lock upgrade at the end of the ALTER will conflict with the DISCARD.
+SET lock_wait_timeout = 1;
+--send
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
+
+connection con1;
+SET DEBUG_SYNC = 'now WAIT_FOR rebuilt4';
+
+connect (con2,localhost,root,,);
+connection con2;
+
+# This will conflict with the ALTER in connection default, above.
+SET lock_wait_timeout = 10;
+--send
+ALTER TABLE t1 DISCARD TABLESPACE;
+
+connection con1;
+let $wait_condition=
+ SELECT COUNT(*) = 1 FROM information_schema.processlist
+ WHERE state = 'Waiting for table level lock' and
+ info = 'ALTER TABLE t1 DISCARD TABLESPACE';
+--source include/wait_condition.inc
+
+SET DEBUG_SYNC = 'now SIGNAL dml4_done';
+disconnect con1;
+connection con2;
+reap;
+disconnect con2;
+connection default;
+--error ER_LOCK_WAIT_TIMEOUT
+reap;
+
+SHOW CREATE TABLE t1;
+# The source tablespace was discarded during the previous online rebuild.
+# The rebuild will re-create t1 as an empty table.
+ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM = INPLACE;
+SELECT * FROM t1;
+
+SET DEBUG_SYNC = 'RESET';
+SET DEBUG = '';
+SET GLOBAL innodb_monitor_disable = module_ddl;
+
+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
+
+SET GLOBAL DEBUG = '';
+SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
+--disable_warnings
+SET GLOBAL innodb_monitor_enable = default;
+SET GLOBAL innodb_monitor_disable = default;
+--enable_warnings
=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h revid:marko.makela@stripped
+++ b/storage/innobase/handler/ha_innodb.h revid:marko.makela@oracle.com-20120530103217-tqj7to989nkmhg9g
@@ -235,7 +235,6 @@ class ha_innobase: public handler
by ALTER TABLE and holding data used during in-place alter.
@retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported
- @retval HA_ALTER_INPLACE_EXCLUSIVE_LOCK Supported, but requires X-lock
@retval HA_ALTER_INPLACE_NO_LOCK Supported
@retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE Supported, prepare phase
*/
=== modified file 'storage/innobase/handler/handler0alter.cc'
--- a/storage/innobase/handler/handler0alter.cc revid:marko.makela@stripped5el7q1dw671r
+++ b/storage/innobase/handler/handler0alter.cc revid:marko.makela@stripped9nkmhg9g
@@ -184,7 +184,6 @@ innobase_need_rebuild(
by ALTER TABLE and holding data used during in-place alter.
@retval HA_ALTER_INPLACE_NOT_SUPPORTED Not supported
-@retval HA_ALTER_INPLACE_SHARED_LOCK Supported, but requires S-lock
@retval HA_ALTER_INPLACE_NO_LOCK Supported
@retval HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE Supported, prepare phase
(any transactions that have modified the table must commit or roll back
@@ -286,35 +285,6 @@ ha_innobase::check_if_supported_inplace_
}
prebuilt->trx->will_lock++;
-
- /* Rebuilding the clustered index requires a shared lock on the
- table during the whole copying operation. */
- if (innobase_need_rebuild(ha_alter_info)) {
- DBUG_RETURN(HA_ALTER_INPLACE_SHARED_LOCK);
- }
-
- if (ha_alter_info->handler_flags
- & (Alter_inplace_info::ADD_INDEX
- | Alter_inplace_info::ADD_UNIQUE_INDEX)) {
- /* Building a full-text index requires a shared lock. */
-
- for (uint i = 0; i < ha_alter_info->index_add_count; i++) {
- const KEY* key =
- &ha_alter_info->key_info_buffer[
- ha_alter_info->index_add_buffer[i]];
- if (key->flags & HA_FULLTEXT) {
- DBUG_ASSERT(!(key->flags & HA_KEYFLAG_MASK
- & ~(HA_FULLTEXT
- | HA_PACK_KEY
- | HA_GENERATED_KEY
- | HA_BINARY_PACK_KEY)));
- DBUG_RETURN(HA_ALTER_INPLACE_SHARED_LOCK);
- }
- }
- }
-
- /* All other operations (create index, drop index, etc.) can
- be perfomed without blocking others in inplace_alter_table(). */
DBUG_RETURN(HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE);
}
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-trunk-wl6255 branch (marko.makela:3905 to 3906) WL#6255 | marko.makela | 30 May |
