You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
237 lines
8.4 KiB
237 lines
8.4 KiB
5 months ago
|
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)) engine=myisam;
|
||
|
|
create table t2 (a int not null auto_increment, b char(16) not null, primary key (a)) engine=myisam;
|
||
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_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 HY000: Incorrect path value: '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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
||
|
|
drop table t1;
|
||
|
|
CREATE TABLE t1(a INT) ENGINE = MYISAM
|
||
|
|
DATA DIRECTORY='TEST_DIR/tmp'
|
||
|
|
INDEX DIRECTORY='TEST_DIR/tmp';
|
||
|
|
Got one of the listed errors
|
||
|
|
CREATE TABLE t2(a INT) ENGINE = MYISAM
|
||
|
|
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' (OS errno 17 - File exists)
|
||
|
|
DROP TABLE t2;
|
||
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/log/'
|
||
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/log/'
|
||
|
|
create table t1 (a int) engine=myisam select 42 a;
|
||
|
|
select * from t1;
|
||
|
|
a
|
||
|
|
9
|
||
|
|
select * from t1;
|
||
|
|
a
|
||
|
|
99
|
||
|
|
select * from t1;
|
||
|
|
a
|
||
|
|
42
|
||
|
|
drop table t1;
|
||
|
|
End of 4.1 tests
|
||
|
|
SET SESSION keep_files_on_create = TRUE;
|
||
|
|
CREATE TABLE t1 (a INT) ENGINE MYISAM;
|
||
|
|
Got one of the listed errors
|
||
|
|
SET SESSION keep_files_on_create = FALSE;
|
||
|
|
CREATE TABLE t1 (a INT) ENGINE MYISAM;
|
||
|
|
DROP TABLE t1;
|
||
|
|
End of 5.0 tests
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM
|
||
|
|
INDEX DIRECTORY='MYSQLD_DATADIR/mysql';
|
||
|
|
DROP TABLE IF EXISTS t1;
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM
|
||
|
|
DATA DIRECTORY='MYSQLD_DATADIR/test';
|
||
|
|
DROP TABLE IF EXISTS t1;
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM
|
||
|
|
DATA DIRECTORY='MYSQLD_DATADIR/';
|
||
|
|
DROP TABLE IF EXISTS t1;
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM
|
||
|
|
INDEX DIRECTORY='MYSQLD_DATADIR';
|
||
|
|
DROP TABLE IF EXISTS t1;
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM
|
||
|
|
INDEX DIRECTORY='TEST_DIR/master-data_var';
|
||
|
|
ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (OS errno 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 SCHEMA schema1;
|
||
|
|
CREATE TABLE schema1.t1 (a INT, b INT) ENGINE=MYISAM;
|
||
|
|
INSERT INTO schema1.t1 VALUES(1,1);
|
||
|
|
INSERT INTO schema1.t1 VALUES(2,2);
|
||
|
|
CREATE TABLE t1(a INT) ENGINE=MYISAM DATA DIRECTORY='MYSQL_TMP_DIR/mysql'
|
||
|
|
INDEX DIRECTORY='MYSQL_TMP_DIR/mysql';
|
||
|
|
FLUSH TABLE t1;
|
||
|
|
# Symlinking schema1 database to tmpdir
|
||
|
|
FLUSH TABLE schema1.t1;
|
||
|
|
DROP TABLE t1;
|
||
|
|
FLUSH TABLE schema1.t1;
|
||
|
|
SELECT * FROM schema1.t1;
|
||
|
|
a b
|
||
|
|
1 1
|
||
|
|
2 2
|
||
|
|
DROP SCHEMA schema1;
|
||
|
|
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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 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=utf8mb4 COLLATE=utf8mb4_0900_ai_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";
|
||
|
|
Warnings:
|
||
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
||
|
|
DROP TABLE test.t1;
|