Debian Codegraph @

mysql-test/suite/parts/r/partition_basic_symlink_innodb.result

raw
1 #
2 # Verify that the DATA/INDEX DIR is stored and used if ALTER to MyISAM.
3 #
4 DROP TABLE IF EXISTS t1;
5 SET SESSION innodb_strict_mode = ON;
6 #
7 # InnoDB only supports DATA DIRECTORY with innodb_file_per_table=ON
8 #
9 SET GLOBAL innodb_file_per_table = OFF;
10 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
11 PARTITION BY HASH (c1) (
12 PARTITION p0
13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'
14 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir',
15 PARTITION p1
16 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'
17 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'
18 );
19 ERROR HY000: Table storage engine for 't1' doesn't have this option
20 SHOW WARNINGS;
21 Level Code Message
22 Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table.
23 Warning 1478 InnoDB: INDEX DIRECTORY is not supported
24 Error 1031 Table storage engine for 't1' doesn't have this option
25 #
26 # InnoDB is different from MyISAM in that instead of
27 # putting the file directly into the DATA DIRECTORY,
28 # it adds a folder under it with the name of the database.
29 # Since strict mode is off, InnoDB ignores the INDEX DIRECTORY
30 # and it is no longer part of the definition.
31 #
32 SET SESSION innodb_strict_mode = OFF;
33 SET GLOBAL innodb_file_per_table = ON;
34 CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
35 PARTITION BY HASH (c1)
36 (PARTITION p0
37 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'
38 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir',
39 PARTITION p1
40 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'
41 INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'
42 );
43 Warnings:
44 Warning 1618 <INDEX DIRECTORY> option ignored
45 Warning 1618 <INDEX DIRECTORY> option ignored
46 SHOW WARNINGS;
47 Level Code Message
48 Warning 1618 <INDEX DIRECTORY> option ignored
49 Warning 1618 <INDEX DIRECTORY> option ignored
50 # Also create a partitioned table with DATA DIRECTORY clasue at
51 # the table level.
52 CREATE TABLE t2 (c1 int) ENGINE = InnoDB
53 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'
54 PARTITION BY HASH (c1)
55 (PARTITION p0,
56 PARTITION p1
57 );
58 # Verifying .ibd files
59 ---- MYSQLD_DATADIR/test
60 ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
61 t1#p#p0.ibd
62 t1#p#p1.ibd
63 t2#p#p0.ibd
64 t2#p#p1.ibd
65 # The ibd tablespaces should not be directly under the DATA DIRECTORY
66 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
67 test
68 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
69 FLUSH TABLES;
70 SHOW CREATE TABLE t1;
71 Table Create Table
72 t1 CREATE TABLE `t1` (
73 `c1` int DEFAULT NULL
74 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
75 /*!50100 PARTITION BY HASH (`c1`)
76 (PARTITION p0 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir/' ENGINE = InnoDB,
77 PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir/' ENGINE = InnoDB) */
78 SHOW CREATE TABLE t2;
79 Table Create Table
80 t2 CREATE TABLE `t2` (
81 `c1` int DEFAULT NULL
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
83 /*!50100 PARTITION BY HASH (`c1`)
84 (PARTITION p0 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir/' ENGINE = InnoDB,
85 PARTITION p1 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir/' ENGINE = InnoDB) */
86 #
87 # Verify how the DATA/INDEX DIRECTORY is stored and used if we
88 # ALTER TABLE to MyISAM.
89 #
90 ALTER TABLE t1 engine=MyISAM REMOVE PARTITIONING;
91 SHOW CREATE TABLE t1;
92 Table Create Table
93 t1 CREATE TABLE `t1` (
94 `c1` int DEFAULT NULL
95 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
96 ALTER TABLE t2 engine=MyISAM REMOVE PARTITIONING;
97 SHOW CREATE TABLE t2;
98 Table Create Table
99 t2 CREATE TABLE `t2` (
100 `c1` int DEFAULT NULL
101 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
102 # Verifying .par and MyISAM files (.MYD, MYI)
103 ---- MYSQLD_DATADIR/test
104 t1.MYD
105 t1.MYI
106 t1_XXX.sdi
107 t2.MYD
108 t2.MYI
109 t2_XXX.sdi
110 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
111 test
112 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
113 #
114 # Now verify how the DATA DIRECTORY is used again if we
115 # ALTER TABLE back to InnoDB.
116 #
117 SET SESSION innodb_strict_mode = ON;
118 ALTER TABLE t1 engine=InnoDB;
119 SHOW CREATE TABLE t1;
120 Table Create Table
121 t1 CREATE TABLE `t1` (
122 `c1` int DEFAULT NULL
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
124 ALTER TABLE t2 engine=InnoDB;
125 SHOW CREATE TABLE t2;
126 Table Create Table
127 t2 CREATE TABLE `t2` (
128 `c1` int DEFAULT NULL
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
130 # Verifying InnoDB .ibd files
131 ---- MYSQLD_DATADIR/test
132 t1.ibd
133 t2.ibd
134 ---- MYSQLTEST_VARDIR/mysql-test-data-dir
135 test
136 ---- MYSQLTEST_VARDIR/mysql-test-idx-dir
137 ---- MYSQLTEST_VARDIR/mysql-test-data-dir/test
138 DROP TABLE t1;
139 DROP TABLE t2;
140 # Creating two non colliding tables test2.t1 and test.t1.
141 CREATE DATABASE test2;
142 USE test2;
143 CREATE TABLE t1 (a INT)
144 ENGINE = InnoDB
145 DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
146 PARTITION BY LIST (a) (
147 PARTITION p0 VALUES IN (0),
148 PARTITION p1 VALUES IN (1)
149 );
150 INSERT INTO t1 VALUES (0);
151 USE test;
152 CREATE TABLE t1 (a INT)
153 ENGINE = InnoDB
154 DATA DIRECTORY 'MYSQLTEST_VARDIR/tmp'
155 PARTITION BY LIST (a) (
156 PARTITION p0 VALUES IN (0),
157 PARTITION p1 VALUES IN (1)
158 );
159 INSERT INTO t1 VALUES (1);
160 ALTER TABLE test.t1 ENGINE= MyISAM REMOVE PARTITIONING;
161 # datadir/test:
162 t1.MYD
163 t1.MYI
164 t1_XXX.sdi
165 # datadir/test2:
166 ALTER TABLE test2.t1 ENGINE= MyISAM REMOVE PARTITIONING;
167 # datadir/test:
168 t1.MYD
169 t1.MYI
170 t1_XXX.sdi
171 # datadir/test2:
172 t1.MYD
173 t1.MYI
174 t1_XXX.sdi
175 FLUSH TABLES;
176 SELECT * FROM test.t1;
177 a
178 1
179 SELECT * FROM test2.t1;
180 a
181 0
182 DROP TABLE test2.t1;
183 DROP TABLE test.t1;
184 DROP DATABASE test2;
185 #
186 # Cleanup
187 #