1#
2# Test of rename table
3#
4
5--source include/count_sessions.inc
6
7--disable_warnings
8drop table if exists t0,t1,t2,t3,t4;
9# Clear up from other tests (to ensure that SHOW TABLES below is right)
10drop table if exists t0,t5,t6,t7,t8,t9,t1_1,t1_2,t9_1,t9_2;
11--enable_warnings
12
13create table t0 SELECT 1,"table 1";
14create table t2 SELECT 2,"table 2";
15create table t3 SELECT 3,"table 3";
16rename table t0 to t1;
17rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
18select * from t1;
19rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
20rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
21select * from t1;
22
23# The following should give errors
24--error ER_TABLE_EXISTS_ERROR
25rename table t1 to t2;
26--error ER_TABLE_EXISTS_ERROR
27rename table t1 to t1;
28--error ER_TABLE_EXISTS_ERROR
29rename table t3 to t4, t2 to t3, t1 to t2, t4 to t2;
30show tables like "t_";
31--error ER_TABLE_EXISTS_ERROR
32rename table t3 to t1, t2 to t3, t1 to t2, t4 to t1;
33--error ER_NO_SUCH_TABLE
34rename table t3 to t4, t5 to t3, t1 to t2, t4 to t1;
35
36select * from t1;
37select * from t2;
38select * from t3;
39
40# This should give a warning for t4
41drop table if exists t1,t2,t3,t4;
42
43#
44# Bug #2397 RENAME TABLES is not blocked by
45# FLUSH TABLES WITH READ LOCK
46#
47
48connect (con1,localhost,root,,);
49connect (con2,localhost,root,,);
50
51connection con1;
52CREATE TABLE t1 (a int);
53CREATE TABLE t3 (a int);
54connection con2;
55FLUSH TABLES WITH READ LOCK;
56connection con1;
57send RENAME TABLE t1 TO t2, t3 to t4;
58connection con2;
59show tables;
60UNLOCK TABLES;
61connection con1;
62reap;
63connection con2;
64
65# Wait for the the tables to be renamed
66# i.e the query below succeds
67let $query= select * from t2, t4;
68source include/wait_for_query_to_succeed.inc;
69
70show tables;
71
72drop table t2, t4;
73
74disconnect con2;
75disconnect con1;
76connection default;
77
78
79--echo End of 4.1 tests
80
81
82--echo #
83--echo # Bug#14959: "ALTER TABLE isn't able to rename a view"
84--echo # Bug#53976: "ALTER TABLE RENAME is allowed on views
85--echo #             (not documented, broken)"
86--echo #
87create table t1(f1 int);
88create view v1 as select * from t1;
89--error ER_WRONG_OBJECT
90alter table v1 rename to v2;
91drop view v1;
92drop table t1;
93
94--echo End of 5.0 tests
95
96--source include/wait_until_count_sessions.inc
97
98#
99# Test of rename with temporary tables
100#
101
102CREATE OR REPLACE TABLE t1 (a INT);
103CREATE OR REPLACE TABLE t2 (a INT);
104CREATE OR REPLACE TEMPORARY TABLE t1_tmp (b INT);
105CREATE OR REPLACE TEMPORARY TABLE t2_tmp (b INT);
106
107# Can't rename table over another one
108--error ER_TABLE_EXISTS_ERROR
109rename table t1 to t2;
110--error ER_TABLE_EXISTS_ERROR
111rename table t1 to tmp, tmp to t2;
112--error ER_TABLE_EXISTS_ERROR
113rename table t1_tmp to t2_tmp;
114--error ER_TABLE_EXISTS_ERROR
115rename table t1_tmp to tmp, tmp to t2_tmp;
116
117show create table t1_tmp;
118show create table t2_tmp;
119
120# The following should work
121rename table t1 to t1_tmp;
122rename table t2_tmp to t2;
123rename table t2 to tmp, tmp to t2;
124rename table t1_tmp to tmp, tmp to t1_tmp;
125show tables;
126SHOW CREATE TABLE t1_tmp;
127drop table t1_tmp;
128SHOW CREATE TABLE t1_tmp;
129drop table t1_tmp;
130SHOW CREATE TABLE t2;
131drop table t2;
132SHOW CREATE TABLE t2;
133drop table t2;
134
135CREATE TABLE t1 (a INT);
136insert into t1 values (1);
137CREATE TEMPORARY TABLE t1 (b INT);
138insert into t1 values (2);
139RENAME TABLE t1 TO tmp, t1 TO t2;
140select * from tmp;
141select * from t2;
142drop table tmp,t2;
143
144#
145# MDEV-11741 handler::ha_reset(): Assertion `bitmap_is_set_all(&table->s->all_set)' failed or server crash in mi_reset or buffer overrun or unexpected ER_CANT_REMOVE_ALL_FIELDS
146#
147create table t1 (a int) engine=memory;
148--error ER_BAD_DB_ERROR
149rename table t1 to non_existent.t2;
150drop table t1;
151