1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
#
# CREATE TABLE AS SELECT tests
#
--source include/galera_cluster.inc
--connection node_1
SET SESSION default_storage_engine=InnoDB;
# Left table already exists
CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB;
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
--error ER_TABLE_EXISTS_ERROR
CREATE TABLE t1 AS SELECT * FROM t2;
DROP TABLE t1,t2;
# Right table does not exist
--error ER_NO_SUCH_TABLE
CREATE TABLE t1 AS SELECT * FROM t2;
# No right table at all
CREATE TABLE t1 AS SELECT 1 FROM DUAL;
--connection node_2
SELECT COUNT(*) = 1 FROM t1;
--connection node_1
DROP TABLE t1;
# Empty right table
--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
CREATE TABLE t1 AS SELECT * FROM t2;
--connection node_2
SELECT COUNT(*) = 0 FROM t1;
--connection node_1
DROP TABLE t1,t2;
# Right table is MyISAM
CREATE TABLE t2 (f1 INTEGER) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TABLE t1 AS SELECT * FROM t2;
SELECT COUNT(*) = 5 FROM t1;
--connection node_2
SELECT COUNT(*) = 5 FROM t1;
--connection node_1
DROP TABLE t1,t2;
# Right side is a subquery
--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TABLE t1 AS SELECT MAX(f1) AS f1 FROM t2;
--connection node_2
SELECT COUNT(*) = 1 FROM t1;
SELECT f1 = 5 FROM t1;
--connection node_1
DROP TABLE t1,t2;
# Inside a stored procedure
--connection node_1
DELIMITER |;
CREATE PROCEDURE sp1 ()
BEGIN
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TABLE t1 AS SELECT * FROM t2;
END|
DELIMITER ;|
CALL sp1();
SELECT COUNT(*) = 5 FROM t1;
--connection node_2
SELECT COUNT(*) = 5 FROM t1;
--connection node_1
DROP TABLE t1, t2;
DROP PROCEDURE sp1;
# Inside a prepared statement
--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT * FROM t2';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1, t2;
#
# Multi-master conflict
#
--connection node_1
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
LOCK TABLE t2 WRITE;
--connection node_1
--send CREATE TABLE t1 AS SELECT * FROM t2;
--connection node_1a
--let $wait_condition = SELECT COUNT(*) = 1 FROM information_schema.processlist WHERE STATE LIKE 'Waiting for table metadata lock%'
--source include/wait_condition.inc
--connection node_2
SELECT COUNT(*) = 5 FROM t2;
CREATE TABLE t1 AS SELECT * FROM t2;
--connection node_1a
UNLOCK TABLES;
--connection node_1
--error ER_TABLE_EXISTS_ERROR,ER_QUERY_INTERRUPTED
--reap
DROP TABLE t1, t2;
#
# Temporary table
#
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
CREATE TEMPORARY TABLE t1 AS SELECT * FROM t2;
--connection node_2
--error ER_NO_SUCH_TABLE
SELECT * FROM t1;
CALL mtr.add_suppression("Slave SQL: Error 'Unknown table 'test.t1'' on query");
--connection node_1
DROP TABLE t1, t2;
|
