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
Tags: cluster, create, Failure, Fragments, ghost

[26 Jan 2011 18:48] Daniel Smythe
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.
[27 Jan 2011 13:27] Bugs System
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()
[27 Jan 2011 13:34] Bugs System
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)
[27 Jan 2011 14:12] Bugs System
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)
[27 Jan 2011 14:13] Bugs System
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
[27 Jan 2011 15:03] Frazer Clement
Comments sent via email
[28 Jan 2011 10:33] Jonas Oreland
pushed into 7.0.22 and 7.1.11
[28 Jan 2011 15:10] Jon Stephens
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.