MySQL Bugs: #18676: Missleading error message when trying to create table when cluster is down

Bug #18676	Missleading error message when trying to create table when cluster is down
Submitted:	31 Mar 2006 2:37	Modified:	25 May 2007 8:16
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.0 5.1	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Guangbao Ni	CPU Architecture:	Any

Description:
If you have the following in the my.cnf:

ndbcluster
ndb-connectstring=host:port  # location of MGM node

and you do not have a cluster up and running to connect to when you start your mysqld, then any table you try to create no matter which database you use will return "Table 'x' already exists". Using the ENGINE=MyISAM or InnoDB does not help.

create table t1 (f int);
ERROR 1050 (42S01): Table 't1' already exists

If you comment out the ndbcluster parts of the my.cnf, then that table is created w/o issue.

How to repeat:
1) Add the following to my.cnf changing the host and port for your systems:

ndbcluster
ndb-connectstring=host:port  # location of MGM node

2) start the mysqld without the cluster up and running.

3) Try to create a table

4) Comment out items for step 1

5) restart the mysqld

6) Try to create a table

This is actually by design.

Suppose you have a mysqld that is not connected to cluster
And create a table T1 (myisam), what should now happen if
  thet mysqld connects and there already is a table in the cluster called T1

That's serious data corruption.
Therefor is any create table prevented when your not connected.
However, I thought that the error message was 4009 (i.e not connected to cluster)

---

Bottom line. I think that this is not a bug.
  (or possibly a P4, for bad error message)

---

ps. tomas has also advocated that this should be allowed.
but having incosistent data is _the_ worst kind of error.
so we've been discussing having a property per database instead.
i.e something like "create database jonas engine = ndb", and then you would
  not be allowed to create table in that database.
but even if something like that probably would work, it's a feature request
  that has to be thought about carefully before implementing...

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated Cluster chapter sections discussing configuration with warning that this is intentional behaviour. Closed.

You can not keep a user from creating an innodb or myisam table if they are using and engine=innodb or engine=myisam. Therefore, I am reopening this bug and esqulating it

Unless there's a way 

(a) for a mysqld to know what tables exist in which databases of a cluster when the cluster isn't running,

or

(b) to rename or ignore any schema objects local to the mysqld that clash with schema objects in the cluster when the mysqld is connected to the cluster, 

I don't see how we can allow this.

Since (a) is impossible AFAICT and (b) would likely lead to complaints of "I can't access my tables now that I've connected to the cluster" and/or trying to figure out a foolproof way to avoid collisions, I agree with Jonas.

Especially given that the workaround (comment out two lines in my.cnf and exercise some common sense) is pretty simple, I don't see this as being in urgent need of a "fix" in any case.

That's my 2 satang, at least.

giving a message of "Table 't1' already exists " does not make sense. Maybe "Can't connect to cluster", but not the other.

Thats a good idea (which I suggested in my first post :-))
Changing subject.
Reopening...

some info is there in the warnings

master> create table t1 (a int) engine=myisam;
ERROR 1050 (42S01): Table 't1' already exists
master> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB | 
| Error | 1050 | Table 't1' already exists                 | 
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)

lowering prio on this as it is a correction of an error message

Note also handle this
  when cluster has been restarted (--initial)

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/15394

ChangeSet@1.2259, 2006-11-16 11:51:47+08:00, gni@dev3-127.(none) +7 -0
  BUG#18676 when cluster stoage engine is down, mysqld can't normally create talbe with other storage engine.

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/16067

ChangeSet@1.2259, 2006-11-29 15:15:30+08:00, gni@dev3-127.(none) +4 -0
  BUG#18676 when cluster stoage engine is down, mysqld can't normally create talbe with other storage engine.

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/18223

ChangeSet@1.2259, 2007-01-17 10:20:25+08:00, gni@dev3-221.dev.cn.tlan +1 -0
  BUG#18676 when cluster stoage engine is down, mysqld can't normally create talbe with other storage engine.

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/18833

ChangeSet@1.2259, 2007-01-26 14:37:31+08:00, gni@dev3-221.dev.cn.tlan +1 -0
  BUG#18676 when cluster stoage engine is down, mysqld can't normally create talbe with other storage engine.

Suggested patch

Attachment: bug18676.patch (text/x-patch), 5.06 KiB.

With my patch:

$ killall ndbd
$ ../client/mysql -u root test -S var/tmp/master.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a int) engine=ndb;
ERROR 157 (HY000): Could not connect to storage engine
mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB | 
| Error |  157 | Could not connect to storage engine       | 
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> create table t1 (a int) engine=myisam;
ERROR 157 (HY000): Could not connect to storage engine
mysql> show warnings;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB | 
| Error |  157 | Could not connect to storage engine       | 
+-------+------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql>

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/23645

ChangeSet@1.2478, 2007-04-03 18:44:29+08:00, gni@dev3-221.dev.cn.tlan +4 -0
  BUG#18676 when cluster storage engine is down, misleading error message on create table with 4009.

push into mysql-5.0-ndb-bj tree about 5.0.38 version

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/23882

ChangeSet@1.2491, 2007-04-05 17:26:07+08:00, gni@dev3-221.dev.cn.tlan +2 -0
  BUG#18676 In order to coincide with 5.0 mysqld error code after bug#18676, Map the 4009 ndb error code to 157 mysql error code

Pushed into 5.0.40

Pushed into 5.1.18-beta

5.0.40, 5.1.18, telco 6.2.1

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.40, 5.1.18, and telco-6.2.1 changelogs.

Updated 4.1/5.0/5.1 Cluster docs to indicate that the behaviour is intentional and not a bug.

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/24471

ChangeSet@1.2491, 2007-04-13 18:11:15+08:00, gni@dev3-221.dev.cn.tlan +5 -0
  BUG#18676 In order to coincide with 5.0 mysqld error code after bug#18676, Map the 4009 ndb error code to 157 mysql error code

gni working on fix for 5.1 patch

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/24576

ChangeSet@1.2491, 2007-04-16 15:15:47+08:00, gni@dev3-221.dev.cn.tlan +6 -0
  BUG#18676 In order to coincide with 5.0 mysqld error code after bug#18676, Map the 4009 ndb error code to 157 mysql error code

Approved by stewart

push into mysql-5.1-ndb-bj tree

merge into mysql-5.1-new-ndb tree

Pushed into 5.1.19-beta

Not sure I understand what needs to be documented. What's the user-visible change accomplished by this (new?) fix, please.

Hi Jon,
  It is invisible for user, just for developer.

Jon,

is improved message when trying to CREATE TABLE (any engine) when no ndbd nodes are around (i.e. "no connection" or "cluster not up" or "cluster failed a while ago").

also much saner internal programming interface.

So perhaps an example somewhere in the manual (it seems to almost be a FAQ) and an entry in the changelog is enough. (unless we talk about this behaviour anywhere else... which i don't think we do)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

User-facing changes already documented per my previous comments. No further action taken.