Description:
Hello,
When attempting to add a column and its associated index to an NDB table online in the same ALTER TABLE command the operation is rejected.
If I split the operations into two different commands the ALTER succeeds. It makes no difference if I use ALTER ONLINE... or ALTER... ALGORITHM=INPLACE syntax.
Note: The same ALTER... ALGORITHM=INPLACE against InnoDB does not suffer from this. (see below)
How to repeat:
mysql> CREATE TABLE ndb_alter_test (a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE NDBCLUSTER;
Query OK, 0 rows affected (4.94 sec)
mysql> ALTER TABLE ndb_alter_test ADD COLUMN c INT UNSIGNED COLUMN_FORMAT DYNAMIC, ADD INDEX `idx_alter_test_c` (c), ALGORITHM=INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
mysql> ALTER TABLE ndb_alter_test ADD COLUMN c INT UNSIGNED COLUMN_FORMAT DYNAMIC, ALGORITHM=INPLACE; ALTER TABLE ndb_alter_test ADD INDEX `idx_alter_test_c` (c), ALGORITHM=INPLACE;
Query OK, 0 rows affected (2.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (3.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE ndb_alter_test\G
*************************** 1. row ***************************
Table: ndb_alter_test
Create Table: CREATE TABLE `ndb_alter_test` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(10) DEFAULT NULL,
`c` int(10) unsigned /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_alter_test_c` (`c`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
## InnoDB example.
mysql> CREATE TABLE ib_alter_test (a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(100)) ENGINE = INNODB;
Query OK, 0 rows affected (1.44 sec)
mysql> ALTER TABLE ib_alter_test ADD COLUMN c INT UNSIGNED, ADD INDEX `idx_ib_alter_test` (`c`), ALGORITHM=INPLACE;
Query OK, 0 rows affected (1.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table ib_alter_test\G
*************************** 1. row ***************************
Table: ib_alter_test
Create Table: CREATE TABLE `ib_alter_test` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(100) DEFAULT NULL,
`c` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx_ib_alter_test` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Suggested fix:
Perform all ONLINE-able operations appearing in the same command together if possible.
© 2024, Oracle Corporation and/or its affiliates
Description: Hello, When attempting to add a column and its associated index to an NDB table online in the same ALTER TABLE command the operation is rejected. If I split the operations into two different commands the ALTER succeeds. It makes no difference if I use ALTER ONLINE... or ALTER... ALGORITHM=INPLACE syntax. Note: The same ALTER... ALGORITHM=INPLACE against InnoDB does not suffer from this. (see below) How to repeat: mysql> CREATE TABLE ndb_alter_test (a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) ENGINE NDBCLUSTER; Query OK, 0 rows affected (4.94 sec) mysql> ALTER TABLE ndb_alter_test ADD COLUMN c INT UNSIGNED COLUMN_FORMAT DYNAMIC, ADD INDEX `idx_alter_test_c` (c), ALGORITHM=INPLACE; ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. mysql> ALTER TABLE ndb_alter_test ADD COLUMN c INT UNSIGNED COLUMN_FORMAT DYNAMIC, ALGORITHM=INPLACE; ALTER TABLE ndb_alter_test ADD INDEX `idx_alter_test_c` (c), ALGORITHM=INPLACE; Query OK, 0 rows affected (2.06 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (3.49 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE ndb_alter_test\G *************************** 1. row *************************** Table: ndb_alter_test Create Table: CREATE TABLE `ndb_alter_test` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(10) DEFAULT NULL, `c` int(10) unsigned /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, PRIMARY KEY (`a`), KEY `idx_alter_test_c` (`c`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ## InnoDB example. mysql> CREATE TABLE ib_alter_test (a INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(100)) ENGINE = INNODB; Query OK, 0 rows affected (1.44 sec) mysql> ALTER TABLE ib_alter_test ADD COLUMN c INT UNSIGNED, ADD INDEX `idx_ib_alter_test` (`c`), ALGORITHM=INPLACE; Query OK, 0 rows affected (1.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table ib_alter_test\G *************************** 1. row *************************** Table: ib_alter_test Create Table: CREATE TABLE `ib_alter_test` ( `a` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` varchar(100) DEFAULT NULL, `c` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`a`), KEY `idx_ib_alter_test` (`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Suggested fix: Perform all ONLINE-able operations appearing in the same command together if possible.