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
|
# Check Default row_format=Dynamic
SELECT @@innodb_default_row_format;
# Check if no tablespace is set
if ($tablespace == '') {
# Check Default file_per_table=ON & Barracuda
SELECT @@innodb_file_per_table;
--echo #Create table with no tablespace
CREATE TABLE tab(c1 TEXT, c2 BLOB);
# Insert some records
INSERT INTO tab VALUES('Check with no tablespace','Check with no tablespace');
}
# Check if tablespace is set
if ($tablespace == 'tblsp') {
# Check for compressed tables without File_block_size.
--echo #Create table with tablespace
eval CREATE TABLESPACE $tablespace ADD DATAFILE '$tablespace.ibd'
ENGINE=InnoDB;
eval CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE $tablespace;
# Check for compressed tables with File_block_size.
CREATE TABLESPACE tblsp1 ADD DATAFILE 'tblsp1.ibd' FILE_BLOCK_SIZE=1k
ENGINE=InnoDB;
CREATE TABLE tab1(c1 TEXT, c2 BLOB) KEY_BLOCK_SIZE=1 TABLESPACE tblsp1;
# Insert some records
INSERT INTO tab VALUES('Check with General tablespace',
'Check with General tablespace');
INSERT INTO tab1 VALUES('tablsp File Block size',
'tablsp File Block size');
}
# Check if system tablespace is set
if ($tablespace == 'innodb_system') {
--echo #Create table with innodb system tablespace
# Create table in system tablesspace
CREATE TABLE tab(c1 TEXT, c2 BLOB) TABLESPACE innodb_system;
# Insert some records
INSERT INTO tab VALUES('Check with InnoDB system tablespace',
'Check with InnoDB system tablespace');
}
# Check when file_per_table=OFF is set
if ($tablespace == 'OFF') {
# Set file_per_table=OFF
SET GLOBAL innodb_file_per_table=0;
# Check Default file_per_table=OFF
SELECT @@innodb_file_per_table;
--echo #Create table with file_per_table=0
CREATE TABLE tab(c1 TEXT, c2 BLOB);
# Insert some records
INSERT INTO tab VALUES('File per table off','File per table off');
}
# Check Default row_format=Dynamic
--source suite/innodb/include/default_row_format_show.inc
ALTER TABLE tab ROW_FORMAT=COMPACT;
# Check Default row_format=Compact
--source suite/innodb/include/default_row_format_show.inc
CHECK TABLE tab;
ALTER TABLE tab ROW_FORMAT=DYNAMIC;
# Check Default row_format=Dynamic
--source suite/innodb/include/default_row_format_show.inc
CHECK TABLE tab;
ALTER TABLE tab ROW_FORMAT=REDUNDANT;
# Check Default row_format=Redundant
--source suite/innodb/include/default_row_format_show.inc
CHECK TABLE tab;
# Check successful, when no tablespace is set
if ($tablespace == '') {
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
}
# When tablespace is set, check error for compressed tables with
# and without File_block_size
# Check ALTER success for compressed tables with tablespace File_block_size
if ($tablespace == 'tblsp') {
# Check error with compressed tables without File_block_size
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
# Check error with compressed tables with File_block_size
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE tab1 ROW_FORMAT=DYNAMIC;
}
# Check when file_per_table=OFF is set
if ($tablespace == 'OFF') {
--error ER_ILLEGAL_HA_CREATE_OPTION
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
}
# Check Default row_format=Compressed
# Note: we do not use default_row_format_show.inc because
# ALTER ROW_FORMAT=COMPRESSED shows different compressed page sizes
# with different innodb_page_sizes
SELECT NAME,ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME='test/tab';
CHECK TABLE tab;
if ($tablespace == 'tblsp') {
CHECK TABLE tab1;
}
ALTER TABLE tab ROW_FORMAT=Dynamic;
# Check Default row_format=Dynamic;
--source suite/innodb/include/default_row_format_show.inc
CHECK TABLE tab;
# Cleanup
DROP TABLE tab;
# Drop only when tablespace is set
if ($tablespace == 'tblsp') {
DROP TABLE tab1;
eval DROP TABLESPACE $tablespace;
DROP TABLESPACE tblsp1;
}
# Reset to default values
SET GLOBAL innodb_file_per_table=Default;
|
