summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/upgrade.test
blob: ed75b8649673ac1ccb97983a440140973af31cbb (plain)
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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
call mtr.add_suppression("Invalid .old.. table or database name");
--source include/not_embedded.inc

#remove this include after fix MDEV-27872
--source include/no_view_protocol.inc

--disable_warnings
drop database if exists `mysqltest1`;
drop database if exists `mysqltest-1`;
drop database if exists `#mysql50#mysqltest-1`;
--enable_warnings

create database `mysqltest1`;
create database `#mysql50#mysqltest-1`;
create table `mysqltest1`.`t1` (a int);
create table `mysqltest1`.`#mysql50#t-1` (a int);
create table `#mysql50#mysqltest-1`.`t1` (a int);
create table `#mysql50#mysqltest-1`.`#mysql50#t-1` (a int);
show create database `mysqltest1`;
--error 1049
show create database `mysqltest-1`;
show create database `#mysql50#mysqltest-1`;
show tables in `mysqltest1`;
show tables in `#mysql50#mysqltest-1`;

--exec $MYSQL_CHECK --all-databases --fix-db-names --fix-table-names

show create database `mysqltest1`;
show create database `mysqltest-1`;
--error 1049
show create database `#mysql50#mysqltest-1`;
show tables in `mysqltest1`;
show tables in `mysqltest-1`;
drop database `mysqltest1`;
drop database `mysqltest-1`;

#
# Bug#17142: Crash if create with encoded name
#
create table `txu#p#p1` (s1 int);
insert into `txu#p#p1` values (1);
--error 1146
select * from `txu@0023p@0023p1`;
create table `txu@0023p@0023p1` (s1 int);
show tables;
insert into `txu@0023p@0023p1` values (2);
select * from `txu@0023p@0023p1`;
select * from `txu#p#p1`;
drop table `txu#p#p1`;
drop table `txu@0023p@0023p1`;

--echo #
--echo # Bug#37631 Incorrect key file for table after upgrading from 5.0 to 5.1
--echo #
--echo # copy table created using mysql4.0 into the data dir
let $MYSQLD_DATADIR= `SELECT @@datadir`;
copy_file std_data/bug37631.frm $MYSQLD_DATADIR/test/t1.frm;
copy_file std_data/bug37631.MYD $MYSQLD_DATADIR/test/t1.MYD;
copy_file std_data/bug37631.MYI $MYSQLD_DATADIR/test/t1.MYI;
--echo # check the table created using mysql 4.0
CHECK TABLE t1;
--echo # query the table created using mysql 4.0
SELECT * FROM t1;
DROP TABLE t1;

#
# Check if old tables work
#

let $MYSQLD_DATADIR= `select @@datadir`;
--error 0,1
--remove_file $MYSQLD_DATADIR/test/t1.frm
--copy_file std_data/old_table-323.frm $MYSQLD_DATADIR/test/t1.frm
truncate t1;
drop table t1;

#
# Bug#28360 (RENAME DATABASE destroys routines)
#

--disable_warnings
drop database if exists `tabc`;
drop database if exists `a-b-c`;
--enable_warnings

create database `tabc` default character set latin2;
create table tabc.t1 (a int);
FLUSH TABLES;

# Manually make a 5.0 database from the template
--mkdir $MYSQLD_DATADIR/a-b-c
--copy_file $MYSQLD_DATADIR/tabc/db.opt $MYSQLD_DATADIR/a-b-c/db.opt
--copy_file $MYSQLD_DATADIR/tabc/t1.frm $MYSQLD_DATADIR/a-b-c/t1.frm
--copy_file $MYSQLD_DATADIR/tabc/t1.MYD $MYSQLD_DATADIR/a-b-c/t1.MYD
--copy_file $MYSQLD_DATADIR/tabc/t1.MYI $MYSQLD_DATADIR/a-b-c/t1.MYI

show databases like '%a-b-c%';
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
# The physical directory name is now a@002db@002dc, the logical name still a-b-c
show databases like '%a-b-c%';
show create database `a-b-c`;
show tables in `a-b-c`;
show create table `a-b-c`.`t1`;
drop database `a-b-c`;
drop database `tabc`;

#
# Bug#43385 Cannot ALTER DATABASE ... UPGRADE DATA DIRECTORY NAME when Views exist
#
let $MYSQLD_DATADIR= `select @@datadir`;
--mkdir $MYSQLD_DATADIR/a-b-c
use `#mysql50#a-b-c`;
create table t1(f1 char(10));

--write_file $MYSQLD_DATADIR/a-b-c/v1.frm
TYPE=VIEW
query=select `a`.`f1` AS `f1` from `a-b-c`.`t1` `a` join `information_schema`.`tables` `b` where (convert(`a`.`f1` using utf8) = `b`.`TABLE_NAME`)
md5=068271f1c657fe115e497856ca0fa493
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2009-04-10 11:53:37
create-version=1
source=select f1 from `a-b-c`.t1 a, information_schema.tables b\nwhere a.f1 = b.table_name
client_cs_name=utf8
connection_cl_name=utf8_general_ci
EOF

show databases like '%a-b-c%';
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
show databases like '%a-b-c%';
show create view `a-b-c`.v1;
--disable_ps_protocol
select * from `a-b-c`.v1;
--enable_ps_protocol
drop database `a-b-c`;
use test;

--echo # End of 5.0 tests

--echo #
--echo # Bug #53804: serious flaws in the alter database .. upgrade data
--echo #             directory name command
--echo #

--error ER_BAD_DB_ERROR
ALTER DATABASE `#mysql50#:` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#.` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#../` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#../..` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#../../` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#./blablabla` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#../blablabla` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#/` UPGRADE DATA DIRECTORY NAME;
--error ER_WRONG_DB_NAME
ALTER DATABASE `#mysql50#/.` UPGRADE DATA DIRECTORY NAME;

--error ER_WRONG_DB_NAME
USE `#mysql50#.`;
--error ER_WRONG_DB_NAME
USE `#mysql50#../blablabla`;

#
# Test of Bug #56441: mysql_upgrade 5.0->5.1 fails for tables with long names
#
copy_file $MYSQL_TEST_DIR/std_data/long_table_name.MYI $MYSQLD_DATADIR/test/ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com.MYI;
copy_file $MYSQL_TEST_DIR/std_data/long_table_name.MYD $MYSQLD_DATADIR/test/ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com.MYD;
copy_file $MYSQL_TEST_DIR/std_data/long_table_name.frm $MYSQLD_DATADIR/test/ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com.frm;
show full tables;
rename table `#mysql50#ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com` to `ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com`;
show full tables;
drop table `ltoriaeinnovacionendesarrolloempres#9120761097220077376#cio_com`;

--echo # End of 5.1 tests