Debian Codegraph @

mysql-test/suite/galera/t/galera_create_table_as_select.test

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