772 lines
26 KiB
Plaintext
772 lines
26 KiB
Plaintext
#
|
|
# Bug 42074 concurrent optimize table and
|
|
# alter table = Assertion failed: thd->is_error()
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
# Create InnoDB table
|
|
CREATE TABLE t1 (id INT) engine=innodb;
|
|
# Connection 1
|
|
# Start optimizing table
|
|
SET DEBUG_SYNC='ha_admin_try_alter SIGNAL optimize_started WAIT_FOR table_altered';
|
|
OPTIMIZE TABLE t1;
|
|
# Connection 2
|
|
# Change table to engine=memory
|
|
SET DEBUG_SYNC='now WAIT_FOR optimize_started';
|
|
ALTER TABLE t1 engine=memory;
|
|
SET DEBUG_SYNC='now SIGNAL table_altered';
|
|
# Connection 1
|
|
# Complete optimization
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize error Got error -1 - 'Unknown error' from storage engine
|
|
test.t1 optimize status Operation failed
|
|
Warnings:
|
|
Error 1030 Got error -1 - 'Unknown error' from storage engine
|
|
DROP TABLE t1;
|
|
SET DEBUG_SYNC='RESET';
|
|
#
|
|
# Bug#47459 Assertion in Diagnostics_area::set_eof_status on
|
|
# OPTIMIZE TABLE
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a INT) ENGINE= InnoDB;
|
|
# Connection con1
|
|
SET DEBUG_SYNC= "ha_admin_open_ltable SIGNAL opening WAIT_FOR dropped";
|
|
# Sending:
|
|
OPTIMIZE TABLE t1;
|
|
# Connection default
|
|
SET DEBUG_SYNC= "now WAIT_FOR opening";
|
|
DROP TABLE t1;
|
|
SET DEBUG_SYNC= "now SIGNAL dropped";
|
|
# Connection con1
|
|
# Reaping: OPTIMIZE TABLE t1
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize error Table 'test.t1' doesn't exist
|
|
test.t1 optimize status Operation failed
|
|
Warnings:
|
|
Error 1146 Table 'test.t1' doesn't exist
|
|
# Connection default
|
|
SET DEBUG_SYNC= "RESET";
|
|
#
|
|
# Bug#53757 assert in mysql_truncate_by_delete
|
|
#
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
CREATE TABLE t1(a INT) Engine=InnoDB;
|
|
CREATE TABLE t2(id INT);
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
INSERT INTO t2 VALUES(connection_id());
|
|
SET DEBUG_SYNC= "open_and_process_table SIGNAL opening WAIT_FOR killed";
|
|
# Sending: (not reaped since connection is killed later)
|
|
TRUNCATE t1;
|
|
SET DEBUG_SYNC= "now WAIT_FOR opening";
|
|
SELECT ((@id := id) - id) FROM t2;
|
|
((@id := id) - id)
|
|
0
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
KILL @id;
|
|
SET DEBUG_SYNC= "now SIGNAL killed";
|
|
DROP TABLE t1, t2;
|
|
SET DEBUG_SYNC= "RESET";
|
|
#
|
|
# Bug#58933 Assertion `thd- >is_error()' fails on shutdown with ongoing
|
|
# OPTIMIZE TABLE
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
# Connection con1
|
|
SET DEBUG_SYNC= 'ha_admin_open_ltable SIGNAL waiting WAIT_FOR killed';
|
|
# Sending:
|
|
OPTIMIZE TABLE t1;
|
|
# Connection default
|
|
SET DEBUG_SYNC= 'now WAIT_FOR waiting';
|
|
KILL QUERY ID;
|
|
SET DEBUG_SYNC= 'now SIGNAL killed';
|
|
# Connection con1
|
|
# Reaping: OPTIMIZE TABLE t1
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t1 optimize status Operation failed
|
|
# Connection default
|
|
DROP TABLE t1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
#
|
|
# Bug#42230 during add index, cannot do queries on storage engines
|
|
# that implement add_index
|
|
#
|
|
DROP DATABASE IF EXISTS db1;
|
|
DROP TABLE IF EXISTS t1;
|
|
# Test 1: Secondary index, should not block reads (original test case).
|
|
# Connection default
|
|
CREATE DATABASE db1;
|
|
CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb;
|
|
INSERT INTO db1.t1(value) VALUES (1), (2);
|
|
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
|
|
# Sending:
|
|
ALTER TABLE db1.t1 ADD INDEX(value);
|
|
# Connection con1
|
|
SET DEBUG_SYNC= "now WAIT_FOR manage";
|
|
USE db1;
|
|
SELECT * FROM t1;
|
|
id value
|
|
1 1
|
|
2 2
|
|
SET DEBUG_SYNC= "now SIGNAL query";
|
|
# Connection default
|
|
# Reaping: ALTER TABLE db1.t1 ADD INDEX(value)
|
|
DROP DATABASE db1;
|
|
# Test 2: Primary index (implicit), should block writes.
|
|
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
|
|
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
|
|
# Sending:
|
|
ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED;
|
|
# Connection con1
|
|
SET DEBUG_SYNC= "now WAIT_FOR manage";
|
|
USE test;
|
|
SELECT * FROM t1;
|
|
a b
|
|
# Sending:
|
|
UPDATE t1 SET a=NULL;
|
|
# Connection con2
|
|
# Waiting for SELECT to be blocked by the metadata lock on t1
|
|
SET DEBUG_SYNC= "now SIGNAL query";
|
|
# Connection default
|
|
# Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a)
|
|
# Connection con1
|
|
# Reaping: UPDATE t1 SET a=NULL
|
|
# Test 3: Primary index (explicit), should block writes.
|
|
# Connection default
|
|
ALTER TABLE t1 DROP INDEX a;
|
|
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
|
|
# Sending:
|
|
ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED;
|
|
# Connection con1
|
|
SET DEBUG_SYNC= "now WAIT_FOR manage";
|
|
SELECT * FROM t1;
|
|
a b
|
|
# Sending:
|
|
UPDATE t1 SET a=NULL;
|
|
# Connection con2
|
|
# Waiting for SELECT to be blocked by the metadata lock on t1
|
|
SET DEBUG_SYNC= "now SIGNAL query";
|
|
# Connection default
|
|
# Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
|
|
# Connection con1
|
|
# Reaping: UPDATE t1 SET a=NULL
|
|
# Test 4: Secondary unique index, should not block reads.
|
|
# Connection default
|
|
SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
|
|
# Sending:
|
|
ALTER TABLE t1 ADD UNIQUE (b);
|
|
# Connection con1
|
|
SET DEBUG_SYNC= "now WAIT_FOR manage";
|
|
SELECT * FROM t1;
|
|
a b
|
|
SET DEBUG_SYNC= "now SIGNAL query";
|
|
# Connection default
|
|
# Reaping: ALTER TABLE t1 ADD UNIQUE (b)
|
|
SET DEBUG_SYNC= "RESET";
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#11853126 RE-ENABLE CONCURRENT READS WHILE CREATING SECONDARY INDEX
|
|
# IN INNODB
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
|
|
INSERT INTO t1 VALUES (1, 12345), (2, 23456);
|
|
# Connection con1
|
|
SET SESSION debug= "+d,alter_table_rollback_new_index";
|
|
ALTER TABLE t1 ADD PRIMARY KEY(a);
|
|
ERROR HY000: Unknown error
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 12345
|
|
2 23456
|
|
# Connection default
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 12345
|
|
2 23456
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
DROP DATABASE IF EXISTS db1;
|
|
CREATE TABLE t1(a int) engine=InnoDB;
|
|
CREATE DATABASE db1;
|
|
# Connection con1
|
|
SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue';
|
|
# Sending:
|
|
ALTER TABLE t1 RENAME db1.t1;
|
|
# Connection con2
|
|
SET DEBUG_SYNC= 'now WAIT_FOR locked';
|
|
# DROP DATABASE db1 should now be blocked by ALTER TABLE
|
|
# Sending:
|
|
DROP DATABASE db1;
|
|
# Connection default
|
|
# Check that DROP DATABASE is blocked by IX lock on db1
|
|
# Resume ALTER TABLE
|
|
SET DEBUG_SYNC= 'now SIGNAL continue';
|
|
# Connection con1
|
|
# Reaping: ALTER TABLE t1 RENAME db1.t1;
|
|
# Connection con2
|
|
# Reaping: DROP DATABASE db1
|
|
# Connection default;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
#
|
|
# WL#5534 Online ALTER, Phase 1
|
|
#
|
|
# Multi thread tests.
|
|
# See alter_table.test for single thread tests.
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB;
|
|
INSERT INTO t1 VALUES (1,1), (2,2);
|
|
SET DEBUG_SYNC= 'RESET';
|
|
SET SESSION lock_wait_timeout= 1;
|
|
#
|
|
# 1: In-place + writes blocked.
|
|
#
|
|
# Connection default
|
|
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
|
|
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
|
|
SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3';
|
|
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4';
|
|
# Sending:
|
|
ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED;
|
|
# Connection con1;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR opened';
|
|
# At this point, neither reads nor writes should be blocked.
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
INSERT INTO t1 VALUES (3,3);
|
|
SET DEBUG_SYNC= 'now SIGNAL continue1';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
|
|
# Now both reads and writes should be blocked
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (4,4);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue2';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR beforecommit';
|
|
# Still both reads and writes should be blocked.
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (5,5);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue3';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
|
|
# Same here.
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (6,6);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue4';
|
|
# Connection default
|
|
# Reaping ALTER TABLE ...
|
|
SET DEBUG_SYNC= 'RESET';
|
|
DELETE FROM t1 WHERE a= 3;
|
|
#
|
|
# 2: Copy + writes blocked.
|
|
#
|
|
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
|
|
SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
|
|
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3';
|
|
# Sending:
|
|
ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED;
|
|
# Connection con1;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR opened';
|
|
# At this point, neither reads nor writes should be blocked.
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
INSERT INTO t1 VALUES (3,3);
|
|
SET DEBUG_SYNC= 'now SIGNAL continue1';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
|
|
# Now writes should be blocked, reads still allowed.
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
3 3
|
|
INSERT INTO t1 VALUES (4,4);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue2';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
|
|
# Now both reads and writes should be blocked.
|
|
SELECT * FROM t1 limit 1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (5,5);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue3';
|
|
# Connection default
|
|
# Reaping ALTER TABLE ...
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'i2' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
|
|
SET DEBUG_SYNC= 'RESET';
|
|
DELETE FROM t1 WHERE a= 3;
|
|
#
|
|
# 3: In-place + writes allowed.
|
|
#
|
|
# TODO: Enable this test once WL#5526 is pushed
|
|
#
|
|
# 4: In-place + reads and writes blocked.
|
|
#
|
|
# Connection default
|
|
SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1';
|
|
SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2';
|
|
SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3';
|
|
SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4';
|
|
# Sending:
|
|
ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE;
|
|
# Connection con1;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR opened';
|
|
# At this point, neither reads nor writes should be blocked.
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
2 2
|
|
INSERT INTO t1 VALUES (3,3);
|
|
SET DEBUG_SYNC= 'now SIGNAL continue1';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR upgraded';
|
|
# Now both reads and writes should be blocked.
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (4,4);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue2';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR beforecommit';
|
|
# Same here.
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (5,5);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue3';
|
|
SET DEBUG_SYNC= 'now WAIT_FOR binlog';
|
|
# Same here.
|
|
SELECT * FROM t1;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
INSERT INTO t1 VALUES (6,6);
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC= 'now SIGNAL continue4';
|
|
# Connection default
|
|
# Reaping ALTER TABLE ...
|
|
Warnings:
|
|
Warning 1831 Duplicate index 'i4' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
|
|
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;
|
|
#
|
|
# BUG#20367116: ALTER TABLE BREAKS ON DELETE CASCADE FOREIGN KEY
|
|
# CONSTRAINT
|
|
# Test case to ensure there are no orphaned rows.
|
|
# (ALTER TABLE, COPY) Algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, foreign key(f2) REFERENCES t1(f1)
|
|
ON DELETE CASCADE)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1);
|
|
SET DEBUG_SYNC= 'alter_after_copy_table SIGNAL delete_parent WAIT_FOR delete_child';
|
|
ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=COPY;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR delete_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below
|
|
# times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now SIGNAL delete_child';
|
|
connection con1;
|
|
connection default;
|
|
# Without the patch, there will be an orphaned row in table 't2'.
|
|
SELECT * FROM t2;
|
|
f2 f3
|
|
SELECT * FROM t1;
|
|
f1
|
|
DROP TABLE t2, t1;
|
|
# Cleanup
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
# Test case to ensure there is no deadlock.
|
|
# (ALTER TABLE, INPLACE) algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1) ON DELETE CASCADE)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1);
|
|
SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent WAIT_FOR alter_child';
|
|
ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=INPLACE;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC='now WAIT_FOR delete_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC='now signal alter_child';
|
|
connection con1;
|
|
# Cleanup
|
|
connection default;
|
|
DROP TABLE t2, t1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
# Test case to ensure that the parent's parent is also locked.
|
|
# (ALTER TABLE, COPY) Algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, f3 INT NOT NULL, FOREIGN KEY(f2)
|
|
REFERENCES t1(f1) ON DELETE CASCADE,
|
|
PRIMARY KEY(f3))ENGINE=INNODB;
|
|
CREATE TABLE t3(f4 INT NOT NULL, FOREIGN KEY(f4) REFERENCES t2(f3)
|
|
ON DELETE CASCADE) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, 2);
|
|
INSERT INTO t3 VALUES(2);
|
|
SET DEBUG_SYNC= 'alter_after_copy_table SIGNAL delete_parent_parent WAIT_FOR delete_child';
|
|
ALTER TABLE t3 ADD f5 INT NOT NULL, ALGORITHM=COPY;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR delete_parent_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below
|
|
# times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now SIGNAL delete_child';
|
|
connection con1;
|
|
connection default;
|
|
# Without the patch, there will be an orphaned row in table 't3'.
|
|
SELECT * FROM t1;
|
|
f1
|
|
SELECT * FROM t2;
|
|
f2 f3
|
|
SELECT * FROM t3;
|
|
f4 f5
|
|
DROP TABLE t3, t2, t1;
|
|
# Cleanup
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
# Test case to ensure there is no deadlock by locking parent's parent.
|
|
# (ALTER TABLE, INPLACE) algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, f3 INT NOT NULL, FOREIGN KEY(f2)
|
|
REFERENCES t1(f1) ON DELETE CASCADE,
|
|
PRIMARY KEY(f3))ENGINE=INNODB;
|
|
CREATE TABLE t3(f4 INT NOT NULL, FOREIGN KEY(f4) REFERENCES t2(f3)
|
|
ON DELETE CASCADE) ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1, 2);
|
|
INSERT INTO t3 VALUES(2);
|
|
SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent_parent WAIT_FOR alter_child';
|
|
ALTER TABLE t3 ADD f3 INT NOT NULL, ALGORITHM=INPLACE;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC='now WAIT_FOR delete_parent_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC='now signal alter_child';
|
|
connection con1;
|
|
# Cleanup
|
|
connection default;
|
|
DROP TABLE t3, t2, t1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
# Test case where ALTER is performed under LOCK TABLES.
|
|
# (ALTER TABLE, COPY) Algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1)) ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1)
|
|
ON DELETE CASCADE)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1);
|
|
SET DEBUG_SYNC= 'alter_after_copy_table SIGNAL delete_parent_parent WAIT_FOR delete_child';
|
|
LOCK TABLES t2 WRITE;
|
|
ALTER TABLE t2 ADD f5 INT NOT NULL, ALGORITHM=COPY;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now WAIT_FOR delete_parent_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below
|
|
# times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC= 'now SIGNAL delete_child';
|
|
connection default;
|
|
UNLOCK TABLES;
|
|
connection con1;
|
|
connection default;
|
|
# Without the patch, there will be an orphaned row in table 't2'.
|
|
SELECT * FROM t1;
|
|
f1
|
|
SELECT * FROM t2;
|
|
f2 f5
|
|
DROP TABLE t2, t1;
|
|
# Cleanup
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
# Test case where ALTER is performed under LOCK TABLES.
|
|
# (ALTER TABLE, INPLACE) algorithm.
|
|
CREATE TABLE t1(f1 INT NOT NULL, PRIMARY KEY(f1))ENGINE=INNODB;
|
|
CREATE TABLE t2(f2 INT NOT NULL, FOREIGN KEY(f2) REFERENCES t1(f1)
|
|
ON DELETE CASCADE)ENGINE=INNODB;
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t2 VALUES(1);
|
|
SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL delete_parent_parent WAIT_FOR alter_child';
|
|
LOCK TABLES t2 WRITE;
|
|
ALTER TABLE t2 ADD f3 INT NOT NULL, ALGORITHM=INPLACE;
|
|
connect con1, localhost, root,,;
|
|
SET DEBUG_SYNC='now WAIT_FOR delete_parent_parent';
|
|
DELETE FROM t1 WHERE f1 = 1;
|
|
# Without the patch, there is no table MDL wait, so the below times out.
|
|
connect con2, localhost, root,,;
|
|
SET DEBUG_SYNC='now signal alter_child';
|
|
connection default;
|
|
UNLOCK TABLES;
|
|
connection con1;
|
|
connection default;
|
|
# Cleanup
|
|
DROP TABLE t2, t1;
|
|
SET DEBUG_SYNC= 'RESET';
|
|
disconnect con1;
|
|
disconnect con2;
|
|
#
|
|
# BUG#21631284: DROP VIRTUAL COLUMN RESULT IN DROP WRONG INDEX.
|
|
#
|
|
# Index is not rebuilt, since there is no change in the definition.
|
|
CREATE TABLE t1 (fld1 VARCHAR(300), fld2 INT, KEY idx1(fld2, fld1(200)))
|
|
ENGINE=InnoDB;
|
|
SET debug="+d,innodb_index_drop_count_zero";
|
|
#Without the patch, an error is reported.
|
|
ALTER TABLE t1 FORCE;
|
|
#cleanup
|
|
DROP TABLE t1;
|
|
SET debug="-d,innodb_index_drop_count_zero";
|
|
# Index is rebuilt since the index is changed from prefixed
|
|
# to non-prefixed index.
|
|
CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1(5))) ENGINE=InnoDB;
|
|
SET debug="+d,innodb_index_drop_count_one";
|
|
#Without the patch, an error is reported.
|
|
ALTER TABLE t1 MODIFY fld1 CHAR(5);
|
|
#cleanup
|
|
DROP TABLE t1;
|
|
SET debug="-d,innodb_index_drop_count_one";
|
|
# Coverage test cases.
|
|
# Index is rebuilt since the index is changed from non-prefixed
|
|
# to prefixed index.
|
|
CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1)) ENGINE=InnoDB;
|
|
SET debug="+d,innodb_index_drop_count_one";
|
|
#In case of incorrect behavior, an error is reported.
|
|
ALTER TABLE t1 MODIFY fld1 CHAR(5);
|
|
#cleanup
|
|
DROP TABLE t1;
|
|
SET debug="-d,innodb_index_drop_count_one";
|
|
# Index is not rebuilt since the index prefix length is
|
|
# the same.
|
|
CREATE TABLE t1 (fld1 CHAR(10), KEY idx1(fld1(5))) ENGINE=InnoDB;
|
|
SET debug="+d,innodb_index_drop_count_zero";
|
|
#In case of incorrect behavior, an error is reported.
|
|
ALTER TABLE t1 MODIFY fld1 CHAR(20);
|
|
#cleanup
|
|
DROP TABLE t1;
|
|
SET debug="-d,innodb_index_drop_count_zero";
|
|
#
|
|
# BUG#26848813: INDEXED COLUMN CAN'T BE CHANGED FROM VARCHAR(15)
|
|
# TO VARCHAR(40) INSTANTANEOUSLY
|
|
CREATE TABLE t1(fld1 VARCHAR(5), KEY(fld1)) ENGINE= InnoDB;
|
|
SET DEBUG="+d,innodb_index_drop_count_zero";
|
|
# Without patch, an error is reported.
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(7), ALGORITHM= INPLACE;
|
|
# Scenario where non-packed keys is converted to packed keys
|
|
# before the patch, an error is reported.
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(9), ALGORITHM= INPLACE;
|
|
SET DEBUG="-d,innodb_index_drop_count_zero";
|
|
# Tests added for covering cases where rebuild is required.
|
|
# Reducing the size of the field.
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(3), ALGORITHM= INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
|
|
# Increasing the size of the field to boundary condition.
|
|
ALTER TABLE t1 MODIFY fld1 VARCHAR(256), ALGORITHM= INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
|
|
DROP TABLE t1;
|