Description:
After adding a new nodegroup to a cluster, it is necessary to perform ALTER ONLINE TABLE
{all-ndbcluster-tables} REORGANIZE PARTITION to redistribute table data to the new
nodes.
The statement fails for any table that does not have its primary key on the first
column.
Cluster pulled/built from telco-6.4 tree 20081208.
Workaround: Use ALTER TABLE to move/add PK as first column. This appears to redistribute
data and make subsequent REORGANIZE attempts succeed.
How to repeat:
1. Start a 2-node/2-replica cluster.
2. Create the following tables:
mysql> show create table ips\G
*************************** 1. row ***************************
Table: ips
Create Table: CREATE TABLE `ips` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`country_code` char(2) NOT NULL,
`type` char(4) NOT NULL,
`ip_address` varchar(15) NOT NULL,
`addresses` bigint(20) unsigned DEFAULT NULL,
`date` bigint(20) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table ips2\G
*************************** 1. row ***************************
Table: ips2
Create Table: CREATE TABLE `ips2` (
`country_code` char(2) NOT NULL,
`type` char(4) NOT NULL,
`ip_address` varchar(15) NOT NULL,
`addresses` bigint(20) unsigned DEFAULT NULL,
`date` bigint(20) unsigned DEFAULT NULL,
`id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table ips3\G
*************************** 1. row ***************************
Table: ips3
Create Table: CREATE TABLE `ips3` (
`country_code` char(2) NOT NULL,
`type` char(4) NOT NULL,
`ip_address` varchar(15) NOT NULL,
`addresses` bigint(20) unsigned DEFAULT NULL,
`date` bigint(20) unsigned DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
*Summary* Tables are identical except for:
ips - PK on first column
ips2 - PK on last column
ips3 - no PK
3. Insert some rows into the tables ("some" = ~50000 in this case).
4. Add new nodegroup to cluster using procedure outlined by Jonas.
5. Execute alter table to redistribute data:
mysql> show tables;
+-------------+
| Tables_in_n |
+-------------+
| ips |
| ips2 |
| ips3 |
+-------------+
3 rows in set (0.00 sec)
mysql> alter online table ips reorganize partition;
Query OK, 0 rows affected (16.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter online table ips2 reorganize partition;
ERROR 1296 (HY000): Got error 4 'Unknown error code' from NDBCLUSTER
mysql> alter online table ips3 reorganize partition;
ERROR 1296 (HY000): Got error 4 'Unknown error code' from NDBCLUSTER
As shown above, only succeeds with first table.
Suggested fix:
1. At a minimum, it should not matter which column has the PK.
2. Although we recommend that all tables have a PK (including NDB tables), it would still
be better if the PK were not actually required for this to work.
Description: After adding a new nodegroup to a cluster, it is necessary to perform ALTER ONLINE TABLE {all-ndbcluster-tables} REORGANIZE PARTITION to redistribute table data to the new nodes. The statement fails for any table that does not have its primary key on the first column. Cluster pulled/built from telco-6.4 tree 20081208. Workaround: Use ALTER TABLE to move/add PK as first column. This appears to redistribute data and make subsequent REORGANIZE attempts succeed. How to repeat: 1. Start a 2-node/2-replica cluster. 2. Create the following tables: mysql> show create table ips\G *************************** 1. row *************************** Table: ips Create Table: CREATE TABLE `ips` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `country_code` char(2) NOT NULL, `type` char(4) NOT NULL, `ip_address` varchar(15) NOT NULL, `addresses` bigint(20) unsigned DEFAULT NULL, `date` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table ips2\G *************************** 1. row *************************** Table: ips2 Create Table: CREATE TABLE `ips2` ( `country_code` char(2) NOT NULL, `type` char(4) NOT NULL, `ip_address` varchar(15) NOT NULL, `addresses` bigint(20) unsigned DEFAULT NULL, `date` bigint(20) unsigned DEFAULT NULL, `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table ips3\G *************************** 1. row *************************** Table: ips3 Create Table: CREATE TABLE `ips3` ( `country_code` char(2) NOT NULL, `type` char(4) NOT NULL, `ip_address` varchar(15) NOT NULL, `addresses` bigint(20) unsigned DEFAULT NULL, `date` bigint(20) unsigned DEFAULT NULL ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec) *Summary* Tables are identical except for: ips - PK on first column ips2 - PK on last column ips3 - no PK 3. Insert some rows into the tables ("some" = ~50000 in this case). 4. Add new nodegroup to cluster using procedure outlined by Jonas. 5. Execute alter table to redistribute data: mysql> show tables; +-------------+ | Tables_in_n | +-------------+ | ips | | ips2 | | ips3 | +-------------+ 3 rows in set (0.00 sec) mysql> alter online table ips reorganize partition; Query OK, 0 rows affected (16.74 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter online table ips2 reorganize partition; ERROR 1296 (HY000): Got error 4 'Unknown error code' from NDBCLUSTER mysql> alter online table ips3 reorganize partition; ERROR 1296 (HY000): Got error 4 'Unknown error code' from NDBCLUSTER As shown above, only succeeds with first table. Suggested fix: 1. At a minimum, it should not matter which column has the PK. 2. Although we recommend that all tables have a PK (including NDB tables), it would still be better if the PK were not actually required for this to work.