| 1 |
CREATE TABLE t1 ( |
| 2 |
a int PRIMARY KEY, |
| 3 |
b varchar(32), |
| 4 |
c int NOT NULL |
| 5 |
) ENGINE = NDB; |
| 6 |
INSERT INTO t1 VALUES |
| 7 |
(1, "MySQL Server with NDB", 3), |
| 8 |
(3, "The third row", 9), |
| 9 |
(37, "And of course number 37", 37); |
| 10 |
SELECT a FROM t1 WHERE c > 8 ORDER by c; |
| 11 |
a |
| 12 |
3 |
| 13 |
37 |
| 14 |
ALTER TABLE t1 ALGORITHM=INPLACE, ADD INDEX c_idx1(c); |
| 15 |
SELECT a FROM t1 WHERE c > 8 ORDER by c; |
| 16 |
a |
| 17 |
3 |
| 18 |
37 |
| 19 |
DROP TABLE t1; |
| 20 |
# |
| 21 |
# Bug#30766579 ADDING AN INDEX WITH INPLACE GENERATES |
| 22 |
# - test adding an index on first pk column using inplace alter |
| 23 |
# |
| 24 |
CREATE TABLE t1 ( |
| 25 |
col1 varbinary(80) NOT NULL, |
| 26 |
pk2 varchar(46) NOT NULL, |
| 27 |
pk1 bigint(20) NOT NULL, |
| 28 |
col2 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 29 |
col3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 30 |
col4 varchar(320) CHARACTER SET utf8 NOT NULL, |
| 31 |
col5 varbinary(16) NOT NULL, |
| 32 |
col6 int(11) NOT NULL, |
| 33 |
col7 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 34 |
col8 bigint(20) NOT NULL, |
| 35 |
col9 timestamp NULL DEFAULT NULL, |
| 36 |
col10 int(11) NOT NULL, |
| 37 |
col11 bit(1) NOT NULL, |
| 38 |
col12 binary(32) NOT NULL, |
| 39 |
col13 bit(1) NOT NULL DEFAULT b'0', |
| 40 |
col14 varchar(32) CHARACTER SET utf8 DEFAULT NULL, |
| 41 |
col15 bigint(10) DEFAULT NULL, |
| 42 |
col16 varchar(3) DEFAULT NULL, |
| 43 |
col17 bit(1) NOT NULL DEFAULT b'0', |
| 44 |
col18 bit(1) NOT NULL DEFAULT b'0', |
| 45 |
col19 varbinary(250) DEFAULT NULL, |
| 46 |
col20 varchar(20) DEFAULT NULL, |
| 47 |
col21 varchar(3) DEFAULT NULL, |
| 48 |
col22 int(11) DEFAULT NULL, |
| 49 |
PRIMARY KEY (pk1, pk2), |
| 50 |
UNIQUE KEY ix_pk2 (pk2), |
| 51 |
KEY ix_t1_col2 (col2), |
| 52 |
KEY ix_t1_col3 (col3), |
| 53 |
KEY ix_t1_pk1_col20 (pk1,col20), |
| 54 |
KEY ix_t1_pk1_col6 (pk1,col6) |
| 55 |
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
| 56 |
PARTITION BY KEY (pk1); |
| 57 |
Warnings: |
| 58 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 59 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 60 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 61 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 62 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 63 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 64 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 65 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 66 |
# Show indexes on t1 |
| 67 |
SELECT index_name, columns FROM ndbinfo.index_columns |
| 68 |
WHERE table_name = 't1' ORDER BY index_name; |
| 69 |
index_name columns |
| 70 |
ix_pk2 pk2 |
| 71 |
ix_pk2$unique pk2 |
| 72 |
ix_t1_col2 col2 |
| 73 |
ix_t1_col3 col3 |
| 74 |
ix_t1_pk1_col20 pk1,col20 |
| 75 |
ix_t1_pk1_col6 pk1,col6 |
| 76 |
PRIMARY pk1,pk2 |
| 77 |
ALTER TABLE t1 ALGORITHM = INPLACE, ADD INDEX ix_test (pk1); |
| 78 |
# Show that new index 'ix_test' was added to t1 |
| 79 |
SELECT index_name, columns, index_type FROM ndbinfo.index_columns |
| 80 |
WHERE table_name = 't1' AND index_name = 'ix_test' ORDER BY index_name; |
| 81 |
index_name columns index_type |
| 82 |
ix_test pk1 6 |
| 83 |
DROP TABLE t1; |