1# 2# This test shows DISCARD/IMPORT of a remote tablespace. 3# 4SET default_storage_engine=InnoDB; 5SET GLOBAL innodb_file_per_table=ON; 6DROP TABLE IF EXISTS t5980; 7# 8# CREATE TABLE ... DATA DIRECTORY 9# combined with WL#5522 - Transportable Tablespace 10# Create the tablespace in MYSQL_TMP_DIR/alt_dir 11# InnoDB will create the sub-directories if needed. 12# Test that DISCARD and IMPORT work correctly. 13# 14CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 15INSERT INTO t5980 VALUES (1, "Create the tablespace"); 16SELECT * FROM t5980; 17a b 181 Create the tablespace 19### files in MYSQLD_DATADIR/test 20t5980.frm 21t5980.isl 22### files in MYSQL_TMP_DIR/alt_dir/test 23t5980.ibd 24# 25# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results. 26# 27SHOW CREATE TABLE t5980; 28Table Create Table 29t5980 CREATE TABLE `t5980` ( 30 `a` int(11) NOT NULL, 31 `b` text, 32 PRIMARY KEY (`a`) 33) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 34# 35# Backup the cfg and ibd files. 36# 37FLUSH TABLES t5980 FOR EXPORT; 38SELECT * FROM t5980; 39a b 401 Create the tablespace 41UNLOCK TABLES; 42### files in MYSQLD_DATADIR/test 43t5980.frm 44t5980.isl 45### files in MYSQL_TMP_DIR/alt_dir/test 46t5980.cfg.bak 47t5980.ibd 48t5980.ibd.bak 49# 50# Do some DDL and DML. 51# 52INSERT INTO t5980 VALUES (2,'Remote table has been FLUSHed and UNLOCKed'); 53START TRANSACTION; 54INSERT INTO t5980 VALUES (12,'Transactional record inserted'); 55COMMIT; 56START TRANSACTION; 57INSERT INTO t5980 VALUES (13,'Rollback this transactional record'); 58ROLLBACK; 59SELECT COUNT(*) FROM t5980; 60COUNT(*) 613 62SELECT * FROM t5980; 63a b 641 Create the tablespace 652 Remote table has been FLUSHed and UNLOCKed 6612 Transactional record inserted 67ALTER TABLE t5980 DROP PRIMARY KEY; 68ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL; 69INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added'); 70SELECT * FROM t5980; 71a b c 721 Create the tablespace NULL 732 Remote table has been FLUSHed and UNLOCKed NULL 7412 Transactional record inserted NULL 752 Duplicate value since primary key has been dropped third column added 76# 77# Make a second backup of the cfg and ibd files. 78# 79FLUSH TABLES t5980 FOR EXPORT; 80SELECT * FROM t5980; 81a b c 821 Create the tablespace NULL 832 Remote table has been FLUSHed and UNLOCKed NULL 8412 Transactional record inserted NULL 852 Duplicate value since primary key has been dropped third column added 86UNLOCK TABLES; 87### files in MYSQLD_DATADIR/test 88t5980.frm 89t5980.isl 90### files in MYSQL_TMP_DIR/alt_dir/test 91t5980.cfg.bak 92t5980.cfg.bak2 93t5980.ibd 94t5980.ibd.bak 95t5980.ibd.bak2 96# 97# DROP the table and make sure all files except the backups are gone. 98# 99DROP TABLE t5980; 100### files in MYSQLD_DATADIR/test 101### files in MYSQL_TMP_DIR/alt_dir/test 102t5980.cfg.bak 103t5980.cfg.bak2 104t5980.ibd.bak 105t5980.ibd.bak2 106# 107# CREATE the table again. 108# 109CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 110INSERT INTO t5980 VALUES (1, "Create the tablespace a second time"); 111SELECT * FROM t5980; 112a b 1131 Create the tablespace a second time 114# 115# DISCARD existing tablespace so backed-up .ibd which can be imported/restored 116# 117ALTER TABLE t5980 DISCARD TABLESPACE; 118SELECT * FROM t5980; 119ERROR HY000: Tablespace has been discarded for table 't5980' 120### files in MYSQLD_DATADIR/test 121t5980.frm 122### files in MYSQL_TMP_DIR/alt_dir/test 123t5980.cfg.bak 124t5980.cfg.bak2 125t5980.ibd.bak 126t5980.ibd.bak2 127# 128# Restore the second backup of cfg and ibd files. 129# 130"### files in MYSQL_TMP_DIR/alt_dir/test" 131t5980.cfg 132t5980.cfg.bak 133t5980.cfg.bak2 134t5980.ibd 135t5980.ibd.bak 136t5980.ibd.bak2 137# 138# Try to Import the second backup. These backups have extra DDL and 139# do not match the current frm file. 140# 141ALTER TABLE t5980 IMPORT TABLESPACE; 142ERROR HY000: Schema mismatch (Number of columns don't match, table has 5 columns but the tablespace meta-data file has 6 columns) 143CHECK TABLE t5980; 144Table Op Msg_type Msg_text 145test.t5980 check Error Tablespace has been discarded for table 't5980' 146test.t5980 check error Corrupt 147### files in MYSQL_TMP_DIR/alt_dir/test 148t5980.cfg.bak 149t5980.cfg.bak2 150t5980.ibd.bak 151t5980.ibd.bak2 152# 153# Restore the first backup of cfg and ibd files. 154# 155### files in MYSQL_TMP_DIR/alt_dir/test 156t5980.cfg 157t5980.cfg.bak 158t5980.cfg.bak2 159t5980.ibd 160t5980.ibd.bak 161t5980.ibd.bak2 162# 163# Import the tablespace and do some DDL and DML. 164# 165ALTER TABLE t5980 IMPORT TABLESPACE; 166Warnings: 167Warning 1814 InnoDB: Tablespace has been discarded for table 't5980' 168### files in MYSQLD_DATADIR/test 169t5980.frm 170t5980.isl 171### files in MYSQL_TMP_DIR/alt_dir/test 172t5980.cfg 173t5980.cfg.bak 174t5980.cfg.bak2 175t5980.ibd 176t5980.ibd.bak 177t5980.ibd.bak2 178CHECK TABLE t5980; 179Table Op Msg_type Msg_text 180test.t5980 check status OK 181SELECT COUNT(*) FROM t5980; 182COUNT(*) 1831 184SELECT * FROM t5980; 185a b 1861 Create the tablespace 187INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT'); 188SELECT * FROM t5980; 189a b 1901 Create the tablespace 1912 Inserted record after IMPORT 192START TRANSACTION; 193INSERT INTO t5980 VALUES (12,'Transactional record inserted'); 194COMMIT; 195START TRANSACTION; 196INSERT INTO t5980 VALUES (13,'Rollback this transactional record'); 197ROLLBACK; 198SELECT * FROM t5980; 199a b 2001 Create the tablespace 2012 Inserted record after IMPORT 20212 Transactional record inserted 203ALTER TABLE t5980 DROP PRIMARY KEY; 204ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL; 205INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added'); 206SELECT * FROM t5980; 207a b c 2081 Create the tablespace NULL 2092 Inserted record after IMPORT NULL 21012 Transactional record inserted NULL 2112 Duplicate value since primary key has been dropped third column added 212# 213# Show that the system tables have this table in them correctly. 214# 215=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 216Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 217test/t5980 test/t5980 97 6 Dynamic 0 Single 218=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 219Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 220test/t5980 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 221=== information_schema.files === 222Space_Name File_Type Engine Status Tablespace_Name Path 223test/t5980 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 224# 225# Drop the imported table and show that the system tables are updated. 226# 227DROP TABLE t5980; 228=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 229Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 230=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 231Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 232=== information_schema.files === 233Space_Name File_Type Engine Status Tablespace_Name Path 234### files in MYSQLD_DATADIR/test 235### files in MYSQL_TMP_DIR/alt_dir/test 236t5980.cfg.bak 237t5980.cfg.bak2 238t5980.ibd.bak 239t5980.ibd.bak2 240# 241# CREATE the table a third time. 242# 243CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 244INSERT INTO t5980 VALUES (1, "Create the tablespace a third time"); 245SELECT * FROM t5980; 246a b 2471 Create the tablespace a third time 248### files in MYSQLD_DATADIR/test 249t5980.frm 250t5980.isl 251### files in MYSQL_TMP_DIR/alt_dir/test 252t5980.cfg.bak 253t5980.cfg.bak2 254t5980.ibd 255t5980.ibd.bak 256t5980.ibd.bak2 257# 258# Restart the server 259# This test makes sure that you can still execute the FLUSH TABLES command 260# after restarting the server and the tablespace can still be found. 261# 262# restart 263SET GLOBAL innodb_file_per_table=ON; 264### files in MYSQLD_DATADIR/test 265t5980.frm 266t5980.isl 267### files in MYSQL_TMP_DIR/alt_dir/test 268t5980.cfg.bak 269t5980.cfg.bak2 270t5980.ibd 271t5980.ibd.bak 272t5980.ibd.bak2 273SELECT * FROM t5980; 274a b 2751 Create the tablespace a third time 276FLUSH TABLES t5980 FOR EXPORT; 277SELECT * FROM t5980; 278a b 2791 Create the tablespace a third time 280UNLOCK TABLES; 281# 282# Restart the server again. This test makes sure that you can 283# still DISCARD a remote table after restarting the server. 284# 285# restart 286SET GLOBAL innodb_file_per_table=ON; 287SELECT * FROM t5980; 288a b 2891 Create the tablespace a third time 290### files in MYSQLD_DATADIR/test 291t5980.frm 292t5980.isl 293### files in MYSQL_TMP_DIR/alt_dir/test 294t5980.cfg.bak 295t5980.cfg.bak2 296t5980.ibd 297t5980.ibd.bak 298t5980.ibd.bak2 299ALTER TABLE t5980 DISCARD TABLESPACE; 300SELECT * FROM t5980; 301ERROR HY000: Tablespace has been discarded for table 't5980' 302### files in MYSQLD_DATADIR/test 303t5980.frm 304### files in MYSQL_TMP_DIR/alt_dir/test 305t5980.cfg.bak 306t5980.cfg.bak2 307t5980.ibd.bak 308t5980.ibd.bak2 309# 310# Restore the backup of *.ibd and *.cfg files 311# 312### files in MYSQLD_DATADIR/test 313t5980.frm 314### files in MYSQL_TMP_DIR/alt_dir/test 315t5980.cfg 316t5980.cfg.bak 317t5980.cfg.bak2 318t5980.ibd 319t5980.ibd.bak 320t5980.ibd.bak2 321# 322# Import the tablespace and check it out. 323# 324ALTER TABLE t5980 IMPORT TABLESPACE; 325SELECT * FROM t5980; 326a b 3271 Create the tablespace 328SHOW CREATE TABLE t5980; 329Table Create Table 330t5980 CREATE TABLE `t5980` ( 331 `a` int(11) NOT NULL, 332 `b` text, 333 PRIMARY KEY (`a`) 334) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 335### files in MYSQLD_DATADIR/test 336t5980.frm 337t5980.isl 338### files in MYSQL_TMP_DIR/alt_dir/test 339t5980.cfg 340t5980.cfg.bak 341t5980.cfg.bak2 342t5980.ibd 343t5980.ibd.bak 344t5980.ibd.bak2 345# 346# DISCARD the tablespace again 347# 348ALTER TABLE t5980 DISCARD TABLESPACE; 349SELECT * FROM t5980; 350ERROR HY000: Tablespace has been discarded for table 't5980' 351### files in MYSQLD_DATADIR/test 352t5980.frm 353### files in MYSQL_TMP_DIR/alt_dir/test 354t5980.cfg.bak 355t5980.cfg.bak2 356t5980.ibd.bak 357t5980.ibd.bak2 358# 359# Restart the engine while the tablespace is in the discarded state 360# 361# restart 362SET GLOBAL innodb_file_per_table=ON; 363SELECT * FROM t5980; 364ERROR HY000: Tablespace has been discarded for table 't5980' 365CHECK TABLE t5980; 366Table Op Msg_type Msg_text 367test.t5980 check Error Tablespace has been discarded for table 't5980' 368test.t5980 check error Corrupt 369# 370# Relocate this discarded file to the default directory 371# instead of the remote directory it was discarded from. 372# Put cfg and idb files into the default directory. 373# Delete the isl file and the remote cfg file. 374# Restart the engine again. 375# The tablespace is still in the discarded state. 376# 377### files in MYSQLD_DATADIR/test 378t5980.cfg 379t5980.frm 380t5980.ibd 381### files in MYSQL_TMP_DIR/alt_dir/test 382t5980.cfg.bak 383t5980.cfg.bak2 384t5980.ibd.bak 385t5980.ibd.bak2 386# Restarting ... 387# restart 388SET GLOBAL innodb_file_per_table=ON; 389SELECT * FROM t5980; 390ERROR HY000: Tablespace has been discarded for table 't5980' 391CHECK TABLE t5980; 392Table Op Msg_type Msg_text 393test.t5980 check Error Tablespace has been discarded for table 't5980' 394test.t5980 check error Corrupt 395# 396# Try to import the tablespace. It can only be imported from 397# the location it was discarded from. 398# The error message for 1810 (IO_READ_ERROR) refers to a local path 399# so do not display it. 400# 401ALTER TABLE t5980 IMPORT TABLESPACE; 402SELECT * FROM t5980; 403ERROR HY000: Tablespace has been discarded for table 't5980' 404CHECK TABLE t5980; 405Table Op Msg_type Msg_text 406test.t5980 check Error Tablespace has been discarded for table 't5980' 407test.t5980 check error Corrupt 408# 409# Restore the ibd and cfg files to the remote directory. 410# Delete the ibd and cfg files from the default directory. 411# The isl file is missing, but is no longer needed since the 412# remote location is in the data dictionary. 413# Import the tablespace and check it out. 414# 415### files in MYSQLD_DATADIR/test 416t5980.frm 417### files in MYSQL_TMP_DIR/alt_dir/test 418t5980.cfg 419t5980.cfg.bak 420t5980.cfg.bak2 421t5980.ibd 422t5980.ibd.bak 423t5980.ibd.bak2 424ALTER TABLE t5980 IMPORT TABLESPACE; 425Warnings: 426Warning 1814 InnoDB: Tablespace has been discarded for table 't5980' 427INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT"); 428SELECT * FROM t5980; 429a b 4301 Create the tablespace 4312 Insert this record after IMPORT 432SHOW CREATE TABLE t5980; 433Table Create Table 434t5980 CREATE TABLE `t5980` ( 435 `a` int(11) NOT NULL, 436 `b` text, 437 PRIMARY KEY (`a`) 438) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 439# 440# Show that the system tables have this table in them correctly. 441# 442=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 443Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 444test/t5980 test/t5980 97 5 Dynamic 0 Single 445=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 446Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 447test/t5980 Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 448=== information_schema.files === 449Space_Name File_Type Engine Status Tablespace_Name Path 450test/t5980 TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 451DROP TABLE t5980; 452# 453# Create a local and remote tablespaces, discard two and make 454# the other two missing upon restart, and try some DDL and DML 455# on these discarded and missing tablespaces. 456# 457SET GLOBAL innodb_file_per_table=ON; 458CREATE TABLE t5980a (a int, b text) engine=InnoDB; 459CREATE TABLE t5980b (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 460CREATE TABLE t5980c (a int, b text) engine=InnoDB; 461CREATE TABLE t5980d (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 462INSERT INTO t5980a VALUES (1, "Default location, discarded."); 463INSERT INTO t5980b VALUES (1, "Remote location, discarded"); 464INSERT INTO t5980c VALUES (1, "Default location, missing"); 465INSERT INTO t5980d VALUES (1, "Remote location, missing"); 466SELECT * FROM t5980a; 467a b 4681 Default location, discarded. 469SELECT * FROM t5980b; 470a b 4711 Remote location, discarded 472SELECT * FROM t5980c; 473a b 4741 Default location, missing 475SELECT * FROM t5980d; 476a b 4771 Remote location, missing 478SHOW CREATE TABLE t5980a; 479Table Create Table 480t5980a CREATE TABLE `t5980a` ( 481 `a` int(11) DEFAULT NULL, 482 `b` text 483) ENGINE=InnoDB DEFAULT CHARSET=latin1 484SHOW CREATE TABLE t5980b; 485Table Create Table 486t5980b CREATE TABLE `t5980b` ( 487 `a` int(11) DEFAULT NULL, 488 `b` text 489) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 490SHOW CREATE TABLE t5980c; 491Table Create Table 492t5980c CREATE TABLE `t5980c` ( 493 `a` int(11) DEFAULT NULL, 494 `b` text 495) ENGINE=InnoDB DEFAULT CHARSET=latin1 496SHOW CREATE TABLE t5980d; 497Table Create Table 498t5980d CREATE TABLE `t5980d` ( 499 `a` int(11) DEFAULT NULL, 500 `b` text 501) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 502=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 503Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 504test/t5980a test/t5980a 33 5 Dynamic 0 Single 505test/t5980b test/t5980b 97 5 Dynamic 0 Single 506test/t5980c test/t5980c 33 5 Dynamic 0 Single 507test/t5980d test/t5980d 97 5 Dynamic 0 Single 508=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 509Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 510test/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd 511test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd 512test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd 513test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 514=== information_schema.files === 515Space_Name File_Type Engine Status Tablespace_Name Path 516test/t5980a TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t5980a.ibd 517test/t5980b TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd 518test/t5980c TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQLD_DATADIR/test/t5980c.ibd 519test/t5980d TABLESPACE InnoDB NORMAL innodb_file_per_table.## MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 520### files in MYSQLD_DATADIR/test 521t5980a.frm 522t5980a.ibd 523t5980b.frm 524t5980b.isl 525t5980c.frm 526t5980c.ibd 527t5980d.frm 528t5980d.isl 529### files in MYSQL_TMP_DIR/alt_dir/test 530t5980b.ibd 531t5980d.ibd 532# 533# Shutdown the server, remove two tablespaces, restart server. 534# 535# restart 536FLUSH TABLES t5980a, t5980b FOR EXPORT; 537UNLOCK TABLES; 538ALTER TABLE t5980a DISCARD TABLESPACE; 539ALTER TABLE t5980b DISCARD TABLESPACE; 540### files in MYSQLD_DATADIR/test 541t5980a.frm 542t5980b.frm 543t5980c.frm 544t5980d.frm 545### files in MYSQL_TMP_DIR/alt_dir/test 546SELECT * FROM t5980a; 547ERROR HY000: Tablespace has been discarded for table 't5980a' 548SELECT * FROM t5980b; 549ERROR HY000: Tablespace has been discarded for table 't5980b' 550SELECT * FROM t5980c; 551ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 552SELECT * FROM t5980d; 553ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 554SHOW CREATE TABLE t5980a; 555Table Create Table 556t5980a CREATE TABLE `t5980a` ( 557 `a` int(11) DEFAULT NULL, 558 `b` text 559) ENGINE=InnoDB DEFAULT CHARSET=latin1 560SHOW CREATE TABLE t5980b; 561Table Create Table 562t5980b CREATE TABLE `t5980b` ( 563 `a` int(11) DEFAULT NULL, 564 `b` text 565) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 566SHOW CREATE TABLE t5980c; 567ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 568SHOW CREATE TABLE t5980d; 569ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 570=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 571Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 572test/t5980a test/t5980a 33 5 Dynamic 0 Single 573test/t5980b test/t5980b 97 5 Dynamic 0 Single 574test/t5980c test/t5980c 33 5 Dynamic 0 Single 575test/t5980d test/t5980d 97 5 Dynamic 0 Single 576=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 577Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 578test/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd 579test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd 580test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd 581test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 582=== information_schema.files === 583Space_Name File_Type Engine Status Tablespace_Name Path 584### files in MYSQLD_DATADIR/test 585t5980a.frm 586t5980b.frm 587t5980c.frm 588t5980d.frm 589### files in MYSQL_TMP_DIR/alt_dir/test 590# 591# Discarded and missing tablespaces cannot be TRUNCATED 592# 593TRUNCATE TABLE t5980a; 594ERROR HY000: Tablespace has been discarded for table 't5980a' 595TRUNCATE TABLE t5980b; 596ERROR HY000: Tablespace has been discarded for table 't5980b' 597TRUNCATE TABLE t5980c; 598ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 599TRUNCATE TABLE t5980d; 600ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 601# 602# Discarded tablespaces can be RENAMED but they remain discarded 603# 604RENAME TABLE t5980a TO t5980aa; 605RENAME TABLE t5980b TO t5980bb; 606# 607# Missing tablespaces cannot be RENAMED 608# 609RENAME TABLE t5980c TO t5980cc; 610ERROR HY000: Error on rename of './test/t5980c' to './test/t5980cc' (errno: 155 - The table does not exist in engine) 611RENAME TABLE t5980d TO t5980dd; 612ERROR HY000: Error on rename of './test/t5980d' to './test/t5980dd' (errno: 155 - The table does not exist in engine) 613SELECT * FROM t5980a; 614ERROR 42S02: Table 'test.t5980a' doesn't exist 615SELECT * FROM t5980b; 616ERROR 42S02: Table 'test.t5980b' doesn't exist 617SELECT * FROM t5980aa; 618ERROR HY000: Tablespace has been discarded for table 't5980aa' 619SELECT * FROM t5980bb; 620ERROR HY000: Tablespace has been discarded for table 't5980bb' 621SELECT * FROM t5980c; 622ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 623SELECT * FROM t5980d; 624ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 625SHOW CREATE TABLE t5980aa; 626Table Create Table 627t5980aa CREATE TABLE `t5980aa` ( 628 `a` int(11) DEFAULT NULL, 629 `b` text 630) ENGINE=InnoDB DEFAULT CHARSET=latin1 631SHOW CREATE TABLE t5980bb; 632Table Create Table 633t5980bb CREATE TABLE `t5980bb` ( 634 `a` int(11) DEFAULT NULL, 635 `b` text 636) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 637SHOW CREATE TABLE t5980c; 638ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 639SHOW CREATE TABLE t5980d; 640ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 641=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 642Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 643test/t5980aa test/t5980a 33 5 Dynamic 0 Single 644test/t5980bb test/t5980b 97 5 Dynamic 0 Single 645test/t5980c test/t5980c 33 5 Dynamic 0 Single 646test/t5980d test/t5980d 97 5 Dynamic 0 Single 647=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 648Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 649test/t5980a Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980a.ibd 650test/t5980b Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd 651test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd 652test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 653=== information_schema.files === 654Space_Name File_Type Engine Status Tablespace_Name Path 655# 656# Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY. 657# 658ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY; 659ERROR HY000: Tablespace has been discarded for table 't5980aa' 660ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY; 661ERROR HY000: Tablespace has been discarded for table 't5980bb' 662# 663# Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE. 664# 665ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE; 666Warnings: 667Warning 1814 InnoDB: Tablespace has been discarded for table 't5980aa' 668ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE; 669Warnings: 670Warning 1814 InnoDB: Tablespace has been discarded for table 't5980bb' 671### files in MYSQLD_DATADIR/test 672t5980aa.frm 673t5980bb.frm 674t5980c.frm 675t5980d.frm 676### files in MYSQL_TMP_DIR/alt_dir/test 677# 678# Missing tablespaces cannot be ALTERED. 679# 680ALTER TABLE t5980c ADD PRIMARY KEY(a); 681ERROR HY000: Tablespace is missing for table `test`.`t5980c`. 682ALTER TABLE t5980d ADD PRIMARY KEY(a); 683ERROR HY000: Tablespace is missing for table `test`.`t5980d`. 684SELECT * FROM t5980aa; 685ERROR HY000: Tablespace has been discarded for table 't5980aa' 686SELECT * FROM t5980bb; 687ERROR HY000: Tablespace has been discarded for table 't5980bb' 688SHOW CREATE TABLE t5980aa; 689Table Create Table 690t5980aa CREATE TABLE `t5980aa` ( 691 `a` int(11) NOT NULL, 692 `b` text, 693 PRIMARY KEY (`a`) 694) ENGINE=InnoDB DEFAULT CHARSET=latin1 695SHOW CREATE TABLE t5980bb; 696Table Create Table 697t5980bb CREATE TABLE `t5980bb` ( 698 `a` int(11) NOT NULL, 699 `b` text, 700 PRIMARY KEY (`a`) 701) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 702=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 703Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 704test/t5980aa test/t5980aa 33 5 Dynamic 0 Single 705test/t5980bb test/t5980bb 97 5 Dynamic 0 Single 706test/t5980c test/t5980c 33 5 Dynamic 0 Single 707test/t5980d test/t5980d 97 5 Dynamic 0 Single 708=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 709Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 710test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd 711test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 712test/t5980aa Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd 713test/t5980bb Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd 714=== information_schema.files === 715Space_Name File_Type Engine Status Tablespace_Name Path 716### files in MYSQLD_DATADIR/test 717t5980aa.frm 718t5980bb.frm 719t5980c.frm 720t5980d.frm 721### files in MYSQL_TMP_DIR/alt_dir/test 722# 723# Restart the server to check if the discarded flag is persistent 724# 725# restart 726# 727# Discarded tablespaces that were ALTERED IN_PLACE are still discarded. 728# 729INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE"); 730ERROR HY000: Tablespace has been discarded for table 't5980aa' 731INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE"); 732ERROR HY000: Tablespace has been discarded for table 't5980bb' 733SELECT * FROM t5980aa; 734ERROR HY000: Tablespace has been discarded for table 't5980aa' 735SELECT * FROM t5980bb; 736ERROR HY000: Tablespace has been discarded for table 't5980bb' 737RENAME TABLE t5980aa TO t5980a; 738RENAME TABLE t5980bb TO t5980b; 739SHOW CREATE TABLE t5980a; 740Table Create Table 741t5980a CREATE TABLE `t5980a` ( 742 `a` int(11) NOT NULL, 743 `b` text, 744 PRIMARY KEY (`a`) 745) ENGINE=InnoDB DEFAULT CHARSET=latin1 746Warnings: 747Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' 748SHOW CREATE TABLE t5980b; 749Table Create Table 750t5980b CREATE TABLE `t5980b` ( 751 `a` int(11) NOT NULL, 752 `b` text, 753 PRIMARY KEY (`a`) 754) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 755Warnings: 756Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' 757=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 758Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 759test/t5980a test/t5980aa 33 5 Dynamic 0 Single 760test/t5980b test/t5980bb 97 5 Dynamic 0 Single 761test/t5980c test/t5980c 33 5 Dynamic 0 Single 762test/t5980d test/t5980d 97 5 Dynamic 0 Single 763=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 764Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 765test/t5980c Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980c.ibd 766test/t5980d Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd 767test/t5980aa Single DEFAULT 0 Dynamic MYSQLD_DATADIR/test/t5980aa.ibd 768test/t5980bb Single DEFAULT 0 Dynamic MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd 769=== information_schema.files === 770Space_Name File_Type Engine Status Tablespace_Name Path 771### files in MYSQLD_DATADIR/test 772t5980a.frm 773t5980b.frm 774t5980c.frm 775t5980d.frm 776### files in MYSQL_TMP_DIR/alt_dir/test 777# 778# Discard tablespaces again and try another ALTER TABLE ROW_FORMAT. 779# 780ALTER TABLE t5980a DISCARD TABLESPACE; 781Warnings: 782Warning 1812 InnoDB: Tablespace is missing for table test/t5980a. 783ALTER TABLE t5980b DISCARD TABLESPACE; 784Warnings: 785Warning 1812 InnoDB: Tablespace is missing for table test/t5980b. 786SELECT * FROM t5980a; 787ERROR HY000: Tablespace has been discarded for table 't5980a' 788SELECT * FROM t5980b; 789ERROR HY000: Tablespace has been discarded for table 't5980b' 790# 791# ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace. 792# 793ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY; 794ERROR HY000: Tablespace has been discarded for table 't5980a' 795ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY; 796ERROR HY000: Tablespace has been discarded for table 't5980b' 797# 798# ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace. 799# 800ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; 801Warnings: 802Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' 803ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; 804Warnings: 805Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' 806# 807# Discarded tablespaces that were ALTERED IN_PLACE are still discarded. 808# 809INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE"); 810ERROR HY000: Tablespace has been discarded for table 't5980a' 811INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE"); 812ERROR HY000: Tablespace has been discarded for table 't5980b' 813SELECT * FROM t5980a; 814ERROR HY000: Tablespace has been discarded for table 't5980a' 815SELECT * FROM t5980b; 816ERROR HY000: Tablespace has been discarded for table 't5980b' 817### files in MYSQLD_DATADIR/test 818t5980a.frm 819t5980b.frm 820t5980c.frm 821t5980d.frm 822### files in MYSQL_TMP_DIR/alt_dir/test 823# 824# Discard tablespaces again and try ALTER TABLE ADD COLUMN. 825# 826ALTER TABLE t5980a DISCARD TABLESPACE; 827Warnings: 828Warning 1812 InnoDB: Tablespace is missing for table test/t5980a. 829ALTER TABLE t5980b DISCARD TABLESPACE; 830Warnings: 831Warning 1812 InnoDB: Tablespace is missing for table test/t5980b. 832SELECT * FROM t5980a; 833ERROR HY000: Tablespace has been discarded for table 't5980a' 834SELECT * FROM t5980b; 835ERROR HY000: Tablespace has been discarded for table 't5980b' 836# 837# ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace. 838# 839ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY; 840ERROR HY000: Tablespace has been discarded for table 't5980a' 841ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY; 842ERROR HY000: Tablespace has been discarded for table 't5980b' 843# 844# ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace. 845# 846ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE; 847Warnings: 848Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' 849ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE; 850Warnings: 851Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' 852# 853# Discarded tablespaces that were ALTERED IN_PLACE are still discarded. 854# 855DELETE FROM t5980a; 856ERROR HY000: Tablespace has been discarded for table 't5980a' 857UPDATE t5980a SET c="Tablespace is DISCARDED"; 858ERROR HY000: Tablespace has been discarded for table 't5980a' 859INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column"); 860ERROR HY000: Tablespace has been discarded for table 't5980a' 861INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column"); 862ERROR HY000: Tablespace has been discarded for table 't5980b' 863SELECT * FROM t5980a; 864ERROR HY000: Tablespace has been discarded for table 't5980a' 865SELECT * FROM t5980b; 866ERROR HY000: Tablespace has been discarded for table 't5980b' 867### files in MYSQLD_DATADIR/test 868t5980a.frm 869t5980b.frm 870t5980c.frm 871t5980d.frm 872### files in MYSQL_TMP_DIR/alt_dir/test 873DROP TABLE t5980a; 874DROP TABLE t5980b; 875DROP TABLE t5980c; 876DROP TABLE t5980d; 877=== information_schema.innodb_sys_tables and innodb_sys_tablespaces === 878Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type 879=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === 880Space_Name Space_Type Page_Size Zip_Size Formats_Permitted Path 881=== information_schema.files === 882Space_Name File_Type Engine Status Tablespace_Name Path 883### files in MYSQLD_DATADIR/test 884### files in MYSQL_TMP_DIR/alt_dir/test 885# 886# Cleanup 887# 888