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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
|
drop table if exists t1,t2,t7,t8,t9;
drop database if exists mysqltest;
create table t1 (a int not null auto_increment, b char(16) not null, primary key (a));
create table t2 (a int not null auto_increment, b char(16) not null, primary key (a));
insert into t1 (b) values ("test"),("test1"),("test2"),("test3");
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
insert into t2 (b) select b from t1;
insert into t1 (b) select b from t2;
drop table t2;
create table t9 (a int not null auto_increment, b char(16) not null, primary key (a))
engine=myisam data directory="MYSQLTEST_VARDIR/tmp" index directory="MYSQLTEST_VARDIR/run";
insert into t9 select * from t1;
check table t9;
Table Op Msg_type Msg_text
test.t9 check status OK
optimize table t9;
Table Op Msg_type Msg_text
test.t9 optimize status OK
repair table t9;
Table Op Msg_type Msg_text
test.t9 repair status OK
alter table t9 add column c int not null;
show create table t9;
Table Create Table
t9 CREATE TABLE `t9` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` char(16) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=16725 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/'
alter table t9 rename t8, add column d int not null;
alter table t8 rename t7;
rename table t7 to t9;
drop table t1;
SHOW CREATE TABLE t9;
Table Create Table
t9 CREATE TABLE `t9` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` char(16) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=16725 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/'
create database mysqltest;
create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a))
engine=myisam index directory="/this-dir-does-not-exist";
Got one of the listed errors
create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a))
engine=myisam index directory="not-hard-path";
ERROR 42000: Incorrect table name 'not-hard-path'
create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a))
engine=myisam index directory="MYSQLTEST_VARDIR/run";
Got one of the listed errors
create table mysqltest.t9 (a int not null auto_increment, b char(16) not null, primary key (a))
engine=myisam data directory="MYSQLTEST_VARDIR/tmp";
Got one of the listed errors
alter table t9 rename mysqltest.t9;
select count(*) from mysqltest.t9;
count(*)
16724
show create table mysqltest.t9;
Table Create Table
t9 CREATE TABLE `t9` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` char(16) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=16725 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/'
drop database mysqltest;
create table t1 (a int not null) engine=myisam;
alter table t1 data directory="MYSQLTEST_VARDIR/tmp";
Warnings:
Warning 1618 <DATA DIRECTORY> option ignored
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
alter table t1 add b int;
alter table t1 data directory="MYSQLTEST_VARDIR/log";
Warnings:
Warning 1618 <DATA DIRECTORY> option ignored
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
alter table t1 index directory="MYSQLTEST_VARDIR/log";
Warnings:
Warning 1618 <INDEX DIRECTORY> option ignored
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
CREATE TABLE t1(a INT)
DATA DIRECTORY='TEST_DIR/tmp'
INDEX DIRECTORY='TEST_DIR/tmp';
Got one of the listed errors
CREATE TABLE t2(a INT)
DATA DIRECTORY='TEST_DIR/tmp'
INDEX DIRECTORY='TEST_DIR/tmp';
RENAME TABLE t2 TO t1;
ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17 "File exists")
DROP TABLE t2;
connect session1,localhost,root,,;
connect session2,localhost,root,,;
connection session1;
create temporary table t1 (a int) engine=myisam data directory="MYSQLTEST_VARDIR/log" select 9 a;
show create table t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/log/'
connection session2;
create temporary table t1 (a int) engine=myisam data directory="MYSQLTEST_VARDIR/log" select 99 a;
show create table t1;
Table Create Table
t1 CREATE TEMPORARY TABLE `t1` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/log/'
connection default;
create table t1 (a int) engine=myisam select 42 a;
connection session1;
select * from t1;
a
9
disconnect session1;
connection session2;
select * from t1;
a
99
disconnect session2;
connection default;
select * from t1;
a
42
drop table t1;
End of 4.1 tests
SET SESSION keep_files_on_create = TRUE;
Warnings:
Warning 1287 '@@keep_files_on_create' is deprecated and will be removed in a future release
CREATE TABLE t1 (a INT) ENGINE MYISAM;
Got one of the listed errors
SET SESSION keep_files_on_create = FALSE;
Warnings:
Warning 1287 '@@keep_files_on_create' is deprecated and will be removed in a future release
CREATE TABLE t1 (a INT) ENGINE MYISAM;
DROP TABLE t1;
End of 5.0 tests
CREATE TABLE t1(a INT)
INDEX DIRECTORY='MYSQLD_DATADIR/mysql';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT)
DATA DIRECTORY='MYSQLD_DATADIR/test';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT)
DATA DIRECTORY='MYSQLD_DATADIR/';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT)
INDEX DIRECTORY='MYSQLD_DATADIR';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INT)
INDEX DIRECTORY='TEST_DIR/master-data_var';
ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errcode: 2 "No such file or directory")
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='NO_DIR_IN_CREATE';
CREATE TABLE t1(a INT) DATA DIRECTORY='MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp';
Warnings:
Warning 1618 <DATA DIRECTORY> option ignored
Warning 1618 <INDEX DIRECTORY> option ignored
DROP TABLE t1;
SET @@SQL_MODE=@OLD_SQL_MODE;
#
# BUG#40980 - Drop table can remove another MyISAM table's
# data and index files
#
CREATE TABLE user(a INT) DATA DIRECTORY='MYSQL_TMP_DIR/mysql'
INDEX DIRECTORY='MYSQL_TMP_DIR/mysql';
FLUSH TABLE user;
# Symlinking mysql database to tmpdir
FLUSH TABLE mysql.user;
DROP TABLE user;
FLUSH TABLE mysql.user;
SELECT * FROM mysql.user;
End of 5.1 tests
#
# Test for bug #11759990 - "52354: 'CREATE TABLE .. LIKE ... '
# STATEMENTS FAIL".
#
drop table if exists t1, t2;
create table t1 (a int primary key) engine=myisam
data directory="MYSQLTEST_VARDIR/tmp"
index directory="MYSQLTEST_VARDIR/run";
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/'
select create_options from information_schema.tables where table_name='t1';
create_options
DATA DIRECTORY='MYSQLTEST_VARDIR/tmp/' INDEX DIRECTORY='MYSQLTEST_VARDIR/run/'
# CREATE TABLE LIKE statement on table with INDEX/DATA DIRECTORY
# options should not fail. Per documentation newly created table
# should not inherit value of these options from the original table.
create table t2 like t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop tables t1, t2;
#
# Test for bug #25514146 DB_NAME IS IGNORED WHEN CREATING TABLE
# WITH DATA DIRECTORY
#
# Make sure we have no current database
CREATE DATABASE x;
USE x;
DROP DATABASE x;
CREATE TABLE test.t1(id INT(11)) ENGINE MYISAM
DATA DIRECTORY "MYSQLTEST_VARDIR/tmp";
DROP TABLE test.t1;
use test;
create table t1(c1 int, c2 int, c3 varchar(100)) engine=MyISAM data directory='MYSQL_TMP_DIR' index directory = 'MYSQL_TMP_DIR';
insert t1 values (1,2,3), (2,3,4), (3,4,5), (4,5,6), (5,6,7), (6,7,8), (7,8,9);
alter online table t1 delay_key_write=1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DELAY_KEY_WRITE=1 DATA DIRECTORY='MYSQL_TMP_DIR/' INDEX DIRECTORY='MYSQL_TMP_DIR/'
drop table t1;
|
