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
158
159
160
161
162
163
164
165
166
167
|
--source include/have_innodb.inc
call mtr.add_suppression("Index for table 'tab' is corrupt; try to repair it");
SET @row_format = @@GLOBAL.innodb_default_row_format;
# set the variables
let $MYSQLD_DATADIR = `SELECT @@datadir`;
--echo # ###########################################################
--echo # Check with Import/Export tablespace with Default_row_format
# Set row_format=Compact
SET GLOBAL innodb_default_row_format=Compact;
# Check row_format=Compact
SELECT @@innodb_default_row_format;
# Check file_per_table=1
SELECT @@innodb_file_per_table;
CREATE TABLE tab(a INT) ENGINE=InnoDB;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
INSERT INTO tab VALUES(1);
INSERT INTO tab VALUES(2);
# Check the rows
SELECT * FROM tab;
FLUSH TABLE tab FOR EXPORT;
# Take the backup of the ibd and cfg files
--copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_DATADIR/tab.cfg
--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/tab.ibd
UNLOCK TABLES;
# Cleanup
DROP TABLE tab;
# Set the default_row_format=Dynamic
SET GLOBAL innodb_default_row_format=Dynamic;
CREATE TABLE tab(a INT) ENGINE=InnoDB;
# Remove the *.ibd file
ALTER TABLE tab DISCARD TABLESPACE;
# Move the *.ibd,*.cfg file into orginal location
--move_file $MYSQLD_DATADIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg
--move_file $MYSQLD_DATADIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd
--error ER_TABLE_SCHEMA_MISMATCH
ALTER TABLE tab IMPORT TABLESPACE;
# Take the backup of the ibd and cfg files
--copy_file $MYSQLD_DATADIR/test/tab.cfg $MYSQLD_DATADIR/tab.cfg
--copy_file $MYSQLD_DATADIR/test/tab.ibd $MYSQLD_DATADIR/tab.ibd
# Cleanup
DROP TABLE tab;
# Set the default_row_format=Compact
SET GLOBAL innodb_default_row_format=Compact;
# Check row_format=Compact
SELECT @@innodb_default_row_format;
CREATE TABLE tab(a INT) ENGINE=InnoDB;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
# Remove the *.ibd file
ALTER TABLE tab DISCARD TABLESPACE;
# Move the *ibd,*.cfg file into orginal location
--copy_file $MYSQLD_DATADIR/tab.cfg $MYSQLD_DATADIR/test/tab.ibd
--move_file $MYSQLD_DATADIR/tab.cfg $MYSQLD_DATADIR/test/tab.cfg
call mtr.add_suppression("InnoDB: Tried to read .* bytes at offset 0");
--error ER_NOT_KEYFILE
ALTER TABLE tab IMPORT TABLESPACE;
--remove_file $MYSQLD_DATADIR/test/tab.ibd
--move_file $MYSQLD_DATADIR/tab.ibd $MYSQLD_DATADIR/test/tab.ibd
# Check import is successful (because same row_format)
ALTER TABLE tab IMPORT TABLESPACE;
# Check the rows
SELECT * FROM tab;
# Cleanup
DROP TABLE tab;
--echo # ###########################################################
# Check when Index Column size (3070 bytes) is too long, Change row_format
# Check when Index Column size (767 bytes), Change row_format
# Dynamic to Compact to Dynamic
# Set the default_row_format=Dynamic
SET GLOBAL innodb_default_row_format=Dynamic;
SELECT @@innodb_default_row_format;
CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(3070))) ENGINE= InnoDB;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
# Check by SELECT, no errors
SELECT * FROM tab;
# Change row_format to Compact
SET GLOBAL innodb_default_row_format=COMPACT;
# Check error ERROR 1709 (HY000): Index column size too large
-- error ER_INDEX_COLUMN_TOO_LONG
ALTER TABLE tab ROW_FORMAT=COMPACT;
# Cleanup
DROP TABLE tab;
# Change the default_row_format to default
SET GLOBAL innodb_default_row_format=Default;
SELECT @@innodb_default_row_format;
SET GLOBAL innodb_default_row_format=Dynamic;
# Change row_format to Dynamic
SELECT @@innodb_default_row_format;
CREATE TABLE tab(a INT PRIMARY KEY, b VARCHAR(5000), KEY idx1(b(767))) ENGINE= InnoDB;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
INSERT INTO tab(a,b) VALUES(1,'Check with max column size');
# Check by SELECT, no errors
SELECT * FROM tab;
# Check no errors because Compact allows 767 bytes
ALTER TABLE tab ROW_FORMAT=COMPACT;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
# Check by SELECT, no errors
SELECT * FROM tab;
# Check no errors
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
# Check by SELECT, no errors
SELECT * FROM tab;
# Check no errors
ALTER TABLE tab ROW_FORMAT=Dynamic;
--replace_column 3 # 5 # 6 # 7 # 8 # 9 # 10 # 12 #
SHOW TABLE STATUS LIKE 'tab';
# Cleanup
DROP TABLE tab;
SET GLOBAL innodb_default_row_format = @row_format;
|
