Bug #39033 Table disappear

• Submitted: 26 Aug 2008 6:10
• Modified: 28 Aug 2008 7:41
• Reporter: Feelora Gee
• Status: Duplicate
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 5.1.24-ndb-6.3.16
• OS: Linux
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: alter, discovery, ndb

[26 Aug 2008 6:10] Feelora Gee

Description:
two data node, two mysqlnode, I create a table and alter it. show tables during altering.

DROP TABLE if exists t2;
CREATE TABLE t2
(
page_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (page_id)
)
TABLESPACE ndtsp STORAGE DISK ENGINE= ndbcluster;
insert into t2 (page_id) values (1);
insert into t2 (page_id) values (2);
insert into t2 (page_id) values (3);
insert into t2 (page_id) values (4);
insert into t2 (page_id) values (5);
insert into t2 (page_id) values (6);

in mysql node1:
use test;
flush tables;
alter table t2 add test_column int(11) not null default '1';
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

show tables in mysqlnode1
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;

then:
ERROR 1146 (42S02) at line 13: Table 'test.t2' doesn't exist

the table disappeared???!!!

error log:
080826 13:01:02 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:33:19 [Note] NDB_SHARE: trailing share ./test/t2(connect_count: 0) released after NSS_DROPPED check at connect_count: 0
080826 13:34:37 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:40 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:40 [Warning] NDB_SHARE: ./test/t2 already exists  use_count=1. Moving away for safety, but possible memleak.
080826 13:34:43 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:46 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:49 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:53 [Note] NDB Binlog: RENAME Event: REPL$test/t2
080826 13:34:57 [Note] NDB Binlog: RENAME Event: REPL$test/t2

How to repeat:
DROP TABLE if exists t2;
CREATE TABLE t2
(
page_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (page_id)
)
TABLESPACE ndtsp STORAGE DISK ENGINE= ndbcluster;
insert into t2 (page_id) values (1);
insert into t2 (page_id) values (2);
insert into t2 (page_id) values (3);
insert into t2 (page_id) values (4);
insert into t2 (page_id) values (5);
insert into t2 (page_id) values (6);

in mysql node1:
use test;
flush tables;
alter table t2 add test_column int(11) not null default '1';
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

flush tables;
alter table t2 drop test_column;
alter table t2 drop key indx_page_def_test;
alter table t2 add test_column int(11) not null default '1';
alter table t2 drop test_column2;
alter table t2 add unique key indx_page_def_test (page_id, test_column);
alter table t2 add test_column2 int(11) not null default '1';
alter table t2 drop key indx_page_def_test;
alter table t2 add unique key indx_page_def_test (page_id, test_column, test_column2);

show tables in mysqlnode1
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;
show tables;

[26 Aug 2008 10:46] Geert Vanderkelen

Verified using MySQL Cluster 6.3.16 and 6.3bzr. Table is indeed gone.
Doing lots of SHOW TABLES consequently triggers the problem. So I guess doing it less, is kind of a workaround to this. Doing it once a second for example didn't prove to be a problem.

I used following to repeat it:
 shell> while(true); do /data1/mysql/ndb-6.3bzr/bin/mysql -uroot -NB -e 'select now();show tables' test | tee -a showtables.log; done

While the ALTER operations are running.

shell> ndb_show_tables -d test t2
id    type                 state    logging database     schema   name
10    OrderedIndex         Online   No      sys          def      PRIMARY

And after:

shell> ndb_show_tables -d test t2
listIndexes - 723: No such table existed

[26 Aug 2008 13:35] Geert Vanderkelen

BTW, happens with in-memory tables too, so nothing to do with disk-based tables.
My setup was also a single data node setup, with 1 mysqld attached.

Putting workaround as W2.

[27 Aug 2008 1:42] Feelora Gee

But I am afraid not only the "show tables",maybe "select" or other operation may cause table lose.

Please help to dig out the root reason.

[28 Aug 2008 7:41] Martin Skold

Duplicate of bug#37419