| 1 |
# |
| 2 |
# This script assumes that the caller has set the following variables: |
| 3 |
# $TABLESPACE1 |
| 4 |
# $TABLESPACE2 |
| 5 |
# $TABLESPACE3 |
| 6 |
# $TABLESPACE4 |
| 7 |
# $INNODB_FILE_PER_TABLE_FOR_CREATE |
| 8 |
# $INNODB_FILE_PER_TABLE_FOR_ALTER |
| 9 |
# |
| 10 |
--eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_CREATE |
| 11 |
|
| 12 |
eval CREATE TABLE t1 (a INT NOT NULL, b INT) |
| 13 |
ENGINE=InnoDB |
| 14 |
PARTITION BY RANGE(a) |
| 15 |
(PARTITION p1 VALUES LESS THAN (5) $TABLESPACE1, |
| 16 |
PARTITION p2 VALUES LESS THAN (10) $TABLESPACE2, |
| 17 |
PARTITION p3 VALUES LESS THAN (15) $TABLESPACE3); |
| 18 |
|
| 19 |
SHOW CREATE TABLE t1; |
| 20 |
|
| 21 |
--disable_query_log |
| 22 |
--replace_result #P# #p# |
| 23 |
--replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/ |
| 24 |
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as |
| 25 |
space_name |
| 26 |
FROM information_schema.innodb_tables A |
| 27 |
LEFT JOIN |
| 28 |
information_schema.innodb_tablespaces B |
| 29 |
ON A.SPACE = B.SPACE |
| 30 |
WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME; |
| 31 |
--enable_query_log |
| 32 |
|
| 33 |
--eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_ALTER |
| 34 |
|
| 35 |
eval ALTER TABLE t1 ADD PARTITION |
| 36 |
(PARTITION p4 VALUES LESS THAN (20) $TABLESPACE4); |
| 37 |
|
| 38 |
SHOW CREATE TABLE t1; |
| 39 |
|
| 40 |
--disable_query_log |
| 41 |
--replace_result #P# #p# |
| 42 |
--replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/ |
| 43 |
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as |
| 44 |
space_name |
| 45 |
FROM information_schema.innodb_tables A |
| 46 |
LEFT JOIN |
| 47 |
information_schema.innodb_tablespaces B |
| 48 |
ON A.SPACE = B.SPACE |
| 49 |
WHERE A.NAME LIKE '%t1%' ORDER BY A.NAME; |
| 50 |
--enable_query_log |
| 51 |
|
| 52 |
DROP TABLE t1; |
| 53 |
|
| 54 |
--eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_CREATE |
| 55 |
|
| 56 |
eval CREATE TABLE t2 (a INT NOT NULL, b INT) |
| 57 |
ENGINE=InnoDB |
| 58 |
PARTITION BY RANGE(a) |
| 59 |
SUBPARTITION BY KEY(b) |
| 60 |
(PARTITION p1 VALUES LESS THAN (5) |
| 61 |
(SUBPARTITION sp1 $TABLESPACE1), |
| 62 |
PARTITION p2 VALUES LESS THAN (10) |
| 63 |
(SUBPARTITION sp2 $TABLESPACE2), |
| 64 |
PARTITION p3 VALUES LESS THAN (15) |
| 65 |
(SUBPARTITION sp3 $TABLESPACE3)); |
| 66 |
|
| 67 |
SHOW CREATE TABLE t2; |
| 68 |
|
| 69 |
--disable_query_log |
| 70 |
--replace_result #P# #p# #SP# #sp# |
| 71 |
--replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/ |
| 72 |
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as |
| 73 |
space_name |
| 74 |
FROM information_schema.innodb_tables A |
| 75 |
LEFT JOIN |
| 76 |
information_schema.innodb_tablespaces B |
| 77 |
ON A.SPACE = B.SPACE |
| 78 |
WHERE A.NAME LIKE '%t2%' ORDER BY A.NAME; |
| 79 |
--enable_query_log |
| 80 |
|
| 81 |
--eval SET GLOBAL innodb_file_per_table = $INNODB_FILE_PER_TABLE_FOR_ALTER |
| 82 |
|
| 83 |
eval ALTER TABLE t2 ADD PARTITION |
| 84 |
(PARTITION p4 VALUES LESS THAN (20) |
| 85 |
(SUBPARTITION sp4 $TABLESPACE4)); |
| 86 |
|
| 87 |
SHOW CREATE TABLE t2; |
| 88 |
|
| 89 |
--disable_query_log |
| 90 |
--replace_result #P# #p# #SP# #sp# |
| 91 |
--replace_regex /innodb_file_per_table.[0-9]+/innodb_file_per_table.##/ |
| 92 |
SELECT A.NAME as partition_name, A.SPACE_TYPE as space_type, B.NAME as |
| 93 |
space_name |
| 94 |
FROM information_schema.innodb_tables A |
| 95 |
LEFT JOIN |
| 96 |
information_schema.innodb_tablespaces B |
| 97 |
ON A.SPACE = B.SPACE |
| 98 |
WHERE A.NAME LIKE '%t2%' ORDER BY A.NAME; |
| 99 |
--enable_query_log |
| 100 |
|
| 101 |
DROP TABLE t2; |