EVOLUTION-MANAGER

Edit File: partition_exchange_innodb.result

# Test with AUTO_INCREMENT
CREATE TABLE tp
(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b varchar(24))
ENGINE = 'InnoDB'
PARTITION BY HASH (a) PARTITIONS 4;
CREATE TABLE t LIKE tp;
ALTER TABLE t REMOVE PARTITIONING;
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY HASH (`a`)
PARTITIONS 4
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO tp (b) VALUES ("One"), ("Two"), ("Three"), ("Four"), ("Five"),
("Six"), ("Seven"), ("Eight"), ("Nine"), ("Ten"), ("Eleven"), ("Twelwe");
INSERT INTO tp VALUES (97, "Ninety seven");
INSERT INTO tp VALUES (111, "One hundred eleven");
INSERT INTO tp VALUES (101, "One hundred one");
SET INSERT_ID = 13;
INSERT INTO t (b) VALUES ("Thirteen");
SET INSERT_ID = 21;
INSERT INTO t (b) VALUES ("Twenty one");
SET INSERT_ID = 25;
INSERT INTO t (b) VALUES ("Twenty five");
SET INSERT_ID = 55;
INSERT INTO t (b) VALUES ("Fifty five");
DELETE FROM tp WHERE a = 111;
DELETE FROM t WHERE a = 55;
UPDATE tp SET a = 41 WHERE a = 101;
UPDATE t SET a = 17 WHERE a = 25;
SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp';
PARTITION_NAME	HAVE_ROWS
p0	YES
p1	YES
p2	YES
p3	YES
SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't';
HAVE_ROWS
YES
ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp';
PARTITION_NAME	HAVE_ROWS
p0	YES
p1	YES
p2	YES
p3	YES
SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't';
HAVE_ROWS
YES
SELECT * FROM tp ORDER BY a;
a	b
2	Two
3	Three
4	Four
6	Six
7	Seven
8	Eight
10	Ten
11	Eleven
12	Twelwe
13	Thirteen
17	Twenty five
21	Twenty one
SELECT * FROM t ORDER BY a;
a	b
1	One
5	Five
9	Nine
41	One hundred one
97	Ninety seven
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=latin1
 PARTITION BY HASH (`a`)
PARTITIONS 4
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1
DROP TABLE tp, t;
CREATE TABLE t
(a INT,
b VARCHAR(55),
PRIMARY KEY (a))
ENGINE =  'InnoDB';
CREATE TABLE tp
(a INT,
b VARCHAR(55),
PRIMARY KEY (a))
ENGINE =  'InnoDB'
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
CREATE TABLE tsp
(a INT,
b VARCHAR(55),
PRIMARY KEY (a))
ENGINE =  'InnoDB'
PARTITION BY RANGE (a)
SUBPARTITION BY HASH(a)
(PARTITION p0 VALUES LESS THAN (100)
(SUBPARTITION sp0,
SUBPARTITION sp1),
PARTITION p1 VALUES LESS THAN MAXVALUE
(SUBPARTITION sp2,
SUBPARTITION sp3));
INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"), (99, "End of values");
INSERT INTO tp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
INSERT INTO tsp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
SELECT * FROM t;
a	b
1	First value
3	Three
5	Five
99	End of values
SELECT * FROM tp;
a	b
10	Ten
161	161
162	162
163	163
164	164
2	First value
200	Two hundred, end of values
50	Fifty
61	Sixty one
62	Sixty two
63	Sixty three
64	Sixty four
# Start by testing read/write locking
SET AUTOCOMMIT = 1;
connect con1, localhost, root,,;
SET DEBUG_SYNC= 'swap_partition_after_compare_tables SIGNAL swap_in_progress WAIT_FOR goto_verification';
SET DEBUG_SYNC= 'swap_partition_first_row_read SIGNAL swap_in_progress WAIT_FOR goto_wait';
SET DEBUG_SYNC= 'swap_partition_after_wait SIGNAL swap_in_progress WAIT_FOR goto_rename';
SET DEBUG_SYNC= 'swap_partition_before_rename SIGNAL swap_in_progress WAIT_FOR test_done';
ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
connection default;
SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
# select from t and select/update/delete/insert from tp should work
SELECT * FROM t WHERE a = 99;
a	b
99	End of values
SELECT * FROM tp WHERE a = 61;
a	b
61	Sixty one
# any write (update/delete/insert) into t or tp should fail
SET SESSION lock_wait_timeout=1;
UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM tp WHERE a = 59;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE tp ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SET DEBUG_SYNC= 'now SIGNAL goto_verification';
SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
# select from t and select/update/delete/insert from tp should work
SELECT * FROM t WHERE a = 99;
a	b
99	End of values
SELECT * FROM tp WHERE a = 61;
a	b
61	Sixty one
UPDATE tp SET a = 43, b = concat("Fifty three, was ", b) WHERE a = 63;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM tp WHERE a = 59;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# any write (update/delete/insert) into t should fail
UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE tp ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SET DEBUG_SYNC= 'now SIGNAL goto_wait';
SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
# Both tables should now be under exclusive lock, even SHOW should fail
SELECT * FROM t WHERE a = 99;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM tp WHERE a = 61;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM tp WHERE a = 59;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE t;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE tp;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE tp ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC= 'now SIGNAL goto_rename';
SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
# Both tables should now be under exclusive lock
SELECT * FROM t WHERE a = 99;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT * FROM tp WHERE a = 61;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM tp WHERE a = 59;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DELETE FROM t WHERE a = 3;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE t ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
ALTER TABLE tp ENGINE = 'InnoDB';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE t;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW CREATE TABLE tp;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC= 'now SIGNAL test_done';
connection con1;
connection default;
# Tables should now be as normal
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SELECT * FROM tp WHERE a = 99;
a	b
99	End of values
SELECT * FROM t WHERE a = 61;
a	b
61	Sixty one
UPDATE t SET a = 53, b = "Fifty three, was sixty three" WHERE a = 63;
INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
DELETE FROM t WHERE a = 59;
UPDATE tp SET a = 53, b = "Fifty three, was three" WHERE a = 3;
INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted");
DELETE FROM tp WHERE a = 3;
ALTER TABLE t ENGINE = 'InnoDB';
ALTER TABLE tp ENGINE = 'InnoDB';
disconnect con1;
connection default;
SET DEBUG_SYNC= 'RESET';
SHOW CREATE TABLE t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE tp;
Table	Create Table
tp	CREATE TABLE `tp` (
  `a` int(11) NOT NULL,
  `b` varchar(55) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 PARTITION BY RANGE (`a`)
(PARTITION `p0` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
SELECT * FROM t;
a	b
10	Ten
2	First value
50	Fifty
53	Fifty three, was sixty three
61	Sixty one
62	Sixty two
63	Sixty three, new
64	Sixty four
SELECT * FROM tp;
a	b
1	First value
161	161
162	162
163	163
164	164
200	Two hundred, end of values
5	Five
53	Fifty three, was three
59	To be deleted
63	Sixty three, new
99	End of values
DROP TABLE t, tp, tsp;