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
|
#
# CREATE TABLE AS SELECT tests
#
--source include/galera_cluster.inc
--source include/have_debug_sync.inc
--source include/have_debug.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
# Pause applying CTAS command from the other node
SET GLOBAL DEBUG_DBUG = 'd,sync.wsrep_apply_cb';
CREATE TABLE t2 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1),(2),(3),(4),(5);
# Wait until local CTAS grabs MDL lock and let applied CTAS BF-abort it
SET DEBUG_SYNC = 'create_table_select_before_create WAIT_FOR sync.wsrep_apply_cb_reached';
SET DEBUG_SYNC = 'create_table_select_before_lock SIGNAL signal.wsrep_apply_cb WAIT_FOR bf_abort';
--send CREATE TABLE t1 AS SELECT * FROM t2;
--connection node_2
SELECT COUNT(*) = 5 FROM t2;
CREATE TABLE t1 AS SELECT * FROM t2;
--connection node_1
--error ER_QUERY_INTERRUPTED
--reap
SET GLOBAL DEBUG_DBUG = '';
SET DEBUG_SYNC = 'RESET';
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;
|
