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
|
--source include/force_myisam_default.inc
--source include/have_myisam.inc
--source include/have_debug.inc
#
# Check that the table options for TABLESPACE and STORAGE
# are printed in SHOW CREATE TABLE
#
# TABLESPACE only
CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# TABLESPACE + STORAGE DISK
CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# TABLESPACE + STORAGE MEMORY
CREATE TABLE t1(a int) TABLESPACE ts STORAGE MEMORY ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# STORAGE MEMORY only
CREATE TABLE t1(a int) STORAGE MEMORY ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# STORAGE DISK only
CREATE TABLE t1(a int) STORAGE DISK ENGINE=MyISAM;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
# Check that the table options for TABLESPACE and STORAGE
# are kept in an ALTER
#
# TABLESPACE only
CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM;
ALTER TABLE t1 ADD COLUMN b int;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# TABLESPACE and STORAGE DISK
CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM;
ALTER TABLE t1 ADD COLUMN b int;
SHOW CREATE TABLE t1;
DROP TABLE t1;
#
# Check that the table options for TABLESPACE and STORAGE
# can be changed with an ALTER
#
# TABLESPACE only
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 TABLESPACE ts;
SHOW CREATE TABLE t1;
ALTER TABLE t1 TABLESPACE ts2;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# STORAGE only
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 STORAGE MEMORY;
SHOW CREATE TABLE t1;
ALTER TABLE t1 STORAGE DISK;
SHOW CREATE TABLE t1;
DROP TABLE t1;
# TABLESPACE and STORAGE
CREATE TABLE t1(a int) ENGINE=MyISAM;
ALTER TABLE t1 STORAGE MEMORY TABLESPACE ts;
SHOW CREATE TABLE t1;
ALTER TABLE t1 STORAGE DISK TABLESPACE ts2;
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo # 2. Non partitioned table DDL.
--echo # 2.1 Create table.
CREATE TABLE t1 (i INTEGER) TABLESPACE innodb_file_per_table ENGINE InnoDB;
CREATE TABLE t2 (i INTEGER) TABLESPACE innodb_system ENGINE InnoDB;
--echo # 2.2 Alter table.
--error ER_WRONG_TABLESPACE_NAME
ALTER TABLE t2 TABLESPACE `innodb_file_per_table.2`;
--echo # This is valid since MyISAM does not care:
ALTER TABLE t2 TABLESPACE `innodb_file_per_table.2` ENGINE MyISAM;
SHOW CREATE TABLE t2;
--echo # Table t1 is carried over to MyISAM using the dummy 'innodb_file_per_table':
ALTER TABLE t1 ENGINE MyISAM;
SHOW CREATE TABLE t1;
--echo # Changing only engine back to InnoDB now will be rejected for t2:
--error ER_WRONG_TABLESPACE_NAME
ALTER TABLE t2 ENGINE InnoDB;
SHOW CREATE TABLE t2;
--echo # For t1, changing engine back to InnoDB will re-establish usage of the implicit tablespace:
ALTER TABLE t1 ENGINE InnoDB;
SHOW CREATE TABLE t1;
--echo # Changing both engine and tablespace works:
ALTER TABLE t1 TABLESPACE innodb_system ENGINE InnoDB;
SHOW CREATE TABLE t1;
ALTER TABLE t2 TABLESPACE innodb_file_per_table ENGINE InnoDB;
SHOW CREATE TABLE t2;
--echo # Keeping a valid tablespace through ALTER TABLE:
ALTER TABLE t1 ADD COLUMN (j INTEGER);
CREATE TABLESPACE ts ADD DATAFILE 'f.ibd' ENGINE InnoDB;
ALTER TABLE t1 TABLESPACE ts;
ALTER TABLE t1 ENGINE MyISAM;
SHOW CREATE TABLE t1;
ALTER TABLE t1 ENGINE InnoDB;
SHOW CREATE TABLE t1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLESPACE ts;
--echo # 1. Verify that ENGINE attribute is not needed for ALTER and DROP
--echo # TABLESPACE
CREATE TABLESPACE ts1 ADD DATAFILE 'df1.ibd' ENGINE=InnoDB;
--echo # No need to add ENGINE - looked up in DD
--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd';
SHOW WARNINGS;
--echo # Specifying correct ENGINE is allowed, but triggers deprecation
--echo # warning
--error ER_ALTER_FILEGROUP_FAILED
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd' ENGINE=INNODB;
SHOW WARNINGS;
--echo # Specifying a different ENGINE than the one stored in the DD is an
--echo # error
--error ER_TABLESPACE_ENGINE_MISMATCH
ALTER TABLESPACE ts1 ADD DATAFILE 'df2.ibd' ENGINE=MYISAM;
DROP TABLESPACE ts1;
|
