1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
# Skip this test other platfoms and run only on Linux.
# Set the environmental variables
# Stop server
# Copy the remote tablespace & DB zip files from suite location to working location.
# Check that the file exists in the working folder.
# Unzip the zip file.
# Remove the DOS based *.isl files from the MySql Data directory.
# Check that the *.ibd files are in the required location.
# Create new *.isl files with the correct path to the *.ibd files,
# Restart the DB server from unzip location Data Dir.
# Note that lower case option is required because the
# partition tables will be stored in mixed (Upper & Lower) format on Linux,
# but on Windows the partition table names are stored in lower case only.
# Check the DB & tables with DML statements.
use test;
SHOW CREATE TABLE emp1;
Table Create Table
emp1 CREATE TABLE `emp1` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(30) DEFAULT NULL,
`sal` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE emp2;
Table Create Table
emp2 CREATE TABLE `emp2` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(30) DEFAULT NULL,
`sal` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (empno)
(PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/tab1' ENGINE = InnoDB,
PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/tab2' ENGINE = InnoDB) */
SHOW CREATE TABLE emp3;
Table Create Table
emp3 CREATE TABLE `emp3` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(30) DEFAULT NULL,
`sal` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/mysqld.5980/tab3/'
SHOW CREATE TABLE emp4;
Table Create Table
emp4 CREATE TABLE `emp4` (
`empno` int(11) DEFAULT NULL,
`ename` varchar(30) DEFAULT NULL,
`sal` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
/*!50100 PARTITION BY HASH (empno)
(PARTITION p1 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/tab4' ENGINE = InnoDB,
PARTITION p2 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/tab5' ENGINE = InnoDB) */
SHOW CREATE TABLE purchase;
Table Create Table
purchase CREATE TABLE `purchase` (
`id` int(11) DEFAULT NULL,
`purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
SUBPARTITION BY HASH ( TO_DAYS(purchased))
(PARTITION p0 VALUES LESS THAN (1990)
(SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/part0' ENGINE = InnoDB,
SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/part1' ENGINE = InnoDB),
PARTITION p1 VALUES LESS THAN (2000)
(SUBPARTITION s2 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/part2' ENGINE = InnoDB,
SUBPARTITION s3 DATA DIRECTORY = 'MYSQL_TMP_DIR/mysqld.5980/part3' ENGINE = InnoDB)) */
SELECT COUNT(*) FROM emp1;
COUNT(*)
2
SELECT COUNT(*) FROM emp2;
COUNT(*)
2
SELECT COUNT(*) FROM emp3;
COUNT(*)
2
SELECT COUNT(*) FROM emp4;
COUNT(*)
2
SELECT COUNT(*) FROM purchase;
COUNT(*)
1
DELETE FROM emp1;
DELETE FROM emp2;
DELETE FROM emp3;
DELETE FROM emp4;
DELETE FROM purchase;
SELECT COUNT(*) FROM emp1;
COUNT(*)
0
SELECT COUNT(*) FROM emp2;
COUNT(*)
0
SELECT COUNT(*) FROM emp3;
COUNT(*)
0
SELECT COUNT(*) FROM emp4;
COUNT(*)
0
SELECT COUNT(*) FROM purchase;
COUNT(*)
0
# Check the system tables have the proper entry of the tables.
SELECT path FROM information_schema.innodb_sys_datafiles
WHERE path LIKE '%test%' ORDER BY space;
path
.\test\emp1.ibd
MYSQL_TMP_DIR/mysqld.5980/tab1/test/emp2#p#p1.ibd
MYSQL_TMP_DIR/mysqld.5980/tab2/test/emp2#p#p2.ibd
MYSQL_TMP_DIR/mysqld.5980/tab3/test/emp3.ibd
MYSQL_TMP_DIR/mysqld.5980/part0/test/purchase#p#p0#sp#s0.ibd
MYSQL_TMP_DIR/mysqld.5980/part1/test/purchase#p#p0#sp#s1.ibd
MYSQL_TMP_DIR/mysqld.5980/part2/test/purchase#p#p1#sp#s2.ibd
MYSQL_TMP_DIR/mysqld.5980/part3/test/purchase#p#p1#sp#s3.ibd
MYSQL_TMP_DIR/mysqld.5980/tab4/test/emp4#p#p1.ibd
MYSQL_TMP_DIR/mysqld.5980/tab5/test/emp4#p#p2.ibd
SELECT name,file_format,row_format
FROM information_schema.innodb_sys_tablespaces
ORDER BY name;
name file_format row_format
test/emp1 Antelope Compact or Redundant
test/emp2#p#p1 Antelope Compact or Redundant
test/emp2#p#p2 Antelope Compact or Redundant
test/emp3 Antelope Compact or Redundant
test/emp4#p#p1 Barracuda Dynamic
test/emp4#p#p2 Barracuda Dynamic
test/purchase#p#p0#sp#s0 Antelope Compact or Redundant
test/purchase#p#p0#sp#s1 Antelope Compact or Redundant
test/purchase#p#p1#sp#s2 Antelope Compact or Redundant
test/purchase#p#p1#sp#s3 Antelope Compact or Redundant
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE '%emp%' ORDER BY name;
name n_cols file_format row_format
test/emp1 6 Antelope Compact
test/emp2#p#p1 6 Antelope Compact
test/emp2#p#p2 6 Antelope Compact
test/emp3 6 Antelope Compact
test/emp4#p#p1 6 Barracuda Dynamic
test/emp4#p#p2 6 Barracuda Dynamic
SELECT name,n_cols,file_format,row_format
FROM information_schema.innodb_sys_tables
WHERE name LIKE '%purchase%' ORDER BY name;
name n_cols file_format row_format
test/purchase#p#p0#sp#s0 5 Antelope Compact
test/purchase#p#p0#sp#s1 5 Antelope Compact
test/purchase#p#p1#sp#s2 5 Antelope Compact
test/purchase#p#p1#sp#s3 5 Antelope Compact
#
# Cleanup
#
DROP TABLE emp1;
DROP TABLE emp2;
DROP TABLE emp3;
DROP TABLE emp4;
DROP TABLE purchase;
CREATE DATABASE mtr;
CREATE PROCEDURE mtr.check_warnings(OUT result INT)
BEGIN
SELECT 0 INTO RESULT;
END|
|
