Bug #18865 Table definitions "stay" even after a ndbd --initial
Submitted: 6 Apr 2006 19:29 Modified: 26 Dec 2006 16:33
Reporter: Kris Buytaert (Candidate Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-4.1 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: 4.1->
Triage: Triaged: D3 (Medium)

[6 Apr 2006 19:29] Kris Buytaert
Description:
While trying to fully reinitialise my debug enviroment and therefore deleting the full ndb_X directories etc on both nodes and starting ndbd --initial I ran in to the following issues.

mysql> CREATE LOGFILE GROUP lg1
    -> ADD UNDOFILE 'undofile.dat'
    -> INITIAL_SIZE 16M
    -> UNDO_BUFFER_SIZE = 1M
    -> ENGINE = NDB;
Query OK, 0 rows affected (0.78 sec)

mysql> CREATE TABLESPACE ts1
    -> ADD DATAFILE 'datafile.dat'
    -> USE LOGFILE GROUP lg1
    -> INITIAL_SIZE 12M
    -> ENGINE NDB;
Query OK, 0 rows affected (0.96 sec)

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> desc t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> show tables;
Empty set (0.04 sec)

!! I didn't do anything here but WAIT.

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
Query OK, 0 rows affected (1.15 sec)

The strange thing however is that if I wait a couple of seconds then try to recreate the table again it works and I get no errors anymore.

How to repeat:
mysql> CREATE TABLESPACE ts1
    -> ADD DATAFILE 'datafile.dat'
    -> USE LOGFILE GROUP lg1
    -> INITIAL_SIZE 12M
    -> ENGINE NDB;
Query OK, 0 rows affected (0.96 sec)

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
rm -rf /var/lib/mysql-cluster/ndb* 
ndbd --initial

mysql> CREATE TABLESPACE ts1
    -> ADD DATAFILE 'datafile.dat'
    -> USE LOGFILE GROUP lg1
    -> INITIAL_SIZE 12M
    -> ENGINE NDB;
Query OK, 0 rows affected (0.96 sec)

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;

Then run into the error.
[10 Apr 2006 10:05] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.1.7-beta, and inform about the results.
[10 Apr 2006 10:10] Kris Buytaert
I`m currently trying to build 5.1.7 on my test platforms (Suse 8.2) as there are no working rpm's available.
[12 May 2006 8:30] Valeriy Kravchuk
Have you got any results with 5.1.7? Anyway, please, try to repeat with a newer version, 5.1.9 (or wait for a couple of days and use 5.1.10, to be released soon). Inform about the results.
[12 May 2006 8:43] Kris Buytaert
Still on my todolist ..
[24 May 2006 13:57] Kris Buytaert
Upgraded my platform to 5.1.9  I can repeat the bug here .

-bash-3.00# ndbd --initial

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @172.16.32.7  (Version: 5.1.9, Nodegroup: 0, Master)
id=3    @172.16.32.107  (Version: 5.1.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @172.16.32.1  (Version: 5.1.9)

[mysqld(API)]   3 node(s)
id=4    @172.16.32.107  (Version: 5.1.9)
id=5    @172.16.32.7  (Version: 5.1.9)
id=6 (not connected, accepting connect from any host)

-bash-3.00# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.9-beta-log

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

mysql> use test;
Database changed
mysql> CREATE LOGFILE GROUP lg1
    -> ADD UNDOFILE 'undofile.dat'
    -> INITIAL_SIZE 16M
    -> UNDO_BUFFER_SIZE = 1M
    -> ENGINE = NDB;
Query OK, 0 rows affected (1.20 sec)

mysql> CREATE TABLESPACE ts1
    -> ADD DATAFILE 'datafile.dat'
    -> USE LOGFILE GROUP lg1
    -> INITIAL_SIZE 12M
    -> ENGINE NDB;
Query OK, 0 rows affected (0.99 sec)

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int,
    -> primary key(a), index(a,b))
    -> TABLESPACE ts1 STORAGE DISK
    -> engine=ndb;
ERROR 1050 (42S01): Table 't1' already exists

 CREATE TABLE t1 (a int, b int, c int, d int, e int, primary key(a), index(a,b)) TABLESPACE ts1 STORAGE DISK engine=ndb;
ERROR 1050 (42S01): Table 't1' already exists
mysql> show tables;
Empty set (0.03 sec)

mysql> CREATE TABLE t1 (a int, b int, c int, d int, e int, primary key(a), index(a,b)) TABLESPACE ts1 STORAGE DISK engine=ndb;
Query OK, 0 rows affected (1.23 sec)
[31 May 2006 13:19] Tomas Ulin
the frm file will still exist in the mysqld

a create table t1 will only look at if there is and frm... and then in this case issue the error

the subsequent desc and show tables will trigger the mysqld to contact ndb and discover that the table is no longer around...

from 5.1.10 the behaviour is somewhat different in that it will sync ndb and the mysqld on startup so you should not experience this anymore.

To avoid this in previous versions, issue a show tables first, after a --initial, that will sync ndb and the mysqld

BR,

Tomas