1--source include/have_debug.inc 2# 3# BUG#60111 storage type for table not saved in .frm 4# 5 6# 7# Check that the table options for TABLESPACE and STORAGE 8# are printed in SHOW CREATE TABLE 9# 10 11# TABLESPACE only 12CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM; 13SHOW CREATE TABLE t1; 14DROP TABLE t1; 15 16# TABLESPACE + STORAGE DISK 17CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM; 18SHOW CREATE TABLE t1; 19DROP TABLE t1; 20 21# TABLESPACE + STORAGE MEMORY 22CREATE TABLE t1(a int) TABLESPACE ts STORAGE MEMORY ENGINE=MyISAM; 23SHOW CREATE TABLE t1; 24DROP TABLE t1; 25 26# STORAGE MEMORY only 27CREATE TABLE t1(a int) STORAGE MEMORY ENGINE=MyISAM; 28SHOW CREATE TABLE t1; 29DROP TABLE t1; 30 31# STORAGE DISK only 32CREATE TABLE t1(a int) STORAGE DISK ENGINE=MyISAM; 33SHOW CREATE TABLE t1; 34DROP TABLE t1; 35 36# 37# Check that the table options for TABLESPACE and STORAGE 38# are kept in an ALTER 39# 40 41# TABLESPACE only 42CREATE TABLE t1(a int) TABLESPACE ts ENGINE=MyISAM; 43ALTER TABLE t1 ADD COLUMN b int; 44SHOW CREATE TABLE t1; 45DROP TABLE t1; 46 47# TABLESPACE and STORAGE DISK 48CREATE TABLE t1(a int) TABLESPACE ts STORAGE DISK ENGINE=MyISAM; 49ALTER TABLE t1 ADD COLUMN b int; 50SHOW CREATE TABLE t1; 51DROP TABLE t1; 52 53# 54# Check that the table options for TABLESPACE and STORAGE 55# can be changed with an ALTER 56# 57 58# TABLESPACE only 59CREATE TABLE t1(a int) ENGINE=MyISAM; 60 61ALTER TABLE t1 TABLESPACE ts; 62SHOW CREATE TABLE t1; 63 64ALTER TABLE t1 TABLESPACE ts2; 65SHOW CREATE TABLE t1; 66 67DROP TABLE t1; 68 69# STORAGE only 70CREATE TABLE t1(a int) ENGINE=MyISAM; 71 72ALTER TABLE t1 STORAGE MEMORY; 73SHOW CREATE TABLE t1; 74 75ALTER TABLE t1 STORAGE DISK; 76SHOW CREATE TABLE t1; 77 78DROP TABLE t1; 79 80# TABLESPACE and STORAGE 81CREATE TABLE t1(a int) ENGINE=MyISAM; 82 83ALTER TABLE t1 STORAGE MEMORY TABLESPACE ts; 84SHOW CREATE TABLE t1; 85 86ALTER TABLE t1 STORAGE DISK TABLESPACE ts2; 87SHOW CREATE TABLE t1; 88 89DROP TABLE t1; 90 91# 92# Check that it's possible to read a .frm fle created 93# by MySQL Cluster 7.0(which introduced the new "format 94# section) with this statement: 95# 96# CREATE TABLE cluster_7022_table 97# ( 98# a int primary key, 99# b int, 100# c int STORAGE DISK, 101# d int STORAGE MEMORY NOT NULL, 102# e int COLUMN_FORMAT DYNAMIC, 103# f int COLUMN_FORMAT FIXED, 104# g int COLUMN_FORMAT DEFAULT, 105# h int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 106# i int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 107# j int STORAGE DISK COLUMN_FORMAT FIXED, 108# k int STORAGE MEMORY COLUMN_FORMAT FIXED 109# ) STORAGE DISK TABLESPACE the_tablespacename ENGINE=MyISAM; 110# 111 112let $MYSQLD_DATADIR= `SELECT @@datadir`; 113copy_file std_data/cluster_7022_table.frm $MYSQLD_DATADIR/test/t1.frm; 114copy_file std_data/cluster_7022_table.MYD $MYSQLD_DATADIR/test/t1.MYD; 115copy_file std_data/cluster_7022_table.MYI $MYSQLD_DATADIR/test/t1.MYI; 116 117SHOW CREATE TABLE t1; 118 119DROP TABLE t1; 120 121--echo # 122--echo # WL#3627 Add COLUMN_FORMAT and STORAGE for fields 123--echo # 124 125--let $DEFAULT_ENGINE = `select @@global.default_storage_engine` 126 127CREATE TABLE t1 ( 128 a int STORAGE DISK, 129 b int STORAGE MEMORY NOT NULL, 130 c int COLUMN_FORMAT DYNAMIC, 131 d int COLUMN_FORMAT FIXED, 132 e int COLUMN_FORMAT DEFAULT, 133 f int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 134 g int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 135 h int STORAGE DISK COLUMN_FORMAT FIXED, 136 i int STORAGE MEMORY COLUMN_FORMAT FIXED 137); 138--replace_result $DEFAULT_ENGINE ENGINE 139SHOW CREATE TABLE t1; 140 141# Add new columns with all variations of the new column 142# level attributes 143ALTER TABLE t1 144 ADD COLUMN j int STORAGE DISK, 145 ADD COLUMN k int STORAGE MEMORY NOT NULL, 146 ADD COLUMN l int COLUMN_FORMAT DYNAMIC, 147 ADD COLUMN m int COLUMN_FORMAT FIXED, 148 ADD COLUMN n int COLUMN_FORMAT DEFAULT, 149 ADD COLUMN o int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 150 ADD COLUMN p int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 151 ADD COLUMN q int STORAGE DISK COLUMN_FORMAT FIXED, 152 ADD COLUMN r int STORAGE MEMORY COLUMN_FORMAT FIXED; 153--replace_result $DEFAULT_ENGINE ENGINE 154SHOW CREATE TABLE t1; 155 156# Use MODIFY COLUMN to "shift" all new attributes to the next column 157ALTER TABLE t1 158 MODIFY COLUMN j int STORAGE MEMORY NOT NULL, 159 MODIFY COLUMN k int COLUMN_FORMAT DYNAMIC, 160 MODIFY COLUMN l int COLUMN_FORMAT FIXED, 161 MODIFY COLUMN m int COLUMN_FORMAT DEFAULT, 162 MODIFY COLUMN n int STORAGE DISK COLUMN_FORMAT DYNAMIC NOT NULL, 163 MODIFY COLUMN o int STORAGE MEMORY COLUMN_FORMAT DYNAMIC, 164 MODIFY COLUMN p int STORAGE DISK COLUMN_FORMAT FIXED, 165 MODIFY COLUMN q int STORAGE MEMORY COLUMN_FORMAT FIXED, 166 MODIFY COLUMN r int STORAGE DISK; 167--replace_result $DEFAULT_ENGINE ENGINE 168SHOW CREATE TABLE t1; 169 170# Check behaviour of multiple COLUMN_FORMAT and/or STORAGE definitions 171ALTER TABLE t1 172 MODIFY COLUMN h int COLUMN_FORMAT DYNAMIC COLUMN_FORMAT FIXED, 173 MODIFY COLUMN i int COLUMN_FORMAT DYNAMIC COLUMN_FORMAT DEFAULT, 174 MODIFY COLUMN j int COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC, 175 MODIFY COLUMN k int COLUMN_FORMAT FIXED COLUMN_FORMAT DEFAULT, 176 MODIFY COLUMN l int STORAGE DISK STORAGE MEMORY, 177 MODIFY COLUMN m int STORAGE DISK STORAGE DEFAULT, 178 MODIFY COLUMN n int STORAGE MEMORY STORAGE DISK, 179 MODIFY COLUMN o int STORAGE MEMORY STORAGE DEFAULT, 180 MODIFY COLUMN p int STORAGE DISK STORAGE MEMORY 181 COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC, 182 MODIFY COLUMN q int STORAGE DISK STORAGE MEMORY STORAGE DEFAULT 183 COLUMN_FORMAT FIXED COLUMN_FORMAT DYNAMIC COLUMN_FORMAT DEFAULT, 184 MODIFY COLUMN r int STORAGE DEFAULT STORAGE DEFAULT STORAGE MEMORY 185 STORAGE DISK STORAGE MEMORY STORAGE DISK STORAGE DISK; 186--replace_result $DEFAULT_ENGINE ENGINE 187SHOW CREATE TABLE t1; 188 189 190 191DROP TABLE t1; 192 193 194--echo # 195--echo # Bug#21347001 SEGMENTATION FAULT WHILE CREATING GENERAL 196--echo # TABLESPACE IN DISK FULL LINUX 197--echo # 198SET SESSION debug="+d,out_of_tablespace_disk"; 199--error ER_CREATE_FILEGROUP_FAILED 200CREATE TABLESPACE `ts6` ADD DATAFILE 'ts6.ibd' ENGINE=INNODB; 201SHOW WARNINGS; 202SET SESSION debug="-d,out_of_tablespace_disk"; 203