mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-06-30 00:18:13 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/parts/r/reorganize_partition_innodb.result
Sergei Golubchik 1a85ae444a MDEV-36050 DATA/INDEX DIRECTORY handling is inconsistent 
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
2025-04-18 09:41:23 +02:00

172 lines
7 KiB
Text
Raw Permalink Blame History

#
# MDEV-15953 Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir
#
CREATE TABLE t (
a INT NOT NULL, KEY(a)
) ENGINE=INNODB
PARTITION BY HASH (a) (
PARTITION p1 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB,
PARTITION p2 DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = INNODB
);
INSERT INTO t VALUES (1);
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE;
Warnings:
Warning 1280 Name 'pk' ignored for PRIMARY key.
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
ALTER TABLE t DROP PRIMARY KEY, ALGORITHM=COPY;
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
SET @TMP = @@GLOBAL.INNODB_FILE_PER_TABLE;
SET GLOBAL INNODB_FILE_PER_TABLE=OFF;
Warnings:
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
ALTER TABLE t ADD PRIMARY KEY pk(a), ALGORITHM=INPLACE;
Warnings:
Warning 1280 Name 'pk' ignored for PRIMARY key.
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
SET GLOBAL INNODB_FILE_PER_TABLE=@TMP;
Warnings:
Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO (
PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB,
PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB
);
Warnings:
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO (
PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/',
PARTITION p2 DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/'
);
Warnings:
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
ALTER TABLE t REORGANIZE PARTITION p1,p2 INTO (
PARTITION p1 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB,
PARTITION p2 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' ENGINE = INNODB
);
Warnings:
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`),
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY HASH (`a`)
(PARTITION `p1` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB,
PARTITION `p2` DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here' ENGINE = InnoDB)
DROP TABLE t;
SET @strict = @@innodb_strict_mode;
SET innodb_strict_mode=OFF;
CREATE TABLE t (
a INT NOT NULL, KEY(a)
) ENGINE=INNODB
PARTITION BY RANGE (a)
SUBPARTITION BY HASH (a)
SUBPARTITIONS 2
(
PARTITION p1 VALUES LESS THAN (7)
DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/'
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/'
ENGINE = INNODB,
PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_here/'
INDEX DIRECTORY = '$MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/'
ENGINE = INNODB
);
Warnings:
Note 1618 INDEX DIRECTORY option ignored
Note 1618 INDEX DIRECTORY option ignored
Note 1618 INDEX DIRECTORY option ignored
Note 1618 INDEX DIRECTORY option ignored
SET innodb_strict_mode=@strict;
ALTER TABLE t
REORGANIZE PARTITION p1,p2 INTO
(
PARTITION p1 VALUES LESS THAN (7)
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/'
INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/'
ENGINE = INNODB,
PARTITION p2 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/'
INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/'
ENGINE = INNODB
);
Warnings:
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
Warning 1982 DATA DIRECTORY option ignored for InnoDB partition
Warning 1982 INDEX DIRECTORY option ignored for InnoDB partition
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) NOT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE (`a`)
SUBPARTITION BY HASH (`a`)
SUBPARTITIONS 2
(PARTITION `p1` VALUES LESS THAN (7) DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN MAXVALUE DATA DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_somewhere_else/' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/tmp/partitions_here/' ENGINE = InnoDB)
DROP TABLE t;
Reference in a new issue View git blame Copy permalink