Bug #40854 SHOW TABLES removes tables from Cluster
Submitted: 19 Nov 2008 13:49 Modified: 7 Jan 2009 14:37
Reporter: Johan Andersson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:6.3.18 OS:Any
Assigned to:
Tags: show tables, tables dropped
Triage: Needs Triage: D1 (Critical)

[19 Nov 2008 13:49] Johan Andersson
Description:
two data nodes
two mysql serves
all on local host

creating the tables using a client connected to mysql server 1.
in another client connected to mysql server 2 i do "SHOW TABLES" repeatedly (i as fast as possible, so that the "SHOW TABLES" command "hangs").

After about 50 tables has been created, the cluster starts to drop them..

Here is the script to generate the tables:
cat gen.sh
#!/bin/bash
for i in `seq 1 100`;
do
       echo "create table t$i(a integer primary key) engine=ndb;"
done    

--
sh gen.sh > t.sql
gives a script t.sql with 100 tables.
then see "how to repeat"

How to repeat:
mysql client 1> 
use test;
source t.sql ;

mysql client 2>
use test;
show tables; (repeat a lot).

You will then see:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| driver         | 
| flat           | 
| subscriber     | 
| t1             | 
| t10            | 
| t11            | 
| t12            | 
| t13            | 
| t14            | 
| t15            | 
| t16            | 
| t17            | 
| t18            | 
| t19            | 
| t2             | 
| t20            | 
| t21            | 
| t22            | 
| t23            | 
| t24            | 
| t25            | 
| t26            | 
| t27            | 
| t28            | 
| t29            | 
| t3             | 
| t30            | 
| t31            | 
| t32            | 
| t33            | 
| t34            | 
| t35            | 
| t36            | 
| t37            | 
| t38            | 
| t39            | 
| t4             | 
| t40            | 
| t41            | 
| t42            | 
| t43            | 
| t44            | 
| t45            | 
| t46            | 
| t47            | 
| t48            | 
| t49            | 
| t5             | 
| t50            | 
| t51            | 
| t52            | 
| t6             | 
| t7             | 
| t8             | 
| t9             | 
+----------------+
55 rows in set (1.24 sec)

mysql> show tables;
^[[6~

Empty set (1 min 4.78 sec)

and from ndb_show_tables:
johan@stingray:~/dev$ ndb_show_tables  |grep UserTable |grep t
120   UserTable            Online   Yes     test         def      t54
108   UserTable            Online   Yes     test         def      t48
110   UserTable            Online   Yes     test         def      t49
4     UserTable            Online   Yes     mysql        def      ndb_apply_status
22    UserTable            Online   Yes     test         def      t5
24    UserTable            Online   Yes     test         def      t6
26    UserTable            Online   Yes     test         def      t7
28    UserTable            Online   Yes     test         def      t8
30    UserTable            Online   Yes     test         def      t9
112   UserTable            Online   Yes     test         def      t50
114   UserTable            Online   Yes     test         def      t51
116   UserTable            Online   Yes     test         def      t52
118   UserTable            Online   Yes     test         def      t53
johan@stingray:~/dev$ ndb_show_tables  |grep UserTable |grep t
120   UserTable            Online   Yes     test         def      t54
4     UserTable            Online   Yes     mysql        def      ndb_apply_status
24    UserTable            Online   Yes     test         def      t6
26    UserTable            Online   Yes     test         def      t7
28    UserTable            Online   Yes     test         def      t8
30    UserTable            Online   Yes     test         def      t9
112   UserTable            Dropping Yes     test         def      t50
114   UserTable            Online   Yes     test         def      t51
116   UserTable            Online   Yes     test         def      t52
118   UserTable            Online   Yes     test         def      t53
johan@stingray:~/dev$ ndb_show_tables  |grep UserTable |grep t
120   UserTable            Online   Yes     test         def      t54
4     UserTable            Online   Yes     mysql        def      ndb_apply_status
24    UserTable            Online   Yes     test         def      t6
26    UserTable            Online   Yes     test         def      t7
28    UserTable            Online   Yes     test         def      t8
30    UserTable            Online   Yes     test         def      t9
johan@stingray:~/dev$ ndb_show_tables  |grep UserTable |grep t
120   UserTable            Online   Yes     test         def      t54
4     UserTable            Online   Yes     mysql        def      ndb_apply_status
28    UserTable            Online   Yes     test         def      t8
30    UserTable            Online   Yes     test         def      t9

The wanted effect is that all hundred tables should be there.. (t1 to t100).
Not random ones..
[24 Nov 2008 9:42] Johan Andersson
I have just verified this in 6.3.19

mysql> show tables;
^[[A
^[[A
^[[A
^[[A+----------------+
| Tables_in_test |
+----------------+
| t1             | 
| t10            | 
| t100           | 
| t11            | 
| t12            | 
| t13            | 
| t14            | 
| t15            | 
| t16            | 
| t17            | 
| t18            | 
| t19            | 
| t2             | 
| t20            | 
| t21            | 
| t22            | 
| t23            | 
| t24            | 
| t25            | 
| t26            | 
| t27            | 
| t28            | 
| t29            | 
| t3             | 
| t30            | 
| t31            | 
| t32            | 
| t33            | 
| t34            | 
| t35            | 
| t36            | 
| t37            | 
| t38            | 
| t39            | 
| t4             | 
| t40            | 
| t41            | 
| t42            | 
| t43            | 
| t44            | 
| t45            | 
| t46            | 
| t47            | 
| t48            | 
| t49            | 
| t5             | 
| t50            | 
| t51            | 
| t52            | 
| t53            | 
| t54            | 
| t55            | 
| t56            | 
| t57            | 
| t58            | 
| t59            | 
| t6             | 
| t60            | 
| t61            | 
| t62            | 
| t63            | 
| t64            | 
| t65            | 
| t66            | 
| t67            | 
| t68            | 
| t69            | 
| t7             | 
| t70            | 
| t71            | 
| t72            | 
| t73            | 
| t74            | 
| t75            | 
| t76            | 
| t77            | 
| t78            | 
| t79            | 
| t8             | 
| t80            | 
| t81            | 
| t88            | 
| t9             | 
| t93            | 
| t94            | 
| t98            | 
+----------------+
86 rows in set (1.25 sec)

mysql> show tables;
^[[A
^[[A
^[[A
^[[A
^[[A
^[[A
^[[A
^[[AEmpty set (1 min 39.01 sec)

mysql> 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t83            | 
+----------------+
1 row in set (1.31 sec)

---

johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
46
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
42
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
37
johan@stingray:~/dev/mysqlcluster-63/scripts$ 
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
32
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
29
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
^[[A25
johan@stingray:~/dev/mysqlcluster-63/scripts$ ndb_show_tables  |grep UserTable |grep t | wc -l
19
[26 Nov 2008 3:34] li zhou
I also encountered a similar problem.
When loading a big table, about several hundred Megabyte with blob data, the exist tables were removed.

2008-11-09 10:58:21 [ndbd] WARNING -- Ndb kernel is stuck in: Job Handling 2008-11-09 10:58:21 [ndbd] INFO -- Watchdog: User time: 105881 System time: 59385 2008-11-09 11:07:41 [ndbd] WARNING -- Ndb kernel is stuck in: Job Handling 2008-11-09 11:07:41 [ndbd] INFO -- Watchdog: User time: 135400 System time: 62908 
2008-11-09 11:15:12 [ndbd] WARNING -- Ndb kernel is stuck in: Job Handling 2008-11-09 11:15:12 [ndbd] INFO -- Watchdog: User time: 159291 System time: 66816 2008-11-09 11:19:02 [ndbd] WARNING -- Ndb kernel is stuck in: Job Handling 2008-11-09 11:19:02 [ndbd] INFO -- Watchdog: User time: 168455 System time: 68132 Free extent: [ m_page_no: 25 m_file_no: 1 m_page_idx: 1364] Free extent: [ 
2008-11-09 11:19:02 [ndbd] INFO -- Watchdog: User time: 168455 System time: 68132 Free extent: [ m_page_no: 25 m_file_no: 1 m_page_idx: 1364] 
Free extent: [ m_page_no: 59 m_file_no: 1 m_page_idx: 1365] 
Free extent: [ m_page_no: 1486 m_file_no: 1 m_page_idx: 1410] 
Free extent: [ m_page_no: 1443 m_file_no: 1 m_page_idx: 1408] 
Free extent: [ m_page_no: 1379 m_file_no: 1 m_page_idx: 1406] 
Free ex .....

Work well in the latest telco-6.2. Seems exist only from 6.3.
[2 Dec 2008 12:46] 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/60373

2777 Tomas Ulin	2008-12-02
      Bug #40854  SHOW TABLES removes tables from Cluster
[2 Dec 2008 12:53] Bugs System
Pushed into 5.1.30-ndb-6.3.20  (revid:tomas.ulin@sun.com-20081202124539-qn567klsdvkpuhcm) (version source revid:tomas.ulin@sun.com-20081202124539-qn567klsdvkpuhcm) (pib:5)
[2 Dec 2008 14:37] Bugs System
Pushed into 5.1.30-ndb-6.4.0  (revid:tomas.ulin@sun.com-20081202124539-qn567klsdvkpuhcm) (version source revid:jonas@mysql.com-20081202142558-p1i4kmk41eoefhr4) (pib:5)
[6 Jan 2009 23:20] Hon-Wing Cheng
What is the root cause of this problem?  Can it be triggered by creating either disk-based or in-memory tables?
[7 Jan 2009 6:09] Tomas Ulin
The root cause of this is an error in the code which is supposed to keep a mysqld in sync with what tables are in the cluster.  This code is triggered by e.g. Show Tables. Actually there where 2 things working together to trigger this:

1. that the code actually removed tables which it could for some reason not find in the cluster.  This has now been replaced by just removing the frm files locally on the mysqld
2. A bug that was introduced when rewriting the ndbapi list tables command, causing it to return an empty list in some cases.  Which in turn triggered 1. above.

What type of table is irrelevant.  A certain length of the result set from the ndbapi list tables command is what triggers the bug.

BR,

Tomas
[7 Jan 2009 14:37] Jon Stephens
Documented in the NDB-6.3.20 changelog as follows:

        Issuing SHOW TABLES repeatedly could cause NDBCLUSTER tables to be
        dropped.