1let $MYSQLD_DATADIR= `select @@datadir`; 2 3--echo # 4--echo # Testing indexing with ALTER on inward table (in-place) 5--echo # 6CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT; 7INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three'); 8SELECT * FROM t1; 9CREATE INDEX xc ON t1(c); 10DESCRIBE SELECT * FROM t1 WHERE c = 2; 11DROP INDEX xc ON t1; 12CREATE INDEX xd ON t1(d); 13DROP INDEX xd ON t1; 14ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d); 15SHOW INDEX FROM t1; 16ALTER TABLE t1 DROP INDEX xc, DROP INDEX xd; 17SHOW INDEX FROM t1; 18 19--echo # 20--echo # Testing modifying columns inward table (not in-place) 21--echo # 22ALTER TABLE t1 MODIFY COLUMN c CHAR(5) NOT NULL; 23SHOW CREATE TABLE t1; 24SELECT * FROM t1; 25ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL; 26 27--echo # 28--echo # Fails because indexing must be in-place 29--echo # 30--error ER_ALTER_OPERATION_NOT_SUPPORTED 31ALTER TABLE t1 MODIFY COLUMN c CHAR(10) NOT NULL, ADD INDEX xd (d); 32 33--echo # 34--echo # Testing changing table type (not in-place) 35--echo # 36ALTER TABLE t1 TABLE_TYPE=CSV HEADER=1 QUOTED=1; 37SELECT * FROM t1; 38SHOW CREATE TABLE t1; 39 40--echo # create an outward table used to see the t1 file 41CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='t1.csv'; 42SELECT * FROM t2; 43 44--echo # 45--echo # Testing changing engine 46--echo # 47DROP TABLE t1; 48CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT; 49INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three'); 50ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d); 51ALTER TABLE t1 ENGINE = MYISAM; 52SHOW CREATE TABLE t1; 53SHOW INDEX FROM t1; 54SELECT * FROM t1; 55ALTER TABLE t1 ENGINE = CONNECT TABLE_TYPE=DBF; 56SHOW CREATE TABLE t1; 57SHOW INDEX FROM t1; 58SELECT * FROM t1; 59DROP TABLE t1, t2; 60 61--echo # 62--echo # Testing ALTER on outward tables 63--echo # 64CREATE TABLE t1 (c INT NOT NULL, d CHAR(10) NOT NULL) ENGINE=CONNECT TABLE_TYPE=fix FILE_NAME='tf1.txt' ENDING=1; 65INSERT INTO t1 VALUES (1,'One'), (2,'Two'), (3,'Three'); 66SELECT * FROM t1; 67CREATE TABLE t2 (line VARCHAR(100) NOT NULL) ENGINE=CONNECT FILE_NAME='tf1.txt'; 68SELECT * FROM t2; 69 70--echo # 71--echo # Indexing works the same 72--echo # 73ALTER TABLE t1 ADD INDEX xc (c), ADD INDEX xd (d); 74SHOW INDEX FROM t1; 75SELECT d FROM t1 WHERE c = 2; 76ALTER TABLE t1 DROP INDEX xc, DROP INDEX xd; 77SHOW INDEX FROM t1; 78 79--echo # 80--echo # Other alterations do not modify the file 81--echo # 82ALTER TABLE t1 MODIFY COLUMN c CHAR(5) NOT NULL; 83SELECT * FROM t2; 84SHOW CREATE TABLE t1; 85#Wrong result 86--error ER_GET_ERRMSG 87SELECT * FROM t1; 88ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL; 89 90--echo # 91--echo # Changing column order 92--echo # 93ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL AFTER d; 94SELECT * FROM t2; 95SHOW CREATE TABLE t1; 96--echo # Wrong result 97SELECT * FROM t1; 98ALTER TABLE t1 MODIFY COLUMN c INT NOT NULL FIRST; 99--echo # What should have been done 100ALTER TABLE t1 MODIFY c INT NOT NULL FLAG=0 AFTER d, MODIFY d CHAR(10) NOT NULL FLAG=11; 101SHOW CREATE TABLE t1; 102SELECT * FROM t1; 103 104--echo # 105--echo # Changing to another engine is Ok 106--echo # However, the data file is not deleted. 107--echo # 108ALTER TABLE t1 ENGINE=ARIA; 109SHOW CREATE TABLE t1; 110set @old_sql_mode=@@sql_mode; 111set sql_mode=ignore_bad_table_options; 112SHOW CREATE TABLE t1; 113set sql_mode=@old_sql_mode; 114SELECT * from t1; 115SELECT * from t2; 116 117--echo # 118--echo # Changing back to CONNECT fails 119--echo # Sure enough, the data file was not deleted. 120--echo # 121--error ER_UNKNOWN_ERROR 122ALTER TABLE t1 ENGINE=CONNECT; 123 124--echo # 125--echo # But changing back to CONNECT succeed 126--echo # if the data file does not exist. 127--echo # 128--remove_file $MYSQLD_DATADIR/test/tf1.txt 129ALTER TABLE t1 ENGINE=CONNECT; 130SHOW CREATE TABLE t1; 131SELECT * from t1; 132SELECT * from t2; 133 134DROP TABLE t1, t2; 135 136# 137# Clean up 138# 139--remove_file $MYSQLD_DATADIR/test/tf1.txt 140