| 1 |
############################################################# |
| 2 |
# Author: Martin |
| 3 |
# Date: 2018-06-27 |
| 4 |
# Purpose: basic online alter column test |
| 5 |
############################################################## |
| 6 |
--source include/have_multi_ndb.inc |
| 7 |
--source suite/ndb/include/backup_restore_setup.inc |
| 8 |
|
| 9 |
# Create view which displays all user tables. |
| 10 |
# NOTE! Uses a format which is backward compatible with the output |
| 11 |
# from "ndb_show_tables" previously used by this test - for example names |
| 12 |
# have surrounding quotes. |
| 13 |
--disable_query_log ONCE |
| 14 |
CREATE VIEW ndb_show_tables_results AS |
| 15 |
SELECT |
| 16 |
id, |
| 17 |
/* Hardcoded since only user tables are selected */ |
| 18 |
'UserTable' AS type, |
| 19 |
/* Table name is last part of fq_name */ |
| 20 |
CONCAT("'", CONCAT(SUBSTRING_INDEX(fq_name, "/", -1), "'")) AS name |
| 21 |
FROM ndbinfo.dict_obj_info WHERE type = 2 /* User table */; |
| 22 |
|
| 23 |
###################################### |
| 24 |
# online alter column |
| 25 |
###################################### |
| 26 |
--echo ******************************* |
| 27 |
--echo * online alter column tests |
| 28 |
--echo ******************************* |
| 29 |
|
| 30 |
CREATE TABLE t1 (a INT UNSIGNED KEY, b INT UNSIGNED, e INT UNIQUE, f TEXT) ROW_FORMAT=DYNAMIC ENGINE NDB; |
| 31 |
INSERT INTO t1 values (1,1,1,"One"); |
| 32 |
|
| 33 |
set @t1_id = (select id from ndb_show_tables_results where name like '%t1%' and type like '%UserTable%'); |
| 34 |
|
| 35 |
--echo *************************************** |
| 36 |
--echo * Alter Table online change column name |
| 37 |
--echo *************************************** |
| 38 |
|
| 39 |
ALTER TABLE t1 CHANGE b c INT UNSIGNED ; |
| 40 |
|
| 41 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%' and type like '%UserTable%'; |
| 42 |
|
| 43 |
--source show_attributes.inc |
| 44 |
|
| 45 |
INSERT INTO t1 values (2,2,2,"Two"); |
| 46 |
SELECT * FROM t1 ORDER BY a; |
| 47 |
|
| 48 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE c D INT UNSIGNED; |
| 49 |
|
| 50 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 51 |
|
| 52 |
--source show_attributes.inc |
| 53 |
|
| 54 |
INSERT INTO t1 values (3,3,3,"Three"); |
| 55 |
SELECT * FROM t1 ORDER BY a; |
| 56 |
|
| 57 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE D d INT UNSIGNED; |
| 58 |
|
| 59 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 60 |
|
| 61 |
--source show_attributes.inc |
| 62 |
|
| 63 |
INSERT INTO t1 values (4,4,4,"Four"); |
| 64 |
SELECT * FROM t1 ORDER BY a; |
| 65 |
|
| 66 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE f t TEXT; |
| 67 |
|
| 68 |
select name from ndb_show_tables_results where id = @t1_id and name like '%t1%'#and type like '%UserTable%'; |
| 69 |
|
| 70 |
--source show_attributes.inc |
| 71 |
|
| 72 |
INSERT INTO t1 values (5,5,5,"Five"); |
| 73 |
SELECT * FROM t1 ORDER BY a; |
| 74 |
|
| 75 |
ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra1 VARCHAR(10); |
| 76 |
ALTER TABLE t1 ALGORITHM=INPLACE, ADD extra2 VARCHAR(10); |
| 77 |
UPDATE t1 set extra1="XXXX", extra2="YYYY"; |
| 78 |
SELECT * FROM t1 ORDER BY a; |
| 79 |
# Swap column names |
| 80 |
ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN extra1 TO extra2, RENAME COLUMN extra2 TO extra1; |
| 81 |
SELECT * FROM t1 ORDER BY a; |
| 82 |
|
| 83 |
# Restart cluster nodes |
| 84 |
--exec $NDB_MGM -e "all restart" >> $NDB_TOOLS_OUTPUT |
| 85 |
# Wait for all nodes to start |
| 86 |
--exec $NDB_WAITER |
| 87 |
|
| 88 |
# |
| 89 |
# Wait until the connection to the |
| 90 |
# cluster has been restored or timeout occurs |
| 91 |
# |
| 92 |
connection default; |
| 93 |
--disable_result_log |
| 94 |
--disable_query_log |
| 95 |
--source include/ndb_not_readonly.inc |
| 96 |
--enable_result_log |
| 97 |
--enable_query_log |
| 98 |
|
| 99 |
SELECT * FROM t1 ORDER BY a; |
| 100 |
|
| 101 |
############################## |
| 102 |
# Backup and restore section # |
| 103 |
############################## |
| 104 |
--echo ********************************* |
| 105 |
--echo * Backup and restore tables w/ changed column |
| 106 |
--echo ********************************* |
| 107 |
|
| 108 |
--source include/ndb_backup.inc |
| 109 |
|
| 110 |
DROP TABLE t1; |
| 111 |
|
| 112 |
--exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT |
| 113 |
--exec $NDB_RESTORE -b $the_backup_id -n 2 -r --print_meta --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT |
| 114 |
--exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT |
| 115 |
|
| 116 |
SELECT * FROM t1 ORDER BY a; |
| 117 |
|
| 118 |
--let $ndb_describe_table= t1 |
| 119 |
--let $ndb_describe_indexes= 1 |
| 120 |
--let $ndb_describe_blobs= 1 |
| 121 |
--source suite/ndb/include/ndb_describe_table.inc |
| 122 |
|
| 123 |
--echo ********************************* |
| 124 |
--echo * Error tests |
| 125 |
--echo ********************************* |
| 126 |
|
| 127 |
# Duplicate name |
| 128 |
--error ER_DUP_FIELDNAME |
| 129 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE d a INT UNSIGNED; |
| 130 |
|
| 131 |
# Cannot rename primary key column |
| 132 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON |
| 133 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE a pk INT UNSIGNED; |
| 134 |
|
| 135 |
# Cannot rename indexed column |
| 136 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON |
| 137 |
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE e ue INT; |
| 138 |
|
| 139 |
# Cannot rename column and add column |
| 140 |
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON |
| 141 |
ALTER TABLE t1 ALGORITHM=INPLACE, RENAME COLUMN D TO D2, ADD t2 CHAR(10); |
| 142 |
DROP TABLE t1; |
| 143 |
|
| 144 |
--source suite/ndb/include/backup_restore_cleanup.inc |
| 145 |
--remove_file $NDB_TOOLS_OUTPUT |
| 146 |
|
| 147 |
--disable_query_log ONCE |
| 148 |
DROP VIEW ndb_show_tables_results; |