Bug #24696 Detached SQL Node allows CREATE TABLE but shouldn't
Submitted: 29 Nov 2006 14:53 Modified: 26 Dec 2006 16:32
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1 OS:Linux (ubuntu edgy)
Assigned to: CPU Architecture:Any
Tags: 5.1.13, 5.1.14bk, CREATE TABLE, detached sql node
Triage: Triaged: D2 (Serious)

[29 Nov 2006 14:53] Roland Bouman
Description:
A mysqld that acts as a SQL node in a MySQL cluster can get detached from the cluster. When it does, an error should occur when an attempt is made to create a table with a  CREATE TABLE statement, regardless of the engine. 

This is a limitation of MySQL Cluster that is known (bug #18676), although it has not yet been documented.

However, it seems that this limitation has been (accidentally?) lifted in mysql 5.1.13.

A create table statement results in a warning:

Error (Code 1296): Got error 4009 'Cluster Failure' from NDB

But is created none the less.

A create table like statement also creates the table, and does not even give a warning

How to repeat:
Create a cluster, start it up, connect a SQL Node, shutdown the cluster, leave the SQL node running. Then do:

mysql> create table city5 (id int);                                                                                                                                                                          
Query OK, 0 rows affected, 1 warning (0.02 sec)

Error (Code 1296): Got error 4009 'Cluster Failure' from NDB
mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB | 
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table city5;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| city5 | CREATE TABLE "city5" (
  "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

With LIKE:

mysql> create table city8 like city6;
Query OK, 0 rows affected (0.01 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> show create table city8;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| city8 | CREATE TABLE "city8" (
  "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Don't allow the table to be created (for reasons outlined in bug #18676