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