Bug #37921 sql node not aware that table does not exist on data nodes
Submitted: 7 Jul 2008 10:21 Modified: 6 Nov 2008 19:50
Reporter: Bogdan Kecman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:6.x.x OS:Any
Assigned to: Martin Skold CPU Architecture:Any
Tags: initial

[7 Jul 2008 10:21] Bogdan Kecman
Description:
if data nodes are started with --initial while sql nodes have the "old" frm files in place, sql node is not aware that tables does not exist on the cluster

How to repeat:
1. setup cluster on single box (1 data node, 1sql node, 1mgm node)
2. start cluster + sql node
3. execute script:

mysql -uroot test -e "create table test.t1 (a int) engine=ndbcluster;"
mysql -uroot test -e "insert into test.t1 values (1);"
killall -9 mysqld_safe
killall -9 mysqld
killall -9 ndbd

libexec/ndbd --initial
bin/mysqld_safe &

mysql -uroot test -e "create table if not exists test.t1 (a int) engine=ndbcluster;"
mysql -uroot test -e "select * from test.t1;"

and you will get the "Table 'test.t1' doesn't exist" error message.

Suggested fix:
in the shown case "if not exist" should not only check if frm file exists but if the table actually exist on the ndbcluster. The SQL nodes should be aware if the table is removed via api or via --initial
[4 Sep 2008 9:07] Martin Skold
The obvious work around is to do a: 
show tables;
This discovers new tables and removes
stale .frm files.
[7 Sep 2008 19:57] 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/53458

2660 Martin Skold	2008-09-07
      Bug #37921  sql node not aware that table does not exist on data nodes: Adding check for stale frm files in mysql_create_table
[6 Nov 2008 19:50] Jon Stephens
Documented bugfix in the NDB-6.2.16 and NDB-6.3.18 changelogs as follows:

        After a forced shutdown and initial restart of the cluster, it was
        possible for SQL nodes to retain .FRM files corresponding to NDBCLUSTER
        tables that had been dropped, and thus to be unaware that these tables
        no longer existed. In such cases, attempting to re-create the tables
        using CREATE TABLE IF NOT EXISTS could fail with a spurious Table
        ... doesn't exist error.