1CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM; 2SHOW CREATE TABLE t1; 3Table Create Table 4t1 CREATE TABLE `t1` ( 5 `a` int(11) DEFAULT NULL 6) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 7DROP TABLE t1; 8CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM; 9SHOW CREATE TABLE t1; 10Table Create Table 11t1 CREATE TABLE `t1` ( 12 `a` int(11) DEFAULT NULL 13) /*!50100 TABLESPACE `ts` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 14DROP TABLE t1; 15CREATE TABLE t1(a int) TABLESPACE ts STORAGE MEMORY ENGINE=MyISAM; 16SHOW CREATE TABLE t1; 17Table Create Table 18t1 CREATE TABLE `t1` ( 19 `a` int(11) DEFAULT NULL 20) /*!50100 TABLESPACE `ts` STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=latin1 21DROP TABLE t1; 22CREATE TABLE t1(a int) STORAGE MEMORY ENGINE=MyISAM; 23SHOW CREATE TABLE t1; 24Table Create Table 25t1 CREATE TABLE `t1` ( 26 `a` int(11) DEFAULT NULL 27) /*!50100 STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=latin1 28DROP TABLE t1; 29CREATE TABLE t1(a int) STORAGE DISK ENGINE=MyISAM; 30SHOW CREATE TABLE t1; 31Table Create Table 32t1 CREATE TABLE `t1` ( 33 `a` int(11) DEFAULT NULL 34) /*!50100 STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 35DROP TABLE t1; 36CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM; 37ALTER TABLE t1 ADD COLUMN b int; 38SHOW CREATE TABLE t1; 39Table Create Table 40t1 CREATE TABLE `t1` ( 41 `a` int(11) DEFAULT NULL, 42 `b` int(11) DEFAULT NULL 43) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 44DROP TABLE t1; 45CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM; 46ALTER TABLE t1 ADD COLUMN b int; 47SHOW CREATE TABLE t1; 48Table Create Table 49t1 CREATE TABLE `t1` ( 50 `a` int(11) DEFAULT NULL, 51 `b` int(11) DEFAULT NULL 52) /*!50100 TABLESPACE `ts` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 53DROP TABLE t1; 54CREATE TABLE t1(a int) ENGINE=MyISAM; 55ALTER TABLE t1 TABLESPACE ts; 56SHOW CREATE TABLE t1; 57Table Create Table 58t1 CREATE TABLE `t1` ( 59 `a` int(11) DEFAULT NULL 60) /*!50100 TABLESPACE `ts` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 61ALTER TABLE t1 TABLESPACE ts2; 62SHOW CREATE TABLE t1; 63Table Create Table 64t1 CREATE TABLE `t1` ( 65 `a` int(11) DEFAULT NULL 66) /*!50100 TABLESPACE `ts2` */ ENGINE=MyISAM DEFAULT CHARSET=latin1 67DROP TABLE t1; 68CREATE TABLE t1(a int) ENGINE=MyISAM; 69ALTER TABLE t1 STORAGE MEMORY; 70SHOW CREATE TABLE t1; 71Table Create Table 72t1 CREATE TABLE `t1` ( 73 `a` int(11) DEFAULT NULL 74) /*!50100 STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=latin1 75ALTER TABLE t1 STORAGE DISK; 76SHOW CREATE TABLE t1; 77Table Create Table 78t1 CREATE TABLE `t1` ( 79 `a` int(11) DEFAULT NULL 80) /*!50100 STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 81DROP TABLE t1; 82CREATE TABLE t1(a int) ENGINE=MyISAM; 83ALTER TABLE t1 STORAGE MEMORY TABLESPACE ts; 84SHOW CREATE TABLE t1; 85Table Create Table 86t1 CREATE TABLE `t1` ( 87 `a` int(11) DEFAULT NULL 88) /*!50100 TABLESPACE `ts` STORAGE MEMORY */ ENGINE=MyISAM DEFAULT CHARSET=latin1 89ALTER TABLE t1 STORAGE DISK TABLESPACE ts2; 90SHOW CREATE TABLE t1; 91Table Create Table 92t1 CREATE TABLE `t1` ( 93 `a` int(11) DEFAULT NULL 94) /*!50100 TABLESPACE `ts2` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 95DROP TABLE t1; 96SHOW CREATE TABLE t1; 97Table Create Table 98t1 CREATE TABLE `t1` ( 99 `a` int(11) NOT NULL, 100 `b` int(11) DEFAULT NULL, 101 `c` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 102 `d` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 103 `e` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 104 `f` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 105 `g` int(11) DEFAULT NULL, 106 `h` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 107 `i` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 108 `j` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 109 `k` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 110 PRIMARY KEY (`a`) 111) /*!50100 TABLESPACE `the_tablespacename` STORAGE DISK */ ENGINE=MyISAM DEFAULT CHARSET=latin1 112DROP TABLE t1; 113# 114# WL#3627 Add COLUMN_FORMAT and STORAGE for fields 115# 116CREATE TABLE t1 ( 117a int STORAGE DISK, 118b int STORAGE MEMORY NOT NULL, 119c int COLUMN_FORMAT DYNAMIC, 120d int COLUMN_FORMAT FIXED, 121e int COLUMN_FORMAT DEFAULT, 122f int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 123g int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 124h int STORAGE DISK COLUMN_FORMAT FIXED, 125i int STORAGE MEMORY COLUMN_FORMAT FIXED 126); 127SHOW CREATE TABLE t1; 128Table Create Table 129t1 CREATE TABLE `t1` ( 130 `a` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 131 `b` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 132 `c` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 133 `d` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 134 `e` int(11) DEFAULT NULL, 135 `f` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 136 `g` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 137 `h` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 138 `i` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL 139) ENGINE=ENGINE DEFAULT CHARSET=latin1 140ALTER TABLE t1 141ADD COLUMN j int STORAGE DISK, 142ADD COLUMN k int STORAGE MEMORY NOT NULL, 143ADD COLUMN l int COLUMN_FORMAT DYNAMIC, 144ADD COLUMN m int COLUMN_FORMAT FIXED, 145ADD COLUMN n int COLUMN_FORMAT DEFAULT, 146ADD COLUMN o int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 147ADD COLUMN p int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 148ADD COLUMN q int STORAGE DISK COLUMN_FORMAT FIXED, 149ADD COLUMN r int STORAGE MEMORY COLUMN_FORMAT FIXED; 150SHOW CREATE TABLE t1; 151Table Create Table 152t1 CREATE TABLE `t1` ( 153 `a` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 154 `b` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 155 `c` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 156 `d` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 157 `e` int(11) DEFAULT NULL, 158 `f` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 159 `g` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 160 `h` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 161 `i` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 162 `j` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 163 `k` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 164 `l` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 165 `m` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 166 `n` int(11) DEFAULT NULL, 167 `o` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 168 `p` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 169 `q` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 170 `r` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL 171) ENGINE=ENGINE DEFAULT CHARSET=latin1 172ALTER TABLE t1 173MODIFY COLUMN j int STORAGE MEMORY NOT NULL, 174MODIFY COLUMN k int COLUMN_FORMAT DYNAMIC, 175MODIFY COLUMN l int COLUMN_FORMAT FIXED, 176MODIFY COLUMN m int COLUMN_FORMAT DEFAULT, 177MODIFY COLUMN n int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 178MODIFY COLUMN o int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 179MODIFY COLUMN p int STORAGE DISK COLUMN_FORMAT FIXED, 180MODIFY COLUMN q int STORAGE MEMORY COLUMN_FORMAT FIXED, 181MODIFY COLUMN r int STORAGE DISK; 182SHOW CREATE TABLE t1; 183Table Create Table 184t1 CREATE TABLE `t1` ( 185 `a` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 186 `b` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 187 `c` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 188 `d` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 189 `e` int(11) DEFAULT NULL, 190 `f` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 191 `g` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 192 `h` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 193 `i` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 194 `j` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 195 `k` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 196 `l` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 197 `m` int(11) DEFAULT NULL, 198 `n` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 199 `o` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 200 `p` int(11) /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 201 `q` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 202 `r` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL 203) ENGINE=ENGINE DEFAULT CHARSET=latin1 204ALTER TABLE t1 205MODIFY COLUMN h int COLUMN_FORMAT DYNAMIC COLUMN_FORMAT FIXED, 206MODIFY COLUMN i int COLUMN_FORMAT DYNAMIC COLUMN_FORMAT DEFAULT, 207MODIFY COLUMN j int COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC, 208MODIFY COLUMN k int COLUMN_FORMAT FIXED COLUMN_FORMAT DEFAULT, 209MODIFY COLUMN l int STORAGE DISK STORAGE MEMORY, 210MODIFY COLUMN m int STORAGE DISK STORAGE DEFAULT, 211MODIFY COLUMN n int STORAGE MEMORY STORAGE DISK, 212MODIFY COLUMN o int STORAGE MEMORY STORAGE DEFAULT, 213MODIFY COLUMN p int STORAGE DISK STORAGE MEMORY 214COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC, 215MODIFY COLUMN q int STORAGE DISK STORAGE MEMORY STORAGE DEFAULT 216COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC COLUMN_FORMAT DEFAULT, 217MODIFY COLUMN r int STORAGE DEFAULT STORAGE DEFAULT STORAGE MEMORY 218STORAGE DISK STORAGE MEMORY STORAGE DISK STORAGE DISK; 219SHOW CREATE TABLE t1; 220Table Create Table 221t1 CREATE TABLE `t1` ( 222 `a` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 223 `b` int(11) NOT NULL /*!50606 STORAGE MEMORY */, 224 `c` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 225 `d` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 226 `e` int(11) DEFAULT NULL, 227 `f` int(11) NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */, 228 `g` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 229 `h` int(11) /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL, 230 `i` int(11) DEFAULT NULL, 231 `j` int(11) /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 232 `k` int(11) DEFAULT NULL, 233 `l` int(11) /*!50606 STORAGE MEMORY */ DEFAULT NULL, 234 `m` int(11) DEFAULT NULL, 235 `n` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL, 236 `o` int(11) DEFAULT NULL, 237 `p` int(11) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, 238 `q` int(11) DEFAULT NULL, 239 `r` int(11) /*!50606 STORAGE DISK */ DEFAULT NULL 240) ENGINE=ENGINE DEFAULT CHARSET=latin1 241DROP TABLE t1; 242# 243# Bug#21347001 SEGMENTATION FAULT WHILE CREATING GENERAL 244# TABLESPACE IN DISK FULL LINUX 245# 246SET SESSION debug="+d,out_of_tablespace_disk"; 247CREATE TABLESPACE `ts6` ADD DATAFILE 'ts6.ibd' ENGINE=INNODB; 248ERROR HY000: Failed to create TABLESPACE ts6 249SHOW WARNINGS; 250Level Code Message 251Error 1528 Failed to create TABLESPACE ts6 252Error 1114 The table 'ts6' is full 253SET SESSION debug="-d,out_of_tablespace_disk"; 254