Description:
After an ALTER TABLE fails with error 4335, the temporary table is still there and it's getting backed up.
Backup should really be ignoring any kind of temporary tables, not sure how but checking the name starting with a '#' might already help? Also, ndb_restore should ignore them.
How to repeat:
mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, ch CHAR(20), PRIMARY KEY(id)) ENGINE=NDB;
Now change a bit the table with ALTER TABLE. The following is probably not really smart, but it shows the problem:
mysql> ALTER TABLE t1 ADD KEY(id), DROP PRIMARY KEY;
ERROR 1005 (HY000): Can't create table 'test.#sql-39f7_2' (errno: 4335)
mysql> SHOW WARNINGS;
| Error | 1296 | Got error 4335 'Only one autoincrement column allowed per table. Having a table without primary key uses an autoincr' from NDB |
| Error | 1005 | Can't create table 'test.#sql-39f7_2' (errno: 4335) |
This is all fine. But now take a backup:
ndb_mgm> START BACKUP;
Re-initialize the cluster so it's empty and try to restore the backup, like:
shell> ndb_restore -m -r -n 3 -b 2 /cluster/BACKUP/BACKUP-2/
This will fail with following error:
Create table `test/def/#sql-319f_2` failed: 4335: Only one autoincrement column allowed per table. Having a table without primary key uses an autoincremented hidden key, i.e. a table without a primary key can not have an autoincremented column
Suggested fix:
- Clean up after the error
- Ignore any lingering temporary tables
