MySQL Bugs: #59756: NDB tables 'ghosted' if CREATE TABLE fails due to too many fragments error

Bug #59756	NDB tables 'ghosted' if CREATE TABLE fails due to too many fragments error
Submitted:	26 Jan 2011 18:48	Modified:	28 Jan 2011 15:12
Reporter:	Daniel Smythe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	7.1.9	OS:	Any
Assigned to:	Jonas Oreland	CPU Architecture:	Any
Tags:	cluster, create, Failure, Fragments, ghost

Description:
If a CREATE TABLE fails due to invalid or impossible number of fragments:

| Error | 1296 | Got error 1224 'Too many fragments' from NDB |
| Error | 1005 | Can't create table 'test.test' (errno: 140)  |

This table is now 'ghosted' and can not be successfully created unless it does not contain any ordered indexes, or a DROP TABLE is executed first. 

This DROP TABLE errors, however it does clear up the issue and the table may successfully be created so long as it has a valid number of fragments.

The invalid table can be created in a number of ways, one of which is outlined in Bug #59751 due to a mis-calculation in the number of fragments, however a simpler method is below.

It is also of note, that while the table is 'ghosted' in this scenario, there is no .frm file, and neither ndb_desc nor ndb_show_tables reports the table.

How to repeat:
1. Start a simple 2 node cluster.
2. Execute the following SQL from a mysqld node:

mysql> CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=ndb PARTITION BY KEY(id) PARTITIONS 24;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 140)

mysql> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Error | 1296 | Got error 1224 'Too many fragments' from NDB |
| Error | 1005 | Can't create table 'test.t1' (errno: 140)    |
+-------+------+----------------------------------------------+
2 rows in set (0.00 sec)

At this point, you cannot create this table unless it either contains no ordered indexes, or a DROP TABLE is issued first. For example:

mysql> CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=ndb;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 155)
mysql> show errors;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1005 | Can't create table 'test.t1' (errno: 155) |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

However this will work:

mysql> CREATE TABLE t1 ( id INT, PRIMARY KEY(id) USING HASH ) ENGINE=ndb;
Query OK, 0 rows affected (0.43 sec)

Also this will work:

mysql> CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=ndb PARTITION BY KEY(id) PARTITIONS 24;
ERROR 1005 (HY000): Can't create table 'test.t1' (errno: 140)

mysql> DROP TABLE t1;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Error | 1296 | Got error 723 'No such table existed' from NDB |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 ( id INT PRIMARY KEY ) ENGINE=ndb;
Query OK, 0 rows affected (0.60 sec)

Suggested fix:
1) Invalidate the table definition when the bad table creation fails at schema-trans commit time.

2) Potentially force a refresh of the table cache definition just after table create and before index create to guard against this in the future.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129762

4144 Jonas Oreland	2011-01-27
      ndb - bug#59756 - Don't put table into global cache until schema trans has been successfully committed. Add interface to get ObjectId back from createTable()

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:jonas@mysql.com-20110127133104-xkuu2x79cxsu5abh) (version source revid:jonas@mysql.com-20110127132307-08gtx11qhcoqpsb2) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.22 (revid:jonas@mysql.com-20110127140839-yzcmimmoqef56rmp) (version source revid:jonas@mysql.com-20110127140839-yzcmimmoqef56rmp) (merge vers: 5.1.51-ndb-7.0.22) (pib:24)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129778

4146 Jonas Oreland	2011-01-27
      ndb - (bug#59756) latest patch revealed the setTemporary does not work...which it doesnt...but was previously silently ignored..but now is brought to light

Comments sent via email

pushed into 7.0.22 and 7.1.11

Documented fix in the NDB-7.0.22 and 7.1.11 changelogs, as follows:

        When a CREATE TABLE statement failed due to NDB error 1224 (Too
        many fragments), it was not possible to create the table
        afterward unless it had no ordered indexes, or a DROP TABLE was
        issued first, even if the subsequent CREATE TABLE was valid.

Closed.