1SET default_storage_engine=InnoDB; 2# 3# TABLESPACE related tests. 4# 5# 6# CREATE TABLE ... DATA DIRECTORY 7# Innodb does not support INDEX DIRECTORY. 8# 9SET SESSION innodb_strict_mode = ON; 10CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 11ERROR HY000: Table storage engine for 't1' doesn't have this option 12SHOW WARNINGS; 13Level Code Message 14Warning 1478 InnoDB: INDEX DIRECTORY is not supported 15Error 1031 Table storage engine for 't1' doesn't have this option 16# 17# Without strict mode, INDEX DIRECTORY is just ignored 18# 19SET SESSION innodb_strict_mode = OFF; 20CREATE TABLE t1 (a int KEY, b text) INDEX DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 21Warnings: 22Warning 1618 <INDEX DIRECTORY> option ignored 23SHOW WARNINGS; 24Level Code Message 25Warning 1618 <INDEX DIRECTORY> option ignored 26SHOW CREATE TABLE t1; 27Table Create Table 28t1 CREATE TABLE `t1` ( 29 `a` int(11) NOT NULL, 30 `b` text, 31 PRIMARY KEY (`a`) 32) ENGINE=InnoDB DEFAULT CHARSET=latin1 33DROP TABLE t1; 34# 35# Innodb does not support DATA DIRECTORY without innodb_file_per_table=ON. 36# 37SET SESSION innodb_strict_mode = ON; 38SET GLOBAL innodb_file_per_table=OFF; 39CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 40ERROR HY000: Table storage engine for 't1' doesn't have this option 41SHOW WARNINGS; 42Level Code Message 43Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table. 44Error 1031 Table storage engine for 't1' doesn't have this option 45# 46# Without strict mode, DATA DIRECTORY without innodb_file_per_table=ON is just ignored. 47# 48SET SESSION innodb_strict_mode = OFF; 49CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 50Warnings: 51Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table. 52Warning 1618 <DATA DIRECTORY> option ignored 53SHOW WARNINGS; 54Level Code Message 55Warning 1478 InnoDB: DATA DIRECTORY requires innodb_file_per_table. 56Warning 1618 <DATA DIRECTORY> option ignored 57SHOW CREATE TABLE t1; 58Table Create Table 59t1 CREATE TABLE `t1` ( 60 `a` int(11) NOT NULL, 61 `b` text, 62 PRIMARY KEY (`a`) 63) ENGINE=InnoDB DEFAULT CHARSET=latin1 64DROP TABLE t1; 65# Now set innodb_file_per_table so that DATA DIRECTORY can be tested. 66SET GLOBAL innodb_file_per_table=ON; 67# 68# Create the tablespace in MYSQL_TMP_DIR/alt_dir 69# InnoDB will create the sub-directories if needed. 70# 71CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 72SHOW WARNINGS; 73Level Code Message 74INSERT INTO t1 VALUES (1, "Create the tablespace"); 75SELECT * FROM t1; 76a b 771 Create the tablespace 78# 79# Check if link file exists in MYSQLD_DATADIR 80# 81---- MYSQLD_DATADIR/test 82t1.frm 83t1.isl 84# Check if tablespace file exists where we specified in DATA DIRECTORY 85---- MYSQL_TMP_DIR/alt_dir/test 86t1.ibd 87# 88# Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results. 89# 90SHOW CREATE TABLE t1; 91Table Create Table 92t1 CREATE TABLE `t1` ( 93 `a` int(11) NOT NULL, 94 `b` text, 95 PRIMARY KEY (`a`) 96) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 97# Show that the new system tables have this table in them correctly 98SELECT name,n_cols,file_format,row_format 99FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%'; 100name n_cols file_format row_format 101test/t1 5 Antelope Compact 102SELECT name,file_format,row_format 103FROM information_schema.innodb_sys_tablespaces 104ORDER BY name; 105name file_format row_format 106mysql/innodb_index_stats Antelope Compact or Redundant 107mysql/innodb_table_stats Antelope Compact or Redundant 108mysql/slave_master_info Antelope Compact or Redundant 109mysql/slave_relay_log_info Antelope Compact or Redundant 110mysql/slave_worker_info Antelope Compact or Redundant 111test/t1 Antelope Compact or Redundant 112SELECT path FROM information_schema.innodb_sys_datafiles 113WHERE path LIKE '%test%' ORDER BY space; 114path 115MYSQL_TMP_DIR/alt_dir/test/t1.ibd 116# 117# Show that the system tables are updated on drop table 118# 119DROP TABLE t1; 120SELECT name,n_cols,file_format,row_format 121FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 122 ORDER BY name; 123name n_cols file_format row_format 124SELECT name,file_format,row_format 125FROM information_schema.innodb_sys_tablespaces 126ORDER BY name; 127name file_format row_format 128mysql/innodb_index_stats Antelope Compact or Redundant 129mysql/innodb_table_stats Antelope Compact or Redundant 130mysql/slave_master_info Antelope Compact or Redundant 131mysql/slave_relay_log_info Antelope Compact or Redundant 132mysql/slave_worker_info Antelope Compact or Redundant 133SELECT path FROM information_schema.innodb_sys_datafiles 134WHERE path LIKE '%test%' ORDER BY space; 135path 136# 137# Create the same table a second time in the same place 138# 139CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 140INSERT INTO t1 VALUES (2, "Create the same table a second time in the same place"); 141SELECT * FROM t1; 142a b 1432 Create the same table a second time in the same place 144SHOW CREATE TABLE t1; 145Table Create Table 146t1 CREATE TABLE `t1` ( 147 `a` int(11) NOT NULL, 148 `b` text, 149 PRIMARY KEY (`a`) 150) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 151SELECT name,n_cols,file_format,row_format 152FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 153 ORDER BY name; 154name n_cols file_format row_format 155test/t1 5 Antelope Compact 156SELECT name,file_format,row_format 157FROM information_schema.innodb_sys_tablespaces 158ORDER BY name; 159name file_format row_format 160mysql/innodb_index_stats Antelope Compact or Redundant 161mysql/innodb_table_stats Antelope Compact or Redundant 162mysql/slave_master_info Antelope Compact or Redundant 163mysql/slave_relay_log_info Antelope Compact or Redundant 164mysql/slave_worker_info Antelope Compact or Redundant 165test/t1 Antelope Compact or Redundant 166SELECT path FROM information_schema.innodb_sys_datafiles 167WHERE path LIKE '%test%' ORDER BY space; 168path 169MYSQL_TMP_DIR/alt_dir/test/t1.ibd 170---- MYSQLD_DATADIR/test 171t1.frm 172t1.isl 173---- MYSQL_TMP_DIR/alt_dir/test 174t1.ibd 175# 176# Truncate the table, then insert and verify 177# 178TRUNCATE TABLE t1; 179INSERT INTO t1 VALUES (3, "Truncate the table, then insert"); 180SELECT * FROM t1; 181a b 1823 Truncate the table, then insert 183SELECT name,n_cols,file_format,row_format 184FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 185 ORDER BY name; 186name n_cols file_format row_format 187test/t1 5 Antelope Compact 188SELECT name,file_format,row_format 189FROM information_schema.innodb_sys_tablespaces 190ORDER BY name; 191name file_format row_format 192mysql/innodb_index_stats Antelope Compact or Redundant 193mysql/innodb_table_stats Antelope Compact or Redundant 194mysql/slave_master_info Antelope Compact or Redundant 195mysql/slave_relay_log_info Antelope Compact or Redundant 196mysql/slave_worker_info Antelope Compact or Redundant 197test/t1 Antelope Compact or Redundant 198SELECT path FROM information_schema.innodb_sys_datafiles 199WHERE path LIKE '%test%' ORDER BY space; 200path 201MYSQL_TMP_DIR/alt_dir/test/t1.ibd 202---- MYSQLD_DATADIR/test 203t1.frm 204t1.isl 205---- MYSQL_TMP_DIR/alt_dir/test 206t1.ibd 207# 208# Rename the table, then insert and verify 209# 210RENAME TABLE t1 TO t2; 211INSERT INTO t2 VALUES (4, "Rename the table, then insert"); 212SELECT * FROM t2; 213a b 2143 Truncate the table, then insert 2154 Rename the table, then insert 216SELECT name,n_cols,file_format,row_format 217FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 218 ORDER BY name; 219name n_cols file_format row_format 220test/t2 5 Antelope Compact 221SELECT name,file_format,row_format 222FROM information_schema.innodb_sys_tablespaces 223ORDER BY name; 224name file_format row_format 225mysql/innodb_index_stats Antelope Compact or Redundant 226mysql/innodb_table_stats Antelope Compact or Redundant 227mysql/slave_master_info Antelope Compact or Redundant 228mysql/slave_relay_log_info Antelope Compact or Redundant 229mysql/slave_worker_info Antelope Compact or Redundant 230test/t2 Antelope Compact or Redundant 231SELECT path FROM information_schema.innodb_sys_datafiles 232WHERE path LIKE '%test%' ORDER BY space; 233path 234MYSQL_TMP_DIR/alt_dir/test/t2.ibd 235---- MYSQLD_DATADIR/test 236t2.frm 237t2.isl 238---- MYSQL_TMP_DIR/alt_dir/test 239t2.ibd 240# 241# CREATE TABLE LIKE does not retain DATA DIRECTORY automatically. 242# 243CREATE TABLE t3 LIKE t2; 244INSERT INTO t3 VALUES (5, "CREATE TABLE LIKE"); 245SELECT * FROM t3; 246a b 2475 CREATE TABLE LIKE 248SELECT name,n_cols,file_format,row_format 249FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 250 ORDER BY name; 251name n_cols file_format row_format 252test/t2 5 Antelope Compact 253test/t3 5 Antelope Compact 254SELECT name,file_format,row_format 255FROM information_schema.innodb_sys_tablespaces 256ORDER BY name; 257name file_format row_format 258mysql/innodb_index_stats Antelope Compact or Redundant 259mysql/innodb_table_stats Antelope Compact or Redundant 260mysql/slave_master_info Antelope Compact or Redundant 261mysql/slave_relay_log_info Antelope Compact or Redundant 262mysql/slave_worker_info Antelope Compact or Redundant 263test/t2 Antelope Compact or Redundant 264test/t3 Antelope Compact or Redundant 265SELECT path FROM information_schema.innodb_sys_datafiles 266WHERE path LIKE '%test%' ORDER BY space; 267path 268MYSQL_TMP_DIR/alt_dir/test/t2.ibd 269MYSQLD_DATADIR/test/t3.ibd 270---- MYSQLD_DATADIR/test 271t2.frm 272t2.isl 273t3.frm 274t3.ibd 275# 276# Now make sure the tables can be fully dropped. 277# 278DROP TABLE t2, t3; 279SELECT name,n_cols,file_format,row_format 280FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' 281 ORDER BY name; 282name n_cols file_format row_format 283SELECT name,file_format,row_format 284FROM information_schema.innodb_sys_tablespaces 285ORDER BY name; 286name file_format row_format 287mysql/innodb_index_stats Antelope Compact or Redundant 288mysql/innodb_table_stats Antelope Compact or Redundant 289mysql/slave_master_info Antelope Compact or Redundant 290mysql/slave_relay_log_info Antelope Compact or Redundant 291mysql/slave_worker_info Antelope Compact or Redundant 292SELECT path FROM information_schema.innodb_sys_datafiles 293WHERE path LIKE '%test%' ORDER BY space; 294path 295---- MYSQLD_DATADIR/test 296---- MYSQL_TMP_DIR/alt_dir/test 297# 298# Be sure SQL MODE "NO_DIR_IN_CREATE" prevents the use of DATA DIRECTORY 299# 300SET @org_mode=@@sql_mode; 301SET @@sql_mode='NO_DIR_IN_CREATE'; 302SELECT @@sql_mode; 303@@sql_mode 304NO_DIR_IN_CREATE 305CREATE TABLE t1 (a int, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 306Warnings: 307Warning 1618 <DATA DIRECTORY> option ignored 308SHOW WARNINGS; 309Level Code Message 310Warning 1618 <DATA DIRECTORY> option ignored 311INSERT INTO t1 VALUES (6, "SQL MODE NO_DIR_IN_CREATE prevents DATA DIRECTORY"); 312DROP TABLE t1; 313set @@sql_mode=@org_mode; 314# 315# MySQL engine does not allow DATA DIRECTORY to be 316# within --datadir for any engine, including InnoDB 317# 318CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY 'MYSQLD_DATADIR/test'; 319ERROR HY000: Incorrect arguments to DATA DIRECTORY 320# TEMPORARY tables are incompatible with DATA DIRECTORY 321SET SESSION innodb_strict_mode = ON; 322CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 323ERROR HY000: Table storage engine for 't1' doesn't have this option 324SHOW WARNINGS; 325Level Code Message 326Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables. 327Error 1031 Table storage engine for 't1' doesn't have this option 328SET SESSION innodb_strict_mode = OFF; 329CREATE TEMPORARY TABLE t1 (a int KEY, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; 330Warnings: 331Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables. 332Warning 1618 <DATA DIRECTORY> option ignored 333SHOW WARNINGS; 334Level Code Message 335Warning 1478 InnoDB: DATA DIRECTORY cannot be used for TEMPORARY tables. 336Warning 1618 <DATA DIRECTORY> option ignored 337SHOW CREATE TABLE t1; 338Table Create Table 339t1 CREATE TEMPORARY TABLE `t1` ( 340 `a` int(11) NOT NULL, 341 `b` text, 342 PRIMARY KEY (`a`) 343) ENGINE=InnoDB DEFAULT CHARSET=latin1 344DROP TABLE t1; 345---- MYSQLD_DATADIR/test 346---- MYSQL_TMP_DIR/alt_dir/test 347# 348# Create the remote table via static DDL statements in a stored procedure 349# 350CREATE PROCEDURE static_proc() BEGIN CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; END | 351CALL static_proc; 352SELECT path FROM information_schema.innodb_sys_datafiles 353WHERE path LIKE '%test%' ORDER BY space; 354path 355MYSQL_TMP_DIR/alt_dir/test/t1.ibd 356INSERT INTO t1 VALUES (7, "Create the remote table via static DDL statements"); 357SELECT * FROM t1; 358a b 3597 Create the remote table via static DDL statements 360SHOW CREATE TABLE t1; 361Table Create Table 362t1 CREATE TABLE `t1` ( 363 `a` int(11) NOT NULL, 364 `b` text, 365 PRIMARY KEY (`a`) 366) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 367---- MYSQLD_DATADIR/test 368t1.frm 369t1.isl 370---- MYSQL_TMP_DIR/alt_dir/test 371t1.ibd 372DROP PROCEDURE static_proc; 373DROP TABLE t1; 374# 375# Create the remote table via dynamic DDL statements in a stored procedure 376# 377CREATE PROCEDURE dynamic_proc() BEGIN PREPARE stmt1 FROM "CREATE TABLE t1 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'"; EXECUTE stmt1; END | 378CALL dynamic_proc; 379SELECT path FROM information_schema.innodb_sys_datafiles 380WHERE path LIKE '%test%' ORDER BY space; 381path 382MYSQL_TMP_DIR/alt_dir/test/t1.ibd 383INSERT INTO t1 VALUES (8, "Create the remote table via dynamic DDL statements"); 384SELECT * FROM t1; 385a b 3868 Create the remote table via dynamic DDL statements 387SHOW CREATE TABLE t1; 388Table Create Table 389t1 CREATE TABLE `t1` ( 390 `a` int(11) NOT NULL, 391 `b` text, 392 PRIMARY KEY (`a`) 393) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' 394---- MYSQLD_DATADIR/test 395t1.frm 396t1.isl 397---- MYSQL_TMP_DIR/alt_dir/test 398t1.ibd 399DROP PROCEDURE dynamic_proc; 400DROP TABLE t1; 401# 402# CREATE, DROP, ADD and TRUNCATE PARTITION with DATA DIRECTORY 403# 404CREATE TABLE emp ( 405id INT NOT NULL, 406store_name VARCHAR(30), 407parts VARCHAR(30), 408store_id INT 409) 410PARTITION BY LIST(store_id) ( 411PARTITION east VALUES IN (10,20,30) 412DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east', 413PARTITION north VALUES IN (40,50,60) 414DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north', 415PARTITION west VALUES IN (70,80,100) 416DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' 417); 418INSERT INTO emp values(1,'Oracle','NUTT',10); 419INSERT INTO emp values(2,'HUAWEI','BOLT',40); 420INSERT INTO emp values(3,'IBM','NAIL',70); 421SHOW CREATE TABLE emp; 422Table Create Table 423emp CREATE TABLE `emp` ( 424 `id` int(11) NOT NULL, 425 `store_name` varchar(30) DEFAULT NULL, 426 `parts` varchar(30) DEFAULT NULL, 427 `store_id` int(11) DEFAULT NULL 428) ENGINE=InnoDB DEFAULT CHARSET=latin1 429/*!50100 PARTITION BY LIST (store_id) 430(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB, 431 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB, 432 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */ 433SELECT name,n_cols,file_format,row_format 434FROM information_schema.innodb_sys_tables 435WHERE name LIKE 'test%' 436 ORDER BY name; 437name n_cols file_format row_format 438test/emp#p#east 7 Antelope Compact 439test/emp#p#north 7 Antelope Compact 440test/emp#p#west 7 Antelope Compact 441SELECT name,file_format,row_format 442FROM information_schema.innodb_sys_tablespaces 443ORDER BY name; 444name file_format row_format 445mysql/innodb_index_stats Antelope Compact or Redundant 446mysql/innodb_table_stats Antelope Compact or Redundant 447mysql/slave_master_info Antelope Compact or Redundant 448mysql/slave_relay_log_info Antelope Compact or Redundant 449mysql/slave_worker_info Antelope Compact or Redundant 450test/emp#p#east Antelope Compact or Redundant 451test/emp#p#north Antelope Compact or Redundant 452test/emp#p#west Antelope Compact or Redundant 453SELECT path FROM information_schema.innodb_sys_datafiles 454WHERE path LIKE '%test%' ORDER BY space; 455path 456MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd 457MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd 458MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd 459SELECT * FROM emp; 460id store_name parts store_id 4611 Oracle NUTT 10 4622 HUAWEI BOLT 40 4633 IBM NAIL 70 464---- MYSQLD_DATADIR/test 465emp#p#east.isl 466emp#p#north.isl 467emp#p#west.isl 468emp.frm 469emp.par 470---- MYSQL_TMP_DIR/alt_dir_east/test 471emp#p#east.ibd 472---- MYSQL_TMP_DIR/alt_dir_north/test 473emp#p#north.ibd 474---- MYSQL_TMP_DIR/alt_dir_west/test 475emp#p#west.ibd 476# 477# DROP one PARTITION. 478# 479ALTER TABLE emp DROP PARTITION west; 480SHOW CREATE TABLE emp; 481Table Create Table 482emp CREATE TABLE `emp` ( 483 `id` int(11) NOT NULL, 484 `store_name` varchar(30) DEFAULT NULL, 485 `parts` varchar(30) DEFAULT NULL, 486 `store_id` int(11) DEFAULT NULL 487) ENGINE=InnoDB DEFAULT CHARSET=latin1 488/*!50100 PARTITION BY LIST (store_id) 489(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB, 490 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB) */ 491SELECT name,n_cols,file_format,row_format 492FROM information_schema.innodb_sys_tables 493WHERE name LIKE 'test%' 494 ORDER BY name; 495name n_cols file_format row_format 496test/emp#p#east 7 Antelope Compact 497test/emp#p#north 7 Antelope Compact 498SELECT name,file_format,row_format 499FROM information_schema.innodb_sys_tablespaces 500ORDER BY name; 501name file_format row_format 502mysql/innodb_index_stats Antelope Compact or Redundant 503mysql/innodb_table_stats Antelope Compact or Redundant 504mysql/slave_master_info Antelope Compact or Redundant 505mysql/slave_relay_log_info Antelope Compact or Redundant 506mysql/slave_worker_info Antelope Compact or Redundant 507test/emp#p#east Antelope Compact or Redundant 508test/emp#p#north Antelope Compact or Redundant 509SELECT path FROM information_schema.innodb_sys_datafiles 510WHERE path LIKE '%test%' ORDER BY space; 511path 512MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd 513MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd 514SELECT * FROM emp; 515id store_name parts store_id 5161 Oracle NUTT 10 5172 HUAWEI BOLT 40 518---- MYSQLD_DATADIR/test 519emp#p#east.isl 520emp#p#north.isl 521emp.frm 522emp.par 523---- MYSQL_TMP_DIR/alt_dir_east/test 524emp#p#east.ibd 525---- MYSQL_TMP_DIR/alt_dir_north/test 526emp#p#north.ibd 527---- MYSQL_TMP_DIR/alt_dir_west/test 528# 529# ADD the PARTITION back. 530# 531ALTER TABLE emp ADD PARTITION ( 532PARTITION west VALUES IN (70,80,100) 533DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west'); 534SHOW CREATE TABLE emp; 535Table Create Table 536emp CREATE TABLE `emp` ( 537 `id` int(11) NOT NULL, 538 `store_name` varchar(30) DEFAULT NULL, 539 `parts` varchar(30) DEFAULT NULL, 540 `store_id` int(11) DEFAULT NULL 541) ENGINE=InnoDB DEFAULT CHARSET=latin1 542/*!50100 PARTITION BY LIST (store_id) 543(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB, 544 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB, 545 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */ 546SELECT name,n_cols,file_format,row_format 547FROM information_schema.innodb_sys_tables 548WHERE name LIKE 'test%' 549 ORDER BY name; 550name n_cols file_format row_format 551test/emp#p#east 7 Antelope Compact 552test/emp#p#north 7 Antelope Compact 553test/emp#p#west 7 Antelope Compact 554SELECT name,file_format,row_format 555FROM information_schema.innodb_sys_tablespaces 556ORDER BY name; 557name file_format row_format 558mysql/innodb_index_stats Antelope Compact or Redundant 559mysql/innodb_table_stats Antelope Compact or Redundant 560mysql/slave_master_info Antelope Compact or Redundant 561mysql/slave_relay_log_info Antelope Compact or Redundant 562mysql/slave_worker_info Antelope Compact or Redundant 563test/emp#p#east Antelope Compact or Redundant 564test/emp#p#north Antelope Compact or Redundant 565test/emp#p#west Antelope Compact or Redundant 566SELECT path FROM information_schema.innodb_sys_datafiles 567WHERE path LIKE '%test%' ORDER BY space; 568path 569MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd 570MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd 571MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd 572INSERT INTO emp VALUES(3,'IBM','NAIL',70); 573SELECT * FROM emp; 574id store_name parts store_id 5751 Oracle NUTT 10 5762 HUAWEI BOLT 40 5773 IBM NAIL 70 578---- MYSQLD_DATADIR/test 579emp#p#east.isl 580emp#p#north.isl 581emp#p#west.isl 582emp.frm 583emp.par 584---- MYSQL_TMP_DIR/alt_dir_east/test 585emp#p#east.ibd 586---- MYSQL_TMP_DIR/alt_dir_north/test 587emp#p#north.ibd 588---- MYSQL_TMP_DIR/alt_dir_west/test 589emp#p#west.ibd 590# 591# TRUNCATE one PARTITION. 592# 593ALTER TABLE emp TRUNCATE PARTITION west; 594SHOW CREATE TABLE emp; 595Table Create Table 596emp CREATE TABLE `emp` ( 597 `id` int(11) NOT NULL, 598 `store_name` varchar(30) DEFAULT NULL, 599 `parts` varchar(30) DEFAULT NULL, 600 `store_id` int(11) DEFAULT NULL 601) ENGINE=InnoDB DEFAULT CHARSET=latin1 602/*!50100 PARTITION BY LIST (store_id) 603(PARTITION east VALUES IN (10,20,30) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = InnoDB, 604 PARTITION north VALUES IN (40,50,60) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = InnoDB, 605 PARTITION west VALUES IN (70,80,100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = InnoDB) */ 606SELECT name,n_cols,file_format,row_format 607FROM information_schema.innodb_sys_tables 608WHERE name LIKE 'test%' 609 ORDER BY name; 610name n_cols file_format row_format 611test/emp#p#east 7 Antelope Compact 612test/emp#p#north 7 Antelope Compact 613test/emp#p#west 7 Antelope Compact 614SELECT name,file_format,row_format 615FROM information_schema.innodb_sys_tablespaces 616ORDER BY name; 617name file_format row_format 618mysql/innodb_index_stats Antelope Compact or Redundant 619mysql/innodb_table_stats Antelope Compact or Redundant 620mysql/slave_master_info Antelope Compact or Redundant 621mysql/slave_relay_log_info Antelope Compact or Redundant 622mysql/slave_worker_info Antelope Compact or Redundant 623test/emp#p#east Antelope Compact or Redundant 624test/emp#p#north Antelope Compact or Redundant 625test/emp#p#west Antelope Compact or Redundant 626SELECT path FROM information_schema.innodb_sys_datafiles 627WHERE path LIKE '%test%' ORDER BY space; 628path 629MYSQL_TMP_DIR/alt_dir_east/test/emp#p#east.ibd 630MYSQL_TMP_DIR/alt_dir_north/test/emp#p#north.ibd 631MYSQL_TMP_DIR/alt_dir_west/test/emp#p#west.ibd 632SELECT * FROM emp; 633id store_name parts store_id 6341 Oracle NUTT 10 6352 HUAWEI BOLT 40 636INSERT INTO emp VALUES(3,'IBM','NAIL',70); 637SELECT * FROM emp; 638id store_name parts store_id 6391 Oracle NUTT 10 6402 HUAWEI BOLT 40 6413 IBM NAIL 70 642---- MYSQLD_DATADIR/test 643emp#p#east.isl 644emp#p#north.isl 645emp#p#west.isl 646emp.frm 647emp.par 648---- MYSQL_TMP_DIR/alt_dir_east/test 649emp#p#east.ibd 650---- MYSQL_TMP_DIR/alt_dir_north/test 651emp#p#north.ibd 652---- MYSQL_TMP_DIR/alt_dir_west/test 653emp#p#west.ibd 654DROP TABLE emp; 655# 656# Cleanup 657# 658