mirror of
https://github.com/MariaDB/server.git
synced 2025-04-22 15:15:41 +02:00
1a85ae444a
consistently issue a Note 1618 DATA DIRECTORY option ignored Note 1618 INDEX DIRECTORY option ignored in archive/csv/innodb/rocksdb whenever an option is ignored. Note that csv doesn't say "INDEX DIRECTORY option ignored" because it does not create index files at all anywhere. Other engines don't say "INDEX DIRECTORY option ignored" if the table has no indexes. additionally InnoDB doesn't say that if INDEX DIRECTORY is the same as DATA DIRECTORY, because in that case indexes are technically stored in INDEX DIRECTORY. collateral fix: use strmake to zero-terminate the string
339 lines
12 KiB
Text
339 lines
12 KiB
Text
#
|
|
# Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM.
|
|
#
|
|
SET @file_per_table= @@GLOBAL.innodb_file_per_table;
|
|
SET @strict_mode= @@SESSION.innodb_strict_mode;
|
|
SET SESSION innodb_strict_mode = ON;
|
|
#
|
|
# InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON
|
|
#
|
|
SET GLOBAL innodb_file_per_table = OFF;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
|
|
PARTITION BY HASH (c1) (
|
|
PARTITION p0
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
|
|
PARTITION p1
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
|
|
);
|
|
ERROR HY000: Can't create table `test`.`t1` (errno: 140 "Wrong create options")
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
|
|
Error 1005 Can't create table `test`.`t1` (errno: 140 "Wrong create options")
|
|
Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
|
|
Error 6 Error on delete of 'MYSQLD_DATADIR/test/t1.par' (Errcode: 2 "No such file or directory")
|
|
#
|
|
# InnoDB is different from MyISAM in that it uses a text file
|
|
# with an '.isl' extension instead of a symbolic link so that
|
|
# the tablespace can be re-located on any OS. Also, instead of
|
|
# putting the file directly into the DATA DIRECTORY,
|
|
# it adds a folder under it with the name of the database.
|
|
# Since strict mode is off, InnoDB ignores the INDEX DIRECTORY
|
|
# and it is no longer part of the definition.
|
|
#
|
|
SET SESSION innodb_strict_mode = OFF;
|
|
SET GLOBAL innodb_file_per_table = ON;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
CREATE TABLE t1 (c1 INT, KEY(c1)) ENGINE = InnoDB
|
|
PARTITION BY HASH (c1)
|
|
(PARTITION p0
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir',
|
|
PARTITION p1
|
|
DATA DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-data-dir'
|
|
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/mysql-test-idx-dir'
|
|
);
|
|
Warnings:
|
|
Note 1618 INDEX DIRECTORY option ignored
|
|
Note 1618 INDEX DIRECTORY option ignored
|
|
SHOW WARNINGS;
|
|
Level Code Message
|
|
Note 1618 INDEX DIRECTORY option ignored
|
|
Note 1618 INDEX DIRECTORY option ignored
|
|
# Verifying .frm, .par, .isl & .ibd files
|
|
---- MYSQLD_DATADIR/test
|
|
db.opt
|
|
t1#P#p0.isl
|
|
t1#P#p1.isl
|
|
t1.frm
|
|
t1.par
|
|
---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
t1#P#p0.ibd
|
|
t1#P#p1.ibd
|
|
# The ibd tablespaces should not be directly under the DATA DIRECTORY
|
|
---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
test
|
|
---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
FLUSH TABLES;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY HASH (`c1`)
|
|
(PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
|
|
#
|
|
# Verify that the DATA/INDEX DIRECTORY is stored and used if we
|
|
# ALTER TABLE to MyISAM.
|
|
#
|
|
ALTER TABLE t1 engine=MyISAM;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY HASH (`c1`)
|
|
(PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = MyISAM,
|
|
PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = MyISAM)
|
|
# Verifying .frm, .par and MyISAM files (.MYD, MYI)
|
|
db.opt
|
|
t1#P#p0.MYD
|
|
t1#P#p0.MYI
|
|
t1#P#p1.MYD
|
|
t1#P#p1.MYI
|
|
t1.frm
|
|
t1.par
|
|
---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
t1#P#p0.MYD
|
|
t1#P#p1.MYD
|
|
test
|
|
---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
#
|
|
# Now verify that the DATA DIRECTORY is used again if we
|
|
# ALTER TABLE back to InnoDB.
|
|
#
|
|
SET SESSION innodb_strict_mode = ON;
|
|
ALTER TABLE t1 engine=InnoDB;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` int(11) DEFAULT NULL,
|
|
KEY `c1` (`c1`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY HASH (`c1`)
|
|
(PARTITION `p0` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
|
|
# Verifying .frm, .par, .isl and InnoDB .ibd files
|
|
---- MYSQLD_DATADIR/test
|
|
db.opt
|
|
t1#P#p0.isl
|
|
t1#P#p1.isl
|
|
t1.frm
|
|
t1.par
|
|
---- MYSQLTEST_VARDIR/mysql-test-data-dir
|
|
test
|
|
---- MYSQLTEST_VARDIR/mysql-test-idx-dir
|
|
---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
|
|
t1#P#p0.ibd
|
|
t1#P#p1.ibd
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-14611 ALTER TABLE EXCHANGE PARTITION does not work
|
|
# properly when used with DATA DIRECTORY
|
|
#
|
|
SET GLOBAL innodb_file_per_table = ON;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY KEY (myid)
|
|
(
|
|
PARTITION p0001 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0002 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0003 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0004 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
|
|
);
|
|
CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir';
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY KEY (`myid`)
|
|
(PARTITION `p0001` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0002` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0003` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0004` DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
|
|
);
|
|
CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
select * from t1;
|
|
myid myval
|
|
1 one
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`myid`)
|
|
(PARTITION `p0001` VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
|
|
PARTITION `p0002` VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0003` VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0004` VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
|
|
select * from t1;
|
|
myid myval
|
|
2 two
|
|
3 threee
|
|
4 four
|
|
select * from t2;
|
|
myid myval
|
|
1 one
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = INNODB
|
|
);
|
|
CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB;
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
select * from t1;
|
|
myid myval
|
|
1 one
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`myid`)
|
|
(PARTITION `p0001` VALUES LESS THAN (50) ENGINE = InnoDB,
|
|
PARTITION `p0002` VALUES LESS THAN (150) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0003` VALUES LESS THAN (1050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB,
|
|
PARTITION `p0004` VALUES LESS THAN (10050) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' ENGINE = InnoDB)
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci DATA DIRECTORY='MYSQLTEST_VARDIR/mysql-test-data-dir/'
|
|
select * from t1;
|
|
myid myval
|
|
2 two
|
|
3 threee
|
|
4 four
|
|
select * from t2;
|
|
myid myval
|
|
1 one
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB PARTITION BY RANGE (myid)
|
|
(
|
|
PARTITION p0001 VALUES LESS THAN (50) ENGINE = INNODB,
|
|
PARTITION p0002 VALUES LESS THAN (150) ENGINE = INNODB,
|
|
PARTITION p0003 VALUES LESS THAN (1050) ENGINE = INNODB,
|
|
PARTITION p0004 VALUES LESS THAN (10050) ENGINE = INNODB
|
|
);
|
|
CREATE TABLE t2
|
|
(
|
|
myid INT(11) NOT NULL,
|
|
myval VARCHAR(10),
|
|
PRIMARY KEY (myid)
|
|
) ENGINE=INNODB DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir';
|
|
insert into t1 values (1, 'one');
|
|
insert into t2 values (2, 'two'), (3, 'threee'), (4, 'four');
|
|
select * from t1;
|
|
myid myval
|
|
1 one
|
|
ALTER TABLE t1 EXCHANGE PARTITION p0001 WITH TABLE t2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
PARTITION BY RANGE (`myid`)
|
|
(PARTITION `p0001` VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = InnoDB,
|
|
PARTITION `p0002` VALUES LESS THAN (150) ENGINE = InnoDB,
|
|
PARTITION `p0003` VALUES LESS THAN (1050) ENGINE = InnoDB,
|
|
PARTITION `p0004` VALUES LESS THAN (10050) ENGINE = InnoDB)
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`myid` int(11) NOT NULL,
|
|
`myval` varchar(10) DEFAULT NULL,
|
|
PRIMARY KEY (`myid`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from t1;
|
|
myid myval
|
|
2 two
|
|
3 threee
|
|
4 four
|
|
select * from t2;
|
|
myid myval
|
|
1 one
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Cleanup
|
|
#
|
|
SET GLOBAL innodb_file_per_table=@file_per_table;
|
|
Warnings:
|
|
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
|
|
SET SESSION innodb_strict_mode=@strict_mode;
|