| 1 |
# |
| 2 |
# A series of tests to show the correct behavior for |
| 3 |
# CREATE TABLESPACE and associated SQL statements. |
| 4 |
# |
| 5 |
|
| 6 |
--echo # |
| 7 |
--echo # CREATE TABLESPACE related tests. |
| 8 |
--echo # |
| 9 |
|
| 10 |
SET DEFAULT_STORAGE_ENGINE=InnoDB; |
| 11 |
SET NAMES utf8; |
| 12 |
LET $MYSQLD_DATADIR = `select @@datadir`; |
| 13 |
LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`; |
| 14 |
|
| 15 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 16 |
SHOW VARIABLES LIKE 'innodb_directories'; |
| 17 |
|
| 18 |
--echo # Strict-mode has no effect on CREATE TABLESPACE. |
| 19 |
--echo # It rejects all invalid input, as if strict mode is always ON. |
| 20 |
SHOW VARIABLES LIKE 'innodb_strict_mode'; |
| 21 |
|
| 22 |
--echo # The setting innodb_file_per_table=OFF will not prevent |
| 23 |
--echo # CREATE TABLESPACE from working because the tablespace is |
| 24 |
--echo # an empty shell that can contain multiple row formats. |
| 25 |
SHOW VARIABLES LIKE 'innodb_file_per_table'; |
| 26 |
|
| 27 |
--echo # |
| 28 |
--echo # Try to create a tablespace without specifying the name |
| 29 |
--echo # |
| 30 |
--error ER_PARSE_ERROR |
| 31 |
CREATE TABLESPACE; |
| 32 |
|
| 33 |
--echo # Try to create a tablespace with bad characters in the tablespace name identifier. |
| 34 |
--echo # |
| 35 |
# From; http://dev.mysql.com/doc/refman/5.7/en/identifiers.html |
| 36 |
# An identifier must be either unquoted or quoted with (`). |
| 37 |
# Identifiers are converted to Unicode internally. They may contain these characters: |
| 38 |
# Permitted characters in unquoted identifiers: |
| 39 |
# ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore) |
| 40 |
# Extended: U+0080 .. U+FFFF |
| 41 |
# Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000: |
| 42 |
# ASCII: U+0001 .. U+007F |
| 43 |
# Extended: U+0080 .. U+FFFF |
| 44 |
# ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers. |
| 45 |
# Identifiers may begin with a digit but unless quoted may not consist solely of digits. |
| 46 |
# Database, table, and column names cannot end with space characters. |
| 47 |
# The identifier quote character is the backtick (`) |
| 48 |
|
| 49 |
--error ER_PARSE_ERROR |
| 50 |
CREATE TABLESPACE 's_bad' ADD DATAFILE 's_bad.ibd'; |
| 51 |
--error ER_PARSE_ERROR |
| 52 |
CREATE TABLESPACE "s_bad" ADD DATAFILE 's_bad.ibd'; |
| 53 |
--error ER_WRONG_TABLESPACE_NAME |
| 54 |
CREATE TABLESPACE `` ADD DATAFILE 's_bad.ibd'; |
| 55 |
--error ER_PARSE_ERROR |
| 56 |
CREATE TABLESPACE s@bad ADD DATAFILE 's_bad.ibd'; |
| 57 |
--error ER_PARSE_ERROR |
| 58 |
CREATE TABLESPACE s-bad ADD DATAFILE 's_bad.ibd'; |
| 59 |
--error ER_PARSE_ERROR |
| 60 |
CREATE TABLESPACE test/s_bad ADD DATAFILE 's_bad.ibd'; |
| 61 |
--error ER_WRONG_TABLESPACE_NAME |
| 62 |
CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; |
| 63 |
LET $TWOFIFTYFIVE=../xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; |
| 64 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 65 |
--error ER_WRONG_FILE_NAME |
| 66 |
eval CREATE TABLESPACE `s_too_long_file_name` ADD DATAFILE '$TWOFIFTYFIVE.ibd'; |
| 67 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 68 |
SHOW WARNINGS; |
| 69 |
--error ER_FILEGROUP_OPTION_ONLY_ONCE |
| 70 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=1k FILE_BLOCK_SIZE=2k; |
| 71 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 72 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=3k; |
| 73 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 74 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' FILE_BLOCK_SIZE=65k; |
| 75 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 76 |
--error ER_WRONG_FILE_NAME |
| 77 |
CREATE TABLESPACE `s_bad` ADD DATAFILE '../sub/dir/////s_bad.ibd'; |
| 78 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 79 |
SHOW WARNINGS; |
| 80 |
# Show that those directories were not created. |
| 81 |
--mkdir $MYSQLD_DATADIR/sub |
| 82 |
--mkdir $MYSQLD_DATADIR/sub/dir |
| 83 |
--rmdir $MYSQLD_DATADIR/sub/dir |
| 84 |
--rmdir $MYSQLD_DATADIR/sub |
| 85 |
|
| 86 |
--error ER_PARSE_ERROR |
| 87 |
DROP TABLESPACE 's_bad'; |
| 88 |
--error ER_PARSE_ERROR |
| 89 |
DROP TABLESPACE "s_bad"; |
| 90 |
--error ER_WRONG_TABLESPACE_NAME |
| 91 |
DROP TABLESPACE ``; |
| 92 |
SHOW WARNINGS; |
| 93 |
# "DROP TABLESPACE s#bad" is the same as "DROP TABLESPACE s". |
| 94 |
# Unlike "CREATE TABLESPACE s", this is valid syntax, so we |
| 95 |
# don't get parse error here. |
| 96 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 97 |
DROP TABLESPACE s#bad; |
| 98 |
--error ER_PARSE_ERROR |
| 99 |
DROP TABLESPACE s@bad; |
| 100 |
--error ER_PARSE_ERROR |
| 101 |
DROP TABLESPACE s-bad; |
| 102 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 103 |
DROP TABLESPACE `test/s_bad`; |
| 104 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 105 |
DROP TABLESPACE s_does_not_exist; |
| 106 |
|
| 107 |
|
| 108 |
--echo # |
| 109 |
--echo # InnoDB does not allow General tablespace names with '/' |
| 110 |
--echo # |
| 111 |
--error ER_WRONG_TABLESPACE_NAME |
| 112 |
CREATE TABLESPACE `test/s_bad` ADD DATAFILE 's_bad.ibd'; |
| 113 |
SHOW WARNINGS; |
| 114 |
|
| 115 |
# When identifiers are quoted, most anything is allowed. |
| 116 |
# Table names cannot have a trailing space, but tablespaces can. |
| 117 |
CREATE TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` ADD DATAFILE 's_utf8.ibd'; |
| 118 |
CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.` (a int, b text) TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `; |
| 119 |
INSERT INTO `t !@#$%^&*()_+-={}[]|\?<>,.` VALUES(1,'one'); |
| 120 |
SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; |
| 121 |
CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2` ( |
| 122 |
`a` int(11) DEFAULT NULL, |
| 123 |
`b` text |
| 124 |
) /*!50100 TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. ` */ ENGINE=InnoDB DEFAULT CHARSET=latin1; |
| 125 |
SHOW CREATE TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; |
| 126 |
DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.2`; |
| 127 |
|
| 128 |
CREATE TABLESPACE `#sql_1` ADD DATAFILE '#sql_1.ibd'; |
| 129 |
CREATE TABLE `#sql_1` (a int, b text) TABLESPACE `#sql_1`; |
| 130 |
INSERT INTO `#sql_1` VALUES(1,'one'); |
| 131 |
SHOW CREATE TABLE `#sql_1`; |
| 132 |
|
| 133 |
CREATE TABLESPACE `s_Cöŀumň` ADD DATAFILE 's_utf8_a.ibd'; |
| 134 |
CREATE TABLE `t_utf8_1` (a int, b text) TABLESPACE `s_Cöŀumň`; |
| 135 |
SHOW CREATE TABLE `t_utf8_1`; |
| 136 |
|
| 137 |
CREATE TABLESPACE `s_cöĿǖmň` ADD DATAFILE 's_utf8_b.ibd'; |
| 138 |
CREATE TABLE `t_utf8_2` (a int, b text) TABLESPACE `s_cöĿǖmň`; |
| 139 |
SHOW CREATE TABLE `t_utf8_2`; |
| 140 |
|
| 141 |
--error ER_INVALID_CHARACTER_STRING |
| 142 |
CREATE TABLESPACE `s_😲` ADD DATAFILE 's_utf8_c.ibd'; |
| 143 |
--error ER_INVALID_CHARACTER_STRING |
| 144 |
CREATE TABLESPACE `s_𐌀𐌁𐌂𐌃𐌄𐌅𐌆𐌇𐌈𐌉𐌊𐌋𐌌𐌍𐌎𐌏𐌐𐌑𐌒𐌓𐌔𐌕𐌖𐌗𐌘𐌙𐌚𐌛𐌜` ADD DATAFILE 's_utf8_d.ibd'; |
| 145 |
|
| 146 |
CREATE TABLESPACE `s_வணக்கம்` ADD DATAFILE 'ஆவணம்.ibd'; |
| 147 |
CREATE TABLE `t_utf8_3` (a int, b text) TABLESPACE `s_வணக்கம்`; |
| 148 |
SHOW CREATE TABLE `t_utf8_3`; |
| 149 |
|
| 150 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 151 |
--source suite/innodb/include/show_i_s_tables.inc |
| 152 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 153 |
--list_files $MYSQLD_DATADIR *.ibd |
| 154 |
|
| 155 |
CHECK TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; |
| 156 |
DROP TABLE `t !@#$%^&*()_+-={}[]|\?<>,.`; |
| 157 |
DROP TABLESPACE `s_ !@#$%^&*()_+-={}[]|\?<>,. `; |
| 158 |
CHECK TABLE `#sql_1`; |
| 159 |
DROP TABLE `#sql_1`; |
| 160 |
DROP TABLESPACE `#sql_1`; |
| 161 |
DROP TABLE `t_utf8_1`; |
| 162 |
DROP TABLESPACE `s_Cöŀumň`; |
| 163 |
DROP TABLE `t_utf8_2` ; |
| 164 |
DROP TABLESPACE `s_cöĿǖmň` ; |
| 165 |
DROP TABLE `t_utf8_3`; |
| 166 |
DROP TABLESPACE `s_வணக்கம்`; |
| 167 |
|
| 168 |
--echo # |
| 169 |
--echo # Try to create a tablespace with the reserved case-sensitive prefix 'innodb_' |
| 170 |
--echo # |
| 171 |
--error ER_WRONG_TABLESPACE_NAME |
| 172 |
CREATE TABLESPACE `innodb_system` ADD DATAFILE 's_bad.ibd'; |
| 173 |
--error ER_WRONG_TABLESPACE_NAME |
| 174 |
DROP TABLESPACE `innodb_system`; |
| 175 |
SHOW WARNINGS; |
| 176 |
# Possible, but not wise! |
| 177 |
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 's_InnoDB_System.ibd'; |
| 178 |
DROP TABLESPACE `InnoDB_System`; |
| 179 |
--error ER_TABLESPACE_DUP_FILENAME |
| 180 |
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1'; |
| 181 |
SHOW WARNINGS; |
| 182 |
# Possible, but not wise! |
| 183 |
CREATE TABLESPACE `InnoDB_System` ADD DATAFILE 'ibdata1.ibd'; |
| 184 |
DROP TABLESPACE `InnoDB_System`; |
| 185 |
|
| 186 |
--error ER_WRONG_TABLESPACE_NAME |
| 187 |
CREATE TABLESPACE `innodb_temporary` ADD DATAFILE 's_bad.ibd'; |
| 188 |
SHOW WARNINGS; |
| 189 |
--error ER_WRONG_TABLESPACE_NAME |
| 190 |
DROP TABLESPACE `innodb_temporary`; |
| 191 |
# Possible, but not wise! |
| 192 |
CREATE TABLESPACE `InnoDB_Temporary` ADD DATAFILE 's_InnoDB_Temporary.ibd'; |
| 193 |
DROP TABLESPACE `InnoDB_Temporary`; |
| 194 |
|
| 195 |
--error ER_WRONG_TABLESPACE_NAME |
| 196 |
CREATE TABLESPACE `innodb_custom` ADD DATAFILE 's_bad.ibd'; |
| 197 |
SHOW WARNINGS; |
| 198 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 199 |
DROP TABLESPACE `innodb_custom`; |
| 200 |
CREATE TABLESPACE `InnoDB_Custom` ADD DATAFILE 's_InnoDB_Custom.ibd'; |
| 201 |
DROP TABLESPACE `InnoDB_Custom`; |
| 202 |
CREATE TABLESPACE `INNODB_CUSTOM` ADD DATAFILE 's_INNODB_CUSTOM.ibd'; |
| 203 |
DROP TABLESPACE `INNODB_CUSTOM`; |
| 204 |
|
| 205 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 206 |
DROP TABLESPACE `ib_logfile0`; |
| 207 |
CREATE TABLESPACE `ib_logfile0` ADD DATAFILE 'ib_logfile0.ibd'; |
| 208 |
DROP TABLESPACE `ib_logfile0`; |
| 209 |
|
| 210 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 211 |
DROP TABLESPACE `ib_logfile1`; |
| 212 |
CREATE TABLESPACE `ib_logfile1` ADD DATAFILE 'ib_logfile1.ibd'; |
| 213 |
DROP TABLESPACE `ib_logfile1`; |
| 214 |
|
| 215 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 216 |
DROP TABLESPACE `ibdata1`; |
| 217 |
CREATE TABLESPACE `ibdata1` ADD DATAFILE 'ibdata1.ibd'; |
| 218 |
DROP TABLESPACE `ibdata1`; |
| 219 |
|
| 220 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 221 |
DROP TABLESPACE `undo_001`; |
| 222 |
CREATE TABLESPACE `undo_001` ADD DATAFILE 'undo_001.ibd'; |
| 223 |
DROP TABLESPACE `undo_001`; |
| 224 |
|
| 225 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 226 |
DROP TABLESPACE `undo_002`; |
| 227 |
CREATE TABLESPACE `undo_002` ADD DATAFILE 'undo_002.ibd'; |
| 228 |
DROP TABLESPACE `undo_002`; |
| 229 |
|
| 230 |
--echo # |
| 231 |
--echo # Test various forms of ADD DATAFILE |
| 232 |
--echo # |
| 233 |
|
| 234 |
--mkdir $MYSQL_TMP_DIR/tablespace.ibd |
| 235 |
--rmdir $MYSQL_TMP_DIR/tablespace.ibd |
| 236 |
--mkdir $MYSQL_TMP_DIR/tablespace.ibd |
| 237 |
--mkdir $MYSQL_TMP_DIR/s2_#_dir |
| 238 |
--mkdir $MYSQL_TMP_DIR/test |
| 239 |
|
| 240 |
CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd' ENGINE=InnoDB; |
| 241 |
CREATE TABLESPACE `s1_#_hash` ADD DATAFILE 's1_#_hash.ibd'; |
| 242 |
|
| 243 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 244 |
eval CREATE TABLESPACE s1_remote ADD DATAFILE '$MYSQL_TMP_DIR/s1.ibd.ibd'; |
| 245 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 246 |
eval CREATE TABLESPACE s2_remote ADD DATAFILE '$MYSQL_TMP_DIR/s2_#_dir/s2.ibd'; |
| 247 |
|
| 248 |
--error ER_WRONG_FILE_NAME |
| 249 |
CREATE TABLESPACE s_bad ADD DATAFILE '.ibd'; |
| 250 |
SHOW WARNINGS; |
| 251 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 252 |
--error ER_WRONG_FILE_NAME |
| 253 |
CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_subdir/.ibd'; |
| 254 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 255 |
SHOW WARNINGS; |
| 256 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 257 |
--error ER_WRONG_FILE_NAME |
| 258 |
CREATE TABLESPACE s_bad ADD DATAFILE '../s_dir/s_bad.ibs'; |
| 259 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 260 |
SHOW WARNINGS; |
| 261 |
|
| 262 |
# A colon is always rejected on Linux, but it could be part of a valid absolute path on Windows. |
| 263 |
# These should be rejected even on Windows. |
| 264 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 265 |
--error ER_WRONG_FILE_NAME |
| 266 |
CREATE TABLESPACE s_bad ADD DATAFILE 'c:s_bad.ibd'; |
| 267 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 268 |
SHOW WARNINGS; |
| 269 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 270 |
--error ER_WRONG_FILE_NAME |
| 271 |
CREATE TABLESPACE s_bad ADD DATAFILE 'drive:s_bad.ibd'; |
| 272 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 273 |
SHOW WARNINGS; |
| 274 |
|
| 275 |
--echo # |
| 276 |
--echo # Try to create a tablespace where a same-named directory and file exist. |
| 277 |
--echo # |
| 278 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 279 |
eval CREATE TABLESPACE s4_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; |
| 280 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 281 |
--error ER_TABLESPACE_DUP_FILENAME |
| 282 |
eval CREATE TABLESPACE s5_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd/s4.ibd'; |
| 283 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 284 |
--error ER_WRONG_FILE_NAME |
| 285 |
eval CREATE TABLESPACE s6_def ADD DATAFILE '$MYSQL_TMP_DIR/tablespace.ibd'; |
| 286 |
|
| 287 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 288 |
|
| 289 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 290 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 291 |
|
| 292 |
--echo # Directory listing of MYSQL_TMP_DIR/ |
| 293 |
--list_files $MYSQL_TMP_DIR/ *.ibd |
| 294 |
|
| 295 |
--echo # Directory listing of MYSQL_TMP_DIR/tablespace.ibd/ |
| 296 |
--list_files $MYSQL_TMP_DIR/tablespace.ibd/ *.ibd |
| 297 |
|
| 298 |
--echo # Directory listing of MYSQL_TMP_DIR/s2_#_dir/ |
| 299 |
--list_files $MYSQL_TMP_DIR/s2_#_dir/ *.ibd |
| 300 |
|
| 301 |
DROP TABLESPACE s4_def; |
| 302 |
DROP TABLESPACE s1_remote; |
| 303 |
DROP TABLESPACE s2_remote; |
| 304 |
DROP TABLESPACE `s1_#_hash`; |
| 305 |
|
| 306 |
--echo # |
| 307 |
--echo # Try to create a tablespace that already exists. |
| 308 |
--echo # Make sure that the tablespace name is compared in a CASE SENSITIVE way. |
| 309 |
--echo # |
| 310 |
--error ER_TABLESPACE_EXISTS |
| 311 |
CREATE TABLESPACE s_def ADD DATAFILE 's_def.ibd'; |
| 312 |
--error ER_TABLESPACE_EXISTS |
| 313 |
CREATE TABLESPACE s_def ADD DATAFILE 's_def_2.ibd'; |
| 314 |
--error ER_TABLESPACE_DUP_FILENAME |
| 315 |
CREATE TABLESPACE S_new ADD DATAFILE 's_def.ibd'; |
| 316 |
CREATE TABLESPACE `s_DEF` ADD DATAFILE 's_def_2.ibd'; |
| 317 |
DROP TABLESPACE `s_DEF`; |
| 318 |
|
| 319 |
--echo # |
| 320 |
--echo # Make a file-per-table tablespace name and try to use it as a General tablespace |
| 321 |
--echo # |
| 322 |
CREATE TABLE t_single (a int, b text); |
| 323 |
--source suite/innodb/include/show_i_s_tables.inc |
| 324 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 325 |
# A parse error occurs if an identifier contains a '/' unless the identifier is quoted. |
| 326 |
--error ER_PARSE_ERROR |
| 327 |
CREATE TABLE t_general (a int, b text) TABLESPACE test/t_single engine=InnoDB; |
| 328 |
--error ER_WRONG_TABLESPACE_NAME |
| 329 |
CREATE TABLE t_general (a int, b text) TABLESPACE `test/t_single` engine=InnoDB; |
| 330 |
SHOW WARNINGS; |
| 331 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 332 |
CREATE TABLE t_general (a int, b text) TABLESPACE `S_Def` engine=InnoDB; |
| 333 |
SHOW WARNINGS; |
| 334 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 335 |
CREATE TABLE t_general (a int, b text) TABLESPACE `S_DEF` engine=InnoDB; |
| 336 |
SHOW WARNINGS; |
| 337 |
|
| 338 |
--error ER_WRONG_TABLESPACE_NAME |
| 339 |
CREATE TABLESPACE `test/t_single` ADD DATAFILE 's_single.ibd'; |
| 340 |
SHOW WARNINGS; |
| 341 |
--error ER_WRONG_TABLESPACE_NAME |
| 342 |
CREATE TABLESPACE `Test/t_Single` ADD DATAFILE 's_single.ibd'; |
| 343 |
SHOW WARNINGS; |
| 344 |
--error ER_WRONG_TABLESPACE_NAME |
| 345 |
CREATE TABLESPACE `TEST/T_SINGLE` ADD DATAFILE 's_single.ibd'; |
| 346 |
SHOW WARNINGS; |
| 347 |
|
| 348 |
DROP TABLE t_single; |
| 349 |
|
| 350 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 351 |
|
| 352 |
--echo # |
| 353 |
--echo # Row format is not allowed on CREATE TABLESPACE |
| 354 |
--echo # |
| 355 |
--error ER_PARSE_ERROR |
| 356 |
CREATE TABLESPACE s_red ADD DATAFILE 's_red.ibd' ROW_FORMAT=redundant; |
| 357 |
|
| 358 |
--echo # |
| 359 |
--echo # Add tables to the tablespaces. |
| 360 |
--echo # |
| 361 |
CREATE TABLE t_def_in_def (a int, b text) TABLESPACE s_def; |
| 362 |
CREATE TABLE t_red_in_def (a int, b text) ROW_FORMAT=Redundant TABLESPACE s_def; |
| 363 |
# Since these are creating tables within existing general tablespaces the value |
| 364 |
# of INNODB_FILE_PER_TABLE does not matter. |
| 365 |
SET GLOBAL innodb_file_per_table = OFF; |
| 366 |
CREATE TABLE t_dyn_in_def (a int, b text) ROW_FORMAT=Dynamic TABLESPACE s_def; |
| 367 |
|
| 368 |
--echo # Add data to the existing Tables |
| 369 |
INSERT INTO t_def_in_def VALUES (1,'a'),(2,'b'),(3,'c'); |
| 370 |
INSERT INTO t_red_in_def VALUES (1,'a'),(2,'b'),(3,'c'); |
| 371 |
INSERT INTO t_dyn_in_def VALUES (1,'a'),(2,'b'),(3,'c'); |
| 372 |
|
| 373 |
--echo # |
| 374 |
--echo # Try to drop a tablespace which is not empty |
| 375 |
--echo # |
| 376 |
--error ER_TABLESPACE_IS_NOT_EMPTY |
| 377 |
DROP TABLESPACE s_def; |
| 378 |
|
| 379 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 380 |
--source suite/innodb/include/show_i_s_tables.inc |
| 381 |
--echo # Directory of MYSQLD_DATADIR/ |
| 382 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 383 |
--echo # Directory of MYSQLD_DATADIR/test/ |
| 384 |
--list_files $MYSQLD_DATADIR/test/ *.ibd |
| 385 |
|
| 386 |
--echo # |
| 387 |
--echo # Drop the tables we no longer need. |
| 388 |
--echo # |
| 389 |
|
| 390 |
CHECK TABLE t_dyn_in_def; |
| 391 |
CHECK TABLE t_red_in_def; |
| 392 |
|
| 393 |
DROP TABLE t_dyn_in_def; |
| 394 |
DROP TABLE t_red_in_def; |
| 395 |
|
| 396 |
--echo # |
| 397 |
--echo # Try to make a table using the database name with an existing table name |
| 398 |
--echo # |
| 399 |
--error ER_PARSE_ERROR |
| 400 |
CREATE TABLE test/t_def_in_def (a int, b text) TABLESPACE s_def; |
| 401 |
# With the back-quotes, MySQL converts the tablename to `test@002ft_def_in_def`. |
| 402 |
CREATE TABLE `test/t_def_in_def` (a int, b text) TABLESPACE s_def; |
| 403 |
|
| 404 |
--source suite/innodb/include/show_i_s_tables.inc |
| 405 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 406 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 407 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 408 |
--echo # Directory listing of MYSQLD_DATADIR/test/ |
| 409 |
--list_files $MYSQLD_DATADIR/test/ *.ibd |
| 410 |
|
| 411 |
--echo # |
| 412 |
--echo # Try to create a temporary tablespace |
| 413 |
--echo # |
| 414 |
--error ER_PARSE_ERROR |
| 415 |
CREATE TEMPORARY TABLESPACE s_temp ADD DATAFILE 's_temp.ibd'; |
| 416 |
SHOW WARNINGS; |
| 417 |
|
| 418 |
--echo # |
| 419 |
--echo # Try to put a temporary table into a non-temporary tablespace |
| 420 |
--echo # |
| 421 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 422 |
CREATE TEMPORARY TABLE t_temp_red (a int, b text) ROW_FORMAT=redundant TABLESPACE s_def; |
| 423 |
SHOW WARNINGS; |
| 424 |
|
| 425 |
--echo # |
| 426 |
--echo # Try to put a compressed temporary table into the system temporary tablespace |
| 427 |
--echo # |
| 428 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 429 |
CREATE TEMPORARY TABLE t_temp_zip (a int, b text) ROW_FORMAT=compressed TABLESPACE=`innodb_temporary`; |
| 430 |
SHOW WARNINGS; |
| 431 |
|
| 432 |
--echo # |
| 433 |
--echo # Try to add a second table to a single-table tablespace |
| 434 |
--echo # |
| 435 |
SET GLOBAL innodb_file_per_table = ON; |
| 436 |
CREATE TABLE s_single (a int, b text) ROW_FORMAT=dynamic; |
| 437 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 438 |
CREATE TABLE t_second (a int, b text) TABLESPACE s_single; |
| 439 |
SHOW WARNINGS; |
| 440 |
--error ER_WRONG_TABLESPACE_NAME |
| 441 |
CREATE TABLE t_second (a int, b text) TABLESPACE=`test/s_single`; |
| 442 |
SHOW WARNINGS; |
| 443 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 444 |
DROP TABLE s_single; |
| 445 |
|
| 446 |
--echo # |
| 447 |
--echo # Try to use both TABLESPACE and DATA DIRECTORY in the same CREATE TABLE. |
| 448 |
--echo # |
| 449 |
--echo # Strict mode and innodb_file_per_table should make no difference |
| 450 |
--echo # when using general tablespaces. If TABLESPACE=innodb_file-per-table, |
| 451 |
--echo # innodb_strict_mode will apply. |
| 452 |
--echo # |
| 453 |
SET GLOBAL innodb_file_per_table = OFF; |
| 454 |
SET innodb_strict_mode = OFF; |
| 455 |
|
| 456 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 457 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 458 |
eval CREATE TABLE t3 (a int, b text) DATA DIRECTORY='$MYSQL_TMP_DIR' TABLESPACE s_def; |
| 459 |
SHOW WARNINGS; |
| 460 |
|
| 461 |
--echo # Cannot use both a general tablespace and a DATA DIRECTORY |
| 462 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 463 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 464 |
eval CREATE TABLE t4 (a int, b text) TABLESPACE=s_def DATA DIRECTORY='$MYSQL_TMP_DIR'; |
| 465 |
SHOW WARNINGS; |
| 466 |
|
| 467 |
--echo # TABLESPACE=innodb_file_per_table can be used with DATA DIRECTORY |
| 468 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 469 |
eval CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR'; |
| 470 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 471 |
SHOW CREATE TABLE t4; |
| 472 |
|
| 473 |
--echo # An altered table should retain both TABLESPACE=innodb_file_per_table and the DATA DIRECTORY |
| 474 |
ALTER TABLE t4 ROW_FORMAT=dynamic, algorithm=copy; |
| 475 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 476 |
SHOW CREATE TABLE t4; |
| 477 |
DROP TABLE t4; |
| 478 |
|
| 479 |
--echo # An empty string in DATA DIRECTORY='' indicates the current directory, |
| 480 |
--echo # which is not allowed. |
| 481 |
--error ER_WRONG_VALUE |
| 482 |
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; |
| 483 |
SHOW WARNINGS; |
| 484 |
|
| 485 |
SET GLOBAL innodb_file_per_table = ON; |
| 486 |
SET innodb_strict_mode = ON; |
| 487 |
|
| 488 |
--error ER_WRONG_VALUE |
| 489 |
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY=''; |
| 490 |
SHOW WARNINGS; |
| 491 |
--error ER_WRONG_VALUE |
| 492 |
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='.'; |
| 493 |
SHOW WARNINGS; |
| 494 |
--error ER_WRONG_VALUE |
| 495 |
CREATE TABLE t4 (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='test'; |
| 496 |
SHOW WARNINGS; |
| 497 |
|
| 498 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 499 |
--source suite/innodb/include/show_i_s_tables.inc |
| 500 |
DROP TABLE t_def_in_def; |
| 501 |
DROP TABLE `test/t_def_in_def`; |
| 502 |
|
| 503 |
--echo # |
| 504 |
--echo # Create tables explicitly in the system tablespace. |
| 505 |
--echo # |
| 506 |
CREATE TABLE t_red_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=redundant; |
| 507 |
CREATE TABLE t_com_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compact; |
| 508 |
CREATE TABLE t_dyn_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=dynamic; |
| 509 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 510 |
CREATE TABLE t_zip_in_system (a int, b text) TABLESPACE=`innodb_system` ROW_FORMAT=compressed; |
| 511 |
SHOW WARNINGS; |
| 512 |
|
| 513 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 514 |
--source suite/innodb/include/show_i_s_tables.inc |
| 515 |
CHECK TABLE t_red_in_system; |
| 516 |
CHECK TABLE t_com_in_system; |
| 517 |
CHECK TABLE t_dyn_in_system; |
| 518 |
DROP TABLE t_red_in_system; |
| 519 |
DROP TABLE t_com_in_system; |
| 520 |
DROP TABLE t_dyn_in_system; |
| 521 |
|
| 522 |
--echo # |
| 523 |
--echo # Create tables explicitly as file_per_table tablespaces. |
| 524 |
--echo # |
| 525 |
CREATE TABLE t_red_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=redundant; |
| 526 |
CREATE TABLE t_com_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=compact; |
| 527 |
CREATE TABLE t_dyn_as_file_per_table (a int, b text) TABLESPACE=innodb_file_per_table ROW_FORMAT=dynamic; |
| 528 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 529 |
eval CREATE TABLE t_def_as_remote (a int, b text) TABLESPACE=innodb_file_per_table DATA DIRECTORY='$MYSQL_TMP_DIR'; |
| 530 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 531 |
--source suite/innodb/include/show_i_s_tables.inc |
| 532 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 533 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 534 |
--echo # Directory listing of MYSQLD_DATADIR/test/ |
| 535 |
--list_files $MYSQLD_DATADIR/test/ *.ibd |
| 536 |
SHOW CREATE TABLE t_red_as_file_per_table; |
| 537 |
SHOW CREATE TABLE t_com_as_file_per_table; |
| 538 |
SHOW CREATE TABLE t_dyn_as_file_per_table; |
| 539 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 540 |
SHOW CREATE TABLE t_def_as_remote; |
| 541 |
|
| 542 |
--echo # |
| 543 |
--echo # These explicit file_per_table tables can be moved to a general tablespace. |
| 544 |
--echo # |
| 545 |
CREATE TABLESPACE s_multiple ADD DATAFILE 'multiple.ibd'; |
| 546 |
ALTER TABLE `t_red_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_red_was_file_per_table`; |
| 547 |
ALTER TABLE `t_com_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_com_was_file_per_table`; |
| 548 |
ALTER TABLE `t_dyn_as_file_per_table` TABLESPACE=`s_multiple`, RENAME TO `t_dyn_was_file_per_table`; |
| 549 |
ALTER TABLE `t_def_as_remote` TABLESPACE=`s_multiple`, RENAME TO `t_def_was_remote`; |
| 550 |
|
| 551 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 552 |
--source suite/innodb/include/show_i_s_tables.inc |
| 553 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 554 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 555 |
--echo # Directory listing of MYSQLD_DATADIR/test/ |
| 556 |
--list_files $MYSQLD_DATADIR/test/ *.ibd |
| 557 |
|
| 558 |
--echo # |
| 559 |
--echo # Tables in a general tablespace can be moved to file_per_table locations. |
| 560 |
--echo # |
| 561 |
ALTER TABLE `t_red_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_red_to_file_per_table`; |
| 562 |
ALTER TABLE `t_com_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_com_to_file_per_table`; |
| 563 |
ALTER TABLE `t_dyn_was_file_per_table` TABLESPACE=`innodb_file_per_table`, RENAME TO `t_dyn_to_file_per_table`; |
| 564 |
--echo # Note that MySQL ignores DATA DIRECTORY on all ALTER TABLE statements. |
| 565 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 566 |
eval ALTER TABLE `t_def_was_remote` TABLESPACE=`innodb_file_per_table`, DATA DIRECTORY='$MYSQL_TMP_DIR', RENAME TO `t_def_to_file_per_table`; |
| 567 |
|
| 568 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 569 |
--source suite/innodb/include/show_i_s_tables.inc |
| 570 |
--echo # Directory listing of MYSQLD_DATADIR/ |
| 571 |
--list_files $MYSQLD_DATADIR/ *.ibd |
| 572 |
--echo # Directory listing of MYSQLD_DATADIR/test/ |
| 573 |
--list_files $MYSQLD_DATADIR/test/ *.ibd |
| 574 |
|
| 575 |
DROP TABLE t_red_to_file_per_table; |
| 576 |
DROP TABLE t_com_to_file_per_table; |
| 577 |
DROP TABLE t_dyn_to_file_per_table; |
| 578 |
DROP TABLE t_def_to_file_per_table; |
| 579 |
DROP TABLESPACE s_multiple; |
| 580 |
|
| 581 |
--echo # |
| 582 |
--echo # Try the syntax that InnoDB does not support. |
| 583 |
--echo # |
| 584 |
|
| 585 |
--echo # Try NDB settings for CREATE TABLESPACE. These are currently accepted |
| 586 |
--echo # even if they probably should not be. This is valid syntax from the |
| 587 |
--echo # parser's POW |
| 588 |
|
| 589 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' |
| 590 |
USE LOGFILE GROUP logfile_group; |
| 591 |
DROP TABLESPACE s_bad; |
| 592 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' EXTENT_SIZE = 100M; |
| 593 |
DROP TABLESPACE s_bad; |
| 594 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd' NO_WAIT; |
| 595 |
DROP TABLESPACE s_bad; |
| 596 |
|
| 597 |
--echo # |
| 598 |
--echo # Checking LOGFILE GROUP commands (only supported by NDB) |
| 599 |
--echo # |
| 600 |
--error ER_PARSE_ERROR |
| 601 |
CREATE LOGFILE GROUP s_bad; |
| 602 |
--error ER_FEATURE_UNSUPPORTED |
| 603 |
CREATE LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat'; |
| 604 |
--error ER_PARSE_ERROR |
| 605 |
ALTER LOGFILE GROUP s_bad; |
| 606 |
--error ER_FEATURE_UNSUPPORTED |
| 607 |
ALTER LOGFILE GROUP s_bad ADD UNDOFILE 'undo_1.dat' |
| 608 |
INITIAL_SIZE = 100M, NO_WAIT; |
| 609 |
--error ER_FEATURE_UNSUPPORTED |
| 610 |
DROP LOGFILE GROUP s_bad; |
| 611 |
|
| 612 |
--echo # |
| 613 |
--echo # Checking ALTER TABLESPACE (only NDB in 5.7) |
| 614 |
--echo # |
| 615 |
CREATE TABLESPACE s_bad ADD DATAFILE 's_bad.ibd'; |
| 616 |
|
| 617 |
--error ER_PARSE_ERROR |
| 618 |
ALTER TABLESPACE s_bad; |
| 619 |
|
| 620 |
--error ER_ALTER_FILEGROUP_FAILED |
| 621 |
ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd'; |
| 622 |
SHOW WARNINGS; |
| 623 |
|
| 624 |
--error ER_ALTER_FILEGROUP_FAILED |
| 625 |
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd'; |
| 626 |
SHOW WARNINGS; |
| 627 |
|
| 628 |
--error ER_ALTER_FILEGROUP_FAILED |
| 629 |
ALTER TABLESPACE s_bad ADD DATAFILE 'bad2.ibd' INITIAL_SIZE = 1G; |
| 630 |
SHOW WARNINGS; |
| 631 |
|
| 632 |
--error ER_ALTER_FILEGROUP_FAILED |
| 633 |
ALTER TABLESPACE s_def ADD DATAFILE 'bad2.ibd' NO_WAIT; |
| 634 |
SHOW WARNINGS; |
| 635 |
|
| 636 |
--error ER_ALTER_FILEGROUP_FAILED |
| 637 |
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' INITIAL_SIZE = 1G; |
| 638 |
SHOW WARNINGS; |
| 639 |
|
| 640 |
--error ER_ALTER_FILEGROUP_FAILED |
| 641 |
ALTER TABLESPACE s_bad DROP DATAFILE 's_bad.ibd' NO_WAIT; |
| 642 |
SHOW WARNINGS; |
| 643 |
|
| 644 |
--echo # |
| 645 |
--echo # Undocumented ALTER TABLESPACE syntax which was removed by wl#8972 |
| 646 |
--echo # |
| 647 |
--error ER_PARSE_ERROR |
| 648 |
ALTER TABLESPACE s_bad CHANGE 's_bad.ibd' |
| 649 |
INITIAL_SIZE = 1G, AUTOEXTEND_SIZE = 100M, MAX_SIZE = 2G; |
| 650 |
--error ER_PARSE_ERROR |
| 651 |
ALTER TABLESPACE s_bad READ_ONLY; |
| 652 |
--error ER_PARSE_ERROR |
| 653 |
ALTER TABLESPACE s_bad READ_WRITE; |
| 654 |
--error ER_PARSE_ERROR |
| 655 |
ALTER TABLESPACE s_bad NOT ACCESSIBLE; |
| 656 |
|
| 657 |
DROP TABLESPACE s_bad; |
| 658 |
|
| 659 |
--echo # |
| 660 |
--echo # Try the syntax that MySQL docs say is ignored. |
| 661 |
--echo # |
| 662 |
CREATE TABLESPACE s_ignore1 ADD DATAFILE 's_ignore1.ibd' NODEGROUP = 5; |
| 663 |
CREATE TABLESPACE s_ignore2 ADD DATAFILE 's_ignore2.ibd' WAIT; |
| 664 |
CREATE TABLESPACE s_ignore3 ADD DATAFILE 's_ignore3.ibd' COMMENT = 'This comment is ignored'; |
| 665 |
|
| 666 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 667 |
|
| 668 |
DROP TABLESPACE s_ignore1; |
| 669 |
DROP TABLESPACE s_ignore2; |
| 670 |
DROP TABLESPACE s_ignore3; |
| 671 |
|
| 672 |
--echo # |
| 673 |
--echo # Try various ALTER TABLE statements. |
| 674 |
--echo # |
| 675 |
CREATE TABLE t_in_def (a serial key, b text) TABLESPACE s_def; |
| 676 |
INSERT INTO t_in_def(b) VALUES(repeat("a short string - ",5)); |
| 677 |
INSERT INTO t_in_def(b) VALUES(repeat("a long string - ",50)); |
| 678 |
INSERT INTO t_in_def(b) SELECT b FROM t_in_def; |
| 679 |
SELECT a, left(b,50) FROM t_in_def; |
| 680 |
SHOW CREATE TABLE t_in_def; |
| 681 |
--source suite/innodb/include/show_i_s_tables.inc |
| 682 |
|
| 683 |
ALTER TABLE t_in_def ROW_FORMAT=redundant; |
| 684 |
SHOW CREATE TABLE t_in_def; |
| 685 |
|
| 686 |
ALTER TABLE t_in_def ROW_FORMAT=dynamic; |
| 687 |
SHOW CREATE TABLE t_in_def; |
| 688 |
|
| 689 |
ALTER TABLE t_in_def ADD COLUMN (c int); |
| 690 |
SHOW CREATE TABLE t_in_def; |
| 691 |
|
| 692 |
ALTER TABLE t_in_def ADD INDEX c (c); |
| 693 |
SHOW CREATE TABLE t_in_def; |
| 694 |
|
| 695 |
ALTER TABLE t_in_def DROP INDEX c; |
| 696 |
SHOW CREATE TABLE t_in_def; |
| 697 |
|
| 698 |
ALTER TABLE t_in_def DROP COLUMN c; |
| 699 |
SHOW CREATE TABLE t_in_def; |
| 700 |
|
| 701 |
ALTER TABLE t_in_def AUTO_INCREMENT=100; |
| 702 |
SHOW CREATE TABLE t_in_def; |
| 703 |
INSERT INTO t_in_def(b) VALUES("after ALTER TABLE AUTO_INCREMENT"); |
| 704 |
SELECT a, left(b,50) FROM t_in_def; |
| 705 |
SHOW CREATE TABLE t_in_def; |
| 706 |
|
| 707 |
ALTER TABLE t_in_def CHANGE a aa int; |
| 708 |
SHOW CREATE TABLE t_in_def; |
| 709 |
|
| 710 |
ALTER TABLE t_in_def CHANGE b bb varchar(1000); |
| 711 |
SHOW CREATE TABLE t_in_def; |
| 712 |
|
| 713 |
ALTER TABLE t_in_def DROP PRIMARY KEY; |
| 714 |
SHOW CREATE TABLE t_in_def; |
| 715 |
|
| 716 |
ALTER TABLE t_in_def ADD PRIMARY KEY aa(aa), ALGORITHM=INPLACE; |
| 717 |
SHOW CREATE TABLE t_in_def; |
| 718 |
|
| 719 |
ALTER TABLE t_in_def ADD INDEX ab(aa,bb(25)), ALGORITHM=COPY; |
| 720 |
SHOW CREATE TABLE t_in_def; |
| 721 |
|
| 722 |
ALTER TABLE t_in_def ADD INDEX bb(bb(50)); |
| 723 |
SHOW CREATE TABLE t_in_def; |
| 724 |
|
| 725 |
RENAME TABLE t_in_def TO t; |
| 726 |
SHOW CREATE TABLE t; |
| 727 |
--source suite/innodb/include/show_i_s_tables.inc |
| 728 |
INSERT INTO t VALUES(200, "after RENAME TABLE t_in_def TO t"); |
| 729 |
SELECT aa, left(bb,50) FROM t; |
| 730 |
|
| 731 |
--echo # |
| 732 |
--echo # Move tables between tablespaces. |
| 733 |
--echo # |
| 734 |
CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd'; |
| 735 |
CREATE TABLESPACE s_alt2 ADD DATAFILE 's_alt2.ibd'; |
| 736 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 737 |
ALTER TABLE t RENAME TO t_nomad, TABLESPACE s_alt1; |
| 738 |
SHOW CREATE TABLE t_nomad; |
| 739 |
--source suite/innodb/include/show_i_s_tables.inc |
| 740 |
ALTER TABLE t_nomad TABLESPACE s_alt2; |
| 741 |
SHOW CREATE TABLE t_nomad; |
| 742 |
--source suite/innodb/include/show_i_s_tables.inc |
| 743 |
ALTER TABLE t_nomad TABLESPACE s_def, DROP COLUMN bb; |
| 744 |
SHOW CREATE TABLE t_nomad; |
| 745 |
--source suite/innodb/include/show_i_s_tables.inc |
| 746 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 747 |
DROP TABLE t_nomad; |
| 748 |
DROP TABLESPACE s_alt1; |
| 749 |
DROP TABLESPACE s_alt2; |
| 750 |
|
| 751 |
--echo # |
| 752 |
--echo # Move a table from the System Tablespace into a General Tablespace |
| 753 |
--echo # and then back out to the system tablespace again. |
| 754 |
--echo # |
| 755 |
SET GLOBAL innodb_file_per_table=OFF; |
| 756 |
CREATE TABLE t_system (a serial key, b text); |
| 757 |
INSERT INTO t_system(b) VALUES(repeat("a short string - ",5)); |
| 758 |
INSERT INTO t_system(b) VALUES(repeat("a long string - ",50)); |
| 759 |
SHOW CREATE TABLE t_system; |
| 760 |
--source suite/innodb/include/show_i_s_tables.inc |
| 761 |
|
| 762 |
ALTER TABLE t_system TABLESPACE s_def; |
| 763 |
SHOW CREATE TABLE t_system; |
| 764 |
--source suite/innodb/include/show_i_s_tables.inc |
| 765 |
|
| 766 |
ALTER TABLE t_system TABLESPACE=`innodb_system`; |
| 767 |
SHOW CREATE TABLE t_system; |
| 768 |
--source suite/innodb/include/show_i_s_tables.inc |
| 769 |
|
| 770 |
--echo # |
| 771 |
--echo # Do a few ALTER TABLES for this table that was moved out and back into the system tablespace. |
| 772 |
--echo # |
| 773 |
ALTER TABLE t_system ROW_FORMAT=redundant; |
| 774 |
SHOW CREATE TABLE t_system; |
| 775 |
ALTER TABLE t_system ROW_FORMAT=dynamic; |
| 776 |
SHOW CREATE TABLE t_system; |
| 777 |
ALTER TABLE t_system ADD COLUMN (c int); |
| 778 |
SHOW CREATE TABLE t_system; |
| 779 |
ALTER TABLE t_system ADD INDEX c (c); |
| 780 |
SHOW CREATE TABLE t_system; |
| 781 |
ALTER TABLE t_system DROP INDEX c; |
| 782 |
SHOW CREATE TABLE t_system; |
| 783 |
ALTER TABLE t_system DROP COLUMN c; |
| 784 |
SHOW CREATE TABLE t_system; |
| 785 |
|
| 786 |
--echo # |
| 787 |
--echo # Now try to put that table into its own Single-Table tablespace. |
| 788 |
--echo # It is not allowed since the table is now marked with the system tablespace |
| 789 |
--echo # |
| 790 |
SET GLOBAL innodb_file_per_table=ON; |
| 791 |
ALTER TABLE t_system RENAME TO t_still_system; |
| 792 |
SHOW CREATE TABLE t_still_system; |
| 793 |
--source suite/innodb/include/show_i_s_tables.inc |
| 794 |
DROP TABLE t_still_system; |
| 795 |
|
| 796 |
--echo # |
| 797 |
--echo # Try to create or move a table into the temporary tablespace. |
| 798 |
--echo # |
| 799 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 800 |
CREATE TABLE t_not_temp (a int, b text) TABLESPACE=`innodb_temporary`; |
| 801 |
SHOW WARNINGS; |
| 802 |
|
| 803 |
CREATE TABLE t_not_temp (a int, b text) TABLESPACE s_def; |
| 804 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 805 |
ALTER TABLE t_not_temp TABLESPACE=`innodb_temporary`; |
| 806 |
SHOW WARNINGS; |
| 807 |
DROP TABLE t_not_temp; |
| 808 |
|
| 809 |
SET SESSION innodb_strict_mode=OFF; |
| 810 |
CREATE TEMPORARY TABLE t_my_temp (a int, b text) TABLESPACE=`innodb_temporary`; |
| 811 |
SHOW CREATE TABLE t_my_temp; |
| 812 |
DROP TABLE t_my_temp; |
| 813 |
SET SESSION innodb_strict_mode=ON; |
| 814 |
|
| 815 |
--echo # |
| 816 |
--echo # Try to create or move a table into the redo tablespace |
| 817 |
--echo # or any tablespace using the reserved `innodb_` prefix. |
| 818 |
--echo # |
| 819 |
--error ER_WRONG_TABLESPACE_NAME |
| 820 |
CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_redo_log`; |
| 821 |
SHOW WARNINGS; |
| 822 |
--error ER_WRONG_TABLESPACE_NAME |
| 823 |
CREATE TABLE t_data (a int, b text) TABLESPACE=`innodb_anything`; |
| 824 |
SHOW WARNINGS; |
| 825 |
|
| 826 |
CREATE TABLE t_data (a int, b text) TABLESPACE s_def; |
| 827 |
--error ER_WRONG_TABLESPACE_NAME |
| 828 |
ALTER TABLE t_data TABLESPACE=`innodb_redo_log`; |
| 829 |
SHOW WARNINGS; |
| 830 |
--error ER_WRONG_TABLESPACE_NAME |
| 831 |
ALTER TABLE t_data TABLESPACE=`innodb_anything`; |
| 832 |
SHOW WARNINGS; |
| 833 |
DROP TABLE t_data; |
| 834 |
|
| 835 |
--echo # |
| 836 |
--echo # Move a table from a Single-Table tablespace into a General Tablespace. |
| 837 |
--echo # |
| 838 |
CREATE TABLE t_single (a serial key, b text); |
| 839 |
INSERT INTO t_single(b) VALUES(repeat("a short string - ",5)); |
| 840 |
INSERT INTO t_single(b) VALUES(repeat("a long string - ",50)); |
| 841 |
SHOW CREATE TABLE t_single; |
| 842 |
--source suite/innodb/include/show_i_s_tables.inc |
| 843 |
ALTER TABLE t_single TABLESPACE s_def; |
| 844 |
SHOW CREATE TABLE t_single; |
| 845 |
--source suite/innodb/include/show_i_s_tables.inc |
| 846 |
DROP TABLE t_single; |
| 847 |
|
| 848 |
--echo # |
| 849 |
--echo # Move a MyISAM table into an InnoDB General Tablespace and back. |
| 850 |
--echo # |
| 851 |
CREATE TABLE t_myisam (a serial key, b int) ENGINE=MyISAM; |
| 852 |
INSERT INTO t_myisam(b) VALUES(11); |
| 853 |
SHOW CREATE TABLE t_myisam; |
| 854 |
ALTER TABLE t_myisam RENAME TO t_innodb, TABLESPACE s_def, ENGINE=InnoDB; |
| 855 |
INSERT INTO t_innodb(b) VALUES(22); |
| 856 |
SHOW CREATE TABLE t_innodb; |
| 857 |
--source suite/innodb/include/show_i_s_tables.inc |
| 858 |
ALTER TABLE t_innodb RENAME TO t_myisam, ENGINE=MyISAM; |
| 859 |
INSERT INTO t_myisam(b) VALUES(33); |
| 860 |
SELECT * FROM t_myisam; |
| 861 |
SHOW CREATE TABLE t_myisam; |
| 862 |
ALTER TABLE t_myisam RENAME TO t_innodb, ENGINE=InnoDB; |
| 863 |
INSERT INTO t_innodb(b) VALUES(44); |
| 864 |
SHOW CREATE TABLE t_innodb; |
| 865 |
--source suite/innodb/include/show_i_s_tables.inc |
| 866 |
DROP TABLE t_innodb; |
| 867 |
|
| 868 |
--echo # |
| 869 |
--echo # Move a Dynamic table from an InnoDB General tablespace to MyISAM |
| 870 |
--echo # and Memory and back without having to re-specify the tablespace |
| 871 |
--echo # and row_format. |
| 872 |
--echo # |
| 873 |
CREATE TABLE t_dyn_in_s_def (a serial key, b int) ROW_FORMAT=Dynamic TABLESPACE=s_def ENGINE=InnoDB; |
| 874 |
INSERT INTO t_dyn_in_s_def(b) VALUES(11); |
| 875 |
SHOW CREATE TABLE t_dyn_in_s_def; |
| 876 |
--source suite/innodb/include/show_i_s_tables.inc |
| 877 |
ALTER TABLE t_dyn_in_s_def ENGINE=MyISAM; |
| 878 |
INSERT INTO t_dyn_in_s_def(b) VALUES(22); |
| 879 |
SHOW CREATE TABLE t_dyn_in_s_def; |
| 880 |
--source suite/innodb/include/show_i_s_tables.inc |
| 881 |
ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB; |
| 882 |
INSERT INTO t_dyn_in_s_def(b) VALUES(33); |
| 883 |
SHOW CREATE TABLE t_dyn_in_s_def; |
| 884 |
--source suite/innodb/include/show_i_s_tables.inc |
| 885 |
ALTER TABLE t_dyn_in_s_def ENGINE=Memory; |
| 886 |
INSERT INTO t_dyn_in_s_def(b) VALUES(44); |
| 887 |
SHOW CREATE TABLE t_dyn_in_s_def; |
| 888 |
--source suite/innodb/include/show_i_s_tables.inc |
| 889 |
ALTER TABLE t_dyn_in_s_def ENGINE=InnoDB; |
| 890 |
INSERT INTO t_dyn_in_s_def(b) VALUES(55); |
| 891 |
SELECT * FROM t_dyn_in_s_def; |
| 892 |
SHOW CREATE TABLE t_dyn_in_s_def; |
| 893 |
--source suite/innodb/include/show_i_s_tables.inc |
| 894 |
CHECK TABLE t_dyn_in_s_def; |
| 895 |
DROP TABLE t_dyn_in_s_def; |
| 896 |
|
| 897 |
--echo # |
| 898 |
--echo # If a tablespace name is associated with a table in another SE, and that tablespace |
| 899 |
--echo # does not exist in InnoDB, the ALTER TABLE will fail |
| 900 |
--echo # |
| 901 |
CREATE TABLESPACE s_short_life ADD DATAFILE 'short_life.ibd'; |
| 902 |
CREATE TABLE t_nomad (a serial key, b int) TABLESPACE=s_short_life ENGINE=InnoDB; |
| 903 |
INSERT INTO t_nomad(b) VALUES(11); |
| 904 |
SHOW CREATE TABLE t_nomad; |
| 905 |
--source suite/innodb/include/show_i_s_tables.inc |
| 906 |
ALTER TABLE t_nomad ENGINE=MyISAM; |
| 907 |
INSERT INTO t_nomad(b) VALUES(22); |
| 908 |
SHOW CREATE TABLE t_nomad; |
| 909 |
--source suite/innodb/include/show_i_s_tables.inc |
| 910 |
DROP TABLESPACE s_short_life; |
| 911 |
--error ER_TABLESPACE_MISSING_WITH_NAME |
| 912 |
ALTER TABLE t_nomad ENGINE=InnoDB; |
| 913 |
CREATE TABLESPACE s_shorter_life ADD DATAFILE 'shorter_life.ibd'; |
| 914 |
ALTER TABLE t_nomad ENGINE=InnoDB TABLESPACE=s_shorter_life; |
| 915 |
SHOW CREATE TABLE t_nomad; |
| 916 |
--source suite/innodb/include/show_i_s_tables.inc |
| 917 |
DROP TABLE t_nomad; |
| 918 |
DROP TABLESPACE s_shorter_life; |
| 919 |
|
| 920 |
--echo # |
| 921 |
--echo # Show that a table with the same SQL name can be created in two different |
| 922 |
--echo # databases, whether they are in the system tablespace, a general tablespace |
| 923 |
--echo # or their own file-per-table tablespace. |
| 924 |
--echo # Also show that DROP DATABASE will remove tables from inside a General Tablespace |
| 925 |
--echo # without removing the tablespace or other tables in it for another databases. |
| 926 |
--echo # |
| 927 |
SET GLOBAL innodb_file_per_table=OFF; |
| 928 |
CREATE TABLE t_system (a serial key, b text); |
| 929 |
CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def; |
| 930 |
CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`; |
| 931 |
SHOW TABLES; |
| 932 |
|
| 933 |
CREATE DATABASE test1; |
| 934 |
USE test1; |
| 935 |
# A general tablespace is independent of the current database. |
| 936 |
CREATE TABLESPACE s_empty1 ADD DATAFILE 's_empty1.ibd' ENGINE InnoDB; |
| 937 |
CREATE TABLE t_system (a serial key, b text); |
| 938 |
CREATE TABLE t_general (a serial key, b text) TABLESPACE s_def; |
| 939 |
CREATE TABLE t_single (a serial key, b text) TABLESPACE=`innodb_file_per_table`; |
| 940 |
SHOW TABLES; |
| 941 |
|
| 942 |
USE test; |
| 943 |
--source suite/innodb/include/show_i_s_tables.inc |
| 944 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 945 |
DROP DATABASE test1; |
| 946 |
--source suite/innodb/include/show_i_s_tables.inc |
| 947 |
--source suite/innodb/include/show_i_s_tablespaces.inc |
| 948 |
DROP TABLE t_system; |
| 949 |
DROP TABLE t_general; |
| 950 |
DROP TABLE t_single; |
| 951 |
DROP TABLESPACE s_empty1; |
| 952 |
|
| 953 |
--echo # |
| 954 |
--echo # Assign different tablespace for PK-FK tables |
| 955 |
--echo # |
| 956 |
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; |
| 957 |
CREATE TABLE t1 (a int primary key) ENGINE=innodb TABLESPACE=s1; |
| 958 |
CREATE TABLE t2 (b int , foreign key (b) references t1(a)) ENGINE=innodb ; |
| 959 |
INSERT INTO t1 VALUES (100); |
| 960 |
INSERT INTO t1 VALUES (200); |
| 961 |
INSERT INTO t2 VALUES (100); |
| 962 |
--source suite/innodb/include/show_i_s_tables.inc |
| 963 |
SELECT * FROM t1; |
| 964 |
SELECT * FROM t2; |
| 965 |
SELECT * FROM t1,t2 WHERE t1.a=t2.b; |
| 966 |
DROP TABLE t2,t1; |
| 967 |
DROP TABLESPACE s1; |
| 968 |
|
| 969 |
--echo # |
| 970 |
--echo # Assigning general tablespace from procedure |
| 971 |
--echo # |
| 972 |
USE test; |
| 973 |
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; |
| 974 |
CREATE TABLE t1 (a geometry NOT NULL SRID 0, SPATIAL (a)) ENGINE=InnoDB; |
| 975 |
INSERT INTO t1 VALUES (st_GeomFromText("LINESTRING(100 100, 200 200, 300 300)")) ; |
| 976 |
DELIMITER |; |
| 977 |
CREATE PROCEDURE p1() |
| 978 |
BEGIN |
| 979 |
ALTER TABLE t1 TABLESPACE=s1; |
| 980 |
END| |
| 981 |
DELIMITER ;| |
| 982 |
--source suite/innodb/include/show_i_s_tables.inc |
| 983 |
CALL p1(); |
| 984 |
--source suite/innodb/include/show_i_s_tables.inc |
| 985 |
DROP PROCEDURE p1; |
| 986 |
|
| 987 |
--echo # |
| 988 |
--echo # Show that DISCARD/IMPORT tablespace does not work on a general tablespace. |
| 989 |
--echo # |
| 990 |
--error ER_NOT_ALLOWED_COMMAND |
| 991 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 992 |
SHOW WARNINGS; |
| 993 |
--error ER_NOT_ALLOWED_COMMAND |
| 994 |
ALTER TABLE t1 IMPORT TABLESPACE; |
| 995 |
SHOW WARNINGS; |
| 996 |
DROP TABLE t1; |
| 997 |
DROP TABLESPACE s1; |
| 998 |
|
| 999 |
--echo # |
| 1000 |
--echo # Clean-up. |
| 1001 |
--echo # |
| 1002 |
|
| 1003 |
DROP TABLESPACE s_def; |
| 1004 |
|
| 1005 |
SET GLOBAL innodb_file_per_table=default; |
| 1006 |
SET GLOBAL innodb_strict_mode=default; |
| 1007 |
|
| 1008 |
--rmdir $MYSQL_TMP_DIR/s2_#_dir |
| 1009 |
--rmdir $MYSQL_TMP_DIR/test |
| 1010 |
--rmdir $MYSQL_TMP_DIR/tablespace.ibd |
| 1011 |
|
| 1012 |
--echo # |
| 1013 |
--echo # Bug #20763179 SEGV WHEN CREATE TABLESPACE IS EXECUTED IN READ ONLY MODE |
| 1014 |
--echo # |
| 1015 |
--echo # Check CREATE TABLESPACE is handled properly in read-only mode |
| 1016 |
|
| 1017 |
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd' ENGINE InnoDB; |
| 1018 |
|
| 1019 |
let $do_not_echo_parameters = 1; |
| 1020 |
let $extra_restart_parameters = --innodb-directories=$MYSQL_TMP_DIR; |
| 1021 |
--source include/restart_innodb_read_only.inc |
| 1022 |
let $do_not_echo_parameters =; |
| 1023 |
--error ER_READ_ONLY_MODE |
| 1024 |
CREATE TABLESPACE s2 ADD DATAFILE 's2.ibd' ENGINE InnoDB; |
| 1025 |
SHOW WARNINGS; |
| 1026 |
--error ER_INNODB_READ_ONLY |
| 1027 |
DROP TABLESPACE s1; |
| 1028 |
SHOW WARNINGS; |
| 1029 |
|
| 1030 |
--echo # Restart in normal mode for cleanup. |
| 1031 |
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR |
| 1032 |
let $restart_parameters = restart: --innodb-directories=$MYSQL_TMP_DIR; |
| 1033 |
--source include/restart_mysqld.inc |
| 1034 |
DROP TABLESPACE s1; |
| 1035 |
|
| 1036 |
--echo # |
| 1037 |
--echo # Bug#26199233 create like ignores innodb_file_per_table |
| 1038 |
--echo # |
| 1039 |
--echo # - Verify if table picks correct tablespace based on |
| 1040 |
--echo # innodb_file_per_table settings. |
| 1041 |
--echo # - Check if SHOW CREATE displays correct tablespace name. |
| 1042 |
|
| 1043 |
CREATE DATABASE db1; |
| 1044 |
|
| 1045 |
SET @@global.innodb_file_per_table = OFF; |
| 1046 |
|
| 1047 |
--echo # Case 1 |
| 1048 |
--echo # SHOW CREATE should not show TABLESPACE clause after the fix. |
| 1049 |
--echo # Because it was not explicitly specified by user. |
| 1050 |
CREATE TABLE db1.t1 (c1 INT) ENGINE=InnoDB; |
| 1051 |
SHOW CREATE TABLE db1.t1; |
| 1052 |
|
| 1053 |
SET @@global.innodb_file_per_table = ON; |
| 1054 |
|
| 1055 |
--echo # Case 2 |
| 1056 |
--echo # After the fix table, t2 should use innodb_file_per_table tablespace. |
| 1057 |
CREATE TABLE db1.t2 LIKE db1.t1; |
| 1058 |
SHOW CREATE TABLE db1.t2; |
| 1059 |
--echo # Verify the same by listing the .ibd file. |
| 1060 |
--list_files $MYSQLD_DATADIR/db1/ |
| 1061 |
|
| 1062 |
--echo # Case 3 |
| 1063 |
--echo # db1.tables TABLESPACE clause should show 'ts0' after the fix. |
| 1064 |
CREATE TABLESPACE ts0 ADD DATAFILE 'df0.ibd'; |
| 1065 |
CREATE TABLE db1.tables (c1 INT) ENGINE=InnoDB tablespace=ts0; |
| 1066 |
SHOW CREATE TABLE db1.tables; |
| 1067 |
DROP TABLE db1.tables; |
| 1068 |
DROP TABLESPACE ts0; |
| 1069 |
|
| 1070 |
--echo # Cleanup |
| 1071 |
DROP DATABASE db1; |
| 1072 |
|
| 1073 |
--echo # Additional test cases for Bug#26199233 to |
| 1074 |
--echo # test various combinations. |
| 1075 |
|
| 1076 |
CREATE SCHEMA s1; |
| 1077 |
USE s1; |
| 1078 |
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd'; |
| 1079 |
|
| 1080 |
--echo # |
| 1081 |
--echo # Tables with explicitly assigned tablespaces. |
| 1082 |
--echo # |
| 1083 |
--echo # Table with an explicitly assigned user tablespace. |
| 1084 |
CREATE TABLE t_expl_usr (i INT) TABLESPACE s1; |
| 1085 |
SHOW CREATE TABLE t_expl_usr; |
| 1086 |
--echo # |
| 1087 |
--echo # Table with an explicitly assigned system tablespace. |
| 1088 |
CREATE TABLE t_expl_sys (i INT) TABLESPACE innodb_system; |
| 1089 |
SHOW CREATE TABLE t_expl_sys; |
| 1090 |
--echo # |
| 1091 |
--echo # Table with an explicitly assigned f_p_t tablespace. |
| 1092 |
CREATE TABLE t_expl_fpt (i INT) TABLESPACE innodb_file_per_table; |
| 1093 |
SHOW CREATE TABLE t_expl_fpt; |
| 1094 |
--echo # |
| 1095 |
--echo # Temporary table with an explicitly assigned tmp tablespace. |
| 1096 |
SET SESSION innodb_strict_mode=OFF; |
| 1097 |
CREATE TEMPORARY TABLE t_expl_tmp (i INT) TABLESPACE innodb_temporary; |
| 1098 |
SHOW WARNINGS; |
| 1099 |
SET SESSION innodb_strict_mode=ON; |
| 1100 |
|
| 1101 |
--echo # |
| 1102 |
--echo # Tables with implicitly assigned tablespaces. |
| 1103 |
--echo # |
| 1104 |
--echo # Table with an implicitly assigned system tablespace. |
| 1105 |
SET GLOBAL innodb_file_per_table= OFF; |
| 1106 |
CREATE TABLE t_impl_sys (i INT); |
| 1107 |
SHOW CREATE TABLE t_impl_sys; |
| 1108 |
--echo # |
| 1109 |
--echo # Table with an implicitly assigned f_p_t tablespace. |
| 1110 |
SET GLOBAL innodb_file_per_table= ON; |
| 1111 |
CREATE TABLE t_impl_fpt (i INT); |
| 1112 |
SHOW CREATE TABLE t_impl_fpt; |
| 1113 |
--echo # |
| 1114 |
--echo # Temporary table with an implicitly assigned tmp tablespace. |
| 1115 |
CREATE TEMPORARY TABLE t_impl_tmp (i INT); |
| 1116 |
SHOW CREATE TABLE t_impl_tmp; |
| 1117 |
|
| 1118 |
--echo # |
| 1119 |
--echo # CREATE LIKE when innodb_file_per_table is ON. |
| 1120 |
--echo # |
| 1121 |
--echo # Tablespace preserved for all source tables that |
| 1122 |
--echo # had an explicitly assigned tablesapce. |
| 1123 |
SET GLOBAL innodb_file_per_table= ON; |
| 1124 |
CREATE TABLE fpt_on_like_t_expl_usr LIKE t_expl_usr; |
| 1125 |
SHOW CREATE TABLE fpt_on_like_t_expl_usr; |
| 1126 |
CREATE TABLE fpt_on_like_t_expl_sys LIKE t_expl_sys; |
| 1127 |
SHOW CREATE TABLE fpt_on_like_t_expl_sys; |
| 1128 |
CREATE TABLE fpt_on_like_t_expl_fpt LIKE t_expl_fpt; |
| 1129 |
SHOW CREATE TABLE fpt_on_like_t_expl_fpt; |
| 1130 |
SET SESSION innodb_strict_mode=OFF; |
| 1131 |
CREATE TEMPORARY TABLE fpt_on_like_t_expl_tmp LIKE t_expl_tmp; |
| 1132 |
SHOW WARNINGS; |
| 1133 |
SET SESSION innodb_strict_mode=OFF; |
| 1134 |
|
| 1135 |
--echo # Tablespace not preserved for all source tables that |
| 1136 |
--echo # had an implicitly assigned tablesapce. |
| 1137 |
CREATE TABLE fpt_on_like_t_impl_sys LIKE t_impl_sys; |
| 1138 |
SHOW CREATE TABLE fpt_on_like_t_impl_sys; |
| 1139 |
CREATE TABLE fpt_on_like_t_impl_fpt LIKE t_impl_fpt; |
| 1140 |
SHOW CREATE TABLE fpt_on_like_t_impl_fpt; |
| 1141 |
CREATE TEMPORARY TABLE fpt_on_like_t_impl_tmp LIKE t_impl_tmp; |
| 1142 |
SHOW CREATE TABLE fpt_on_like_t_impl_tmp; |
| 1143 |
|
| 1144 |
--echo # |
| 1145 |
--echo # CREATE LIKE when innodb_file_per_table is OFF. |
| 1146 |
--echo # |
| 1147 |
--echo # Tablespace preserved for all source tables that |
| 1148 |
--echo # had an explicitly assigned tablesapce. |
| 1149 |
SET GLOBAL innodb_file_per_table= OFF; |
| 1150 |
CREATE TABLE fpt_off_like_t_expl_usr LIKE t_expl_usr; |
| 1151 |
SHOW CREATE TABLE fpt_off_like_t_expl_usr; |
| 1152 |
CREATE TABLE fpt_off_like_t_expl_sys LIKE t_expl_sys; |
| 1153 |
CREATE TABLE fpt_off_like_t_expl_fpt LIKE t_expl_fpt; |
| 1154 |
SHOW CREATE TABLE fpt_off_like_t_expl_fpt; |
| 1155 |
CREATE TEMPORARY TABLE fpt_off_like_t_expl_tmp LIKE t_expl_tmp; |
| 1156 |
|
| 1157 |
--echo # Tablespace not preserved for all source tables that |
| 1158 |
--echo # had an implicitly assigned tablesapce. |
| 1159 |
CREATE TABLE fpt_off_like_t_impl_sys LIKE t_impl_sys; |
| 1160 |
SHOW CREATE TABLE fpt_off_like_t_impl_sys; |
| 1161 |
CREATE TABLE fpt_off_like_t_impl_fpt LIKE t_impl_fpt; |
| 1162 |
SHOW CREATE TABLE fpt_off_like_t_impl_fpt; |
| 1163 |
CREATE TEMPORARY TABLE fpt_off_like_t_impl_tmp LIKE t_impl_tmp; |
| 1164 |
SHOW CREATE TABLE fpt_off_like_t_impl_tmp; |
| 1165 |
|
| 1166 |
--echo # The end result should be that we have .ibd files for: |
| 1167 |
--echo # fpt_off_like_t_expl_fpt.ibd |
| 1168 |
--echo # fpt_on_like_t_expl_fpt.ibd |
| 1169 |
--echo # fpt_on_like_t_impl_fpt.ibd |
| 1170 |
--echo # fpt_on_like_t_impl_sys.ibd |
| 1171 |
--echo # t_expl_fpt.ibd |
| 1172 |
--echo # t_impl_fpt.ibd |
| 1173 |
|
| 1174 |
let $MYSQLD_DATADIR = `select @@datadir`; |
| 1175 |
--list_files $MYSQLD_DATADIR/s1/ |
| 1176 |
|
| 1177 |
--echo # Now, we can ALTER the tables that have implicitly |
| 1178 |
--echo # assigned tablespaces to instead assign the spaces |
| 1179 |
--echo # explicitly. This will make SHOW CREATE show the |
| 1180 |
--echo # tablespace names. |
| 1181 |
ALTER TABLE t_impl_sys TABLESPACE innodb_system; |
| 1182 |
SHOW CREATE TABLE t_impl_sys; |
| 1183 |
ALTER TABLE t_impl_fpt TABLESPACE innodb_file_per_table; |
| 1184 |
SHOW CREATE TABLE t_impl_fpt; |
| 1185 |
ALTER TABLE t_impl_tmp TABLESPACE innodb_temporary; |
| 1186 |
SHOW CREATE TABLE t_impl_tmp; |
| 1187 |
|
| 1188 |
--echo # And CREATE TABLE LIKE will now preserve the |
| 1189 |
--echo # tablespace name of the above tables. |
| 1190 |
|
| 1191 |
CREATE TABLE after_alter_like_t_impl_sys LIKE t_impl_sys; |
| 1192 |
SHOW CREATE TABLE after_alter_like_t_impl_sys; |
| 1193 |
CREATE TABLE after_alter_like_t_impl_fpt LIKE t_impl_fpt; |
| 1194 |
SHOW CREATE TABLE after_alter_like_t_impl_fpt; |
| 1195 |
CREATE TEMPORARY TABLE after_alter_like_t_impl_tmp LIKE t_impl_tmp; |
| 1196 |
SHOW CREATE TABLE after_alter_like_t_impl_tmp; |
| 1197 |
|
| 1198 |
DROP SCHEMA s1; |
| 1199 |
DROP TABLESPACE s1; |
| 1200 |
SET GLOBAL innodb_file_per_table= DEFAULT; |
| 1201 |
|
| 1202 |
--disable_query_log |
| 1203 |
call mtr.add_suppression("\\[Warning\\].* '.*tablespace.ibd' is a directory, can't delete!"); |
| 1204 |
call mtr.add_suppression("\\[Warning\\].* Can't create test file"); |
| 1205 |
call mtr.add_suppression("\\[ERROR\\].* File .* 'delete' returned OS error .*"); |
| 1206 |
call mtr.add_suppression("\\[ERROR\\].* File .* 'stat' returned OS error .*"); |
| 1207 |
call mtr.add_suppression("\\[ERROR\\].* File .*\.ibd: 'create' returned OS error .*"); |
| 1208 |
call mtr.add_suppression("\\[ERROR\\].* The error means the system cannot find the path specified"); |
| 1209 |
call mtr.add_suppression("\\[ERROR\\].* The error means mysqld does not have the access rights to the directory. It may also be you have created a subdirectory of the same name as a data file."); |
| 1210 |
call mtr.add_suppression("\\[ERROR\\].* Operating system error number .* in a file operation"); |
| 1211 |
call mtr.add_suppression("\\[ERROR\\].* Error number .* means"); |
| 1212 |
call mtr.add_suppression("\\[ERROR\\].* Cannot create file"); |
| 1213 |
call mtr.add_suppression("\\[ERROR\\].* Invalid use of ':' in"); |
| 1214 |
call mtr.add_suppression("\\[ERROR\\].* The file .* already exists though the corresponding table did not exist."); |
| 1215 |
call mtr.add_suppression("\\[ERROR\\].* The directory for tablespace .* does not exist or is incorrect"); |
| 1216 |
call mtr.add_suppression("\\[ERROR\\].* Cannot create tablespace .* because the directory is not a valid location. The DATAFILE location must be in a known directory"); |
| 1217 |
--enable_query_log |
| 1218 |
|