Bug #41803 My sql Cluster old tables disappear when adding new tables
Submitted: 31 Dec 2008 7:20 Modified: 2 Jan 2009 6:59
Reporter: Steven Fan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: NDB API Severity:S1 (Critical)
Version:5.1.24-ndb-6.3.15-telco OS:Linux
Assigned to: CPU Architecture:Any
Tags: Table Disappear

[31 Dec 2008 7:20] Steven Fan
Description:
The linux version is  <Linux acm-dev10-n1-int 2.6.15-1.2054_FC5smp #1 SMP Tue Mar 14 16:05:46 EST 2006 i686 i686 i386 GNU/Linux>
The Mysql version is  <5.1.24-ndb-6.3.15-telco>

Our application need create about 200 table. Firstly we create about 50 tables and run "show tables" in mysql client. We could find the 50 table is there. Then we create a new table every day. After a few days, we find all the tables in the database disappear. We don't do any dropping tables operation and from the bin log, there is no any dropping table operation.

  

How to repeat:
1. Create some tables in database XXX;
2. Run the following sql every 10 seconds (The table name could change every time).

CREATE TABLE IF NOT EXISTS call_log_1230707653444 (user_int_id int(11) unsigned NOT NULL,  call_type tinyint(4) NOT NULL, is_answered tinyint(1) unsigned NOT NULL DEFAULT '1', my_phone varchar(255) NOT NULL,  start_time datetime NOT NULL,  end_time datetime NOT NULL,  far_end_name varchar(255) NOT NULL,  far_end_num varchar(255) NOT NULL, acct_code varchar(36) default '', call_note text, INDEX user_call_log_entry_idx(user_int_id, end_time)) TABLESPACE ts_CL STORAGE DISK ENGINE=NDB DEFAULT CHARSET=utf8;

3. Stop the creating table SQL after about 5 min, Run "Show tables". You could see there is no table and sometimes there is only call_log_XXX tables. Other table disappear.

4. It is easy to reproduce. I use single-node to reproduce. Losing customer data in site is one serious issue for us. Could you help check the root cause?

Thanks
[31 Dec 2008 10:47] Geert Vanderkelen
While you have your reproducible test case, could you please use ndb_show_tables if you see the tables that 'dissapeared' in that list still? If you have them still there, try on one SQL node:
1) Stop SQL Node,
2) Remove the database directory (careful with none Cluster tables!!!)
3) Start SQL Node,
4) and recreate the Database using CREATE DATABASE

Doing SHOW TABLES, what happens? I would like to check if the discovery somehow failed.
Also, please upload all the logs you have, included SQL Node.

It happens that I was just investigating similar problem on 6.3.15.
I'll try to reproduce
[31 Dec 2008 10:55] Geert Vanderkelen
Please provided the log file group and tablespace creation statements too.

Also, could you reproduce it using in-memory tables?
[31 Dec 2008 13:12] Geert Vanderkelen
Using provided table I could not reproduce (see script here under). Could you upload the tablespace/log group DDL? Also the configuration, or was it all default?
I create 30 tables, so 30 tables with different names.. takes a while to create them since they are disk-based.

    db = mysql.connector.Connect(**config)
    cursor = db.cursor()

    i = 30
    while i > 0:
        cursor.execute(CREATE_TABLE, (i,))
        db.commit()
        time.sleep(10)
        i = i - 1

    db.close()
[31 Dec 2008 13:16] Geert Vanderkelen
Also, if possible, trying it with later release would be fabulous, if you can repeated 100% with MySQL Cluster 6.3.15.
[31 Dec 2008 15:50] Steven Fan
Here is the syntax to create data file and log group:

CREATE LOGFILE GROUP lg_PCM ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE=128M UNDO_BUFFER_SIZE=16M ENGINE=NDB;
ALTER LOGFILE GROUP lg_PCM ADD UNDOFILE 'undo_2.dat' INITIAL_SIZE=128M ENGINE=NDB;

CREATE TABLESPACE ts_CL ADD DATAFILE 'data_CL_0.dat' USE LOGFILE GROUP lg_PCM INITIAL_SIZE=50M EXTENT_SIZE=1M ENGINE=NDB;
ALTER TABLESPACE ts_CL ADD DATAFILE 'data_CL_1.dat' INITIAL_SIZE=50M ENGINE=NDB;

This issue happens in in-memory database too. I have ever thought it is due to no primary key or disk database. So i add a primary key and createt the table in in-memory database. But this issue still happens. 

All the configuration is default except the following configuration:
NoOfReplicas=1
DataMemory=300M
IndexMemory=300M

MaxNoOfConcurrentTransactions=500
MaxNoOfConcurrentOperations=250000
TimeBetweenWatchDogCheck=40000
MaxNoOfOrderedIndexes=27000
MaxNoOfTables=9000
MaxNoOfAttributes=25000

I have a simple java file to reproduce this issue.
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Timer;
import java.util.TimerTask;

public class DiskDataEnhancement {
        private Connection conn = null;
        private long time = 0;
        private long count = System.currentTimeMillis();
        public DiskDataEnhancement(Connection conn) {
                this.conn = conn;
        }

        public static void main(String[] args) {
                try {
                        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
                        Connection conn = DriverManager.getConnection("jdbc:mysql://135.252.151.11:3306/xcm?user=xcmuser&password=xcmuser");
                        long startTime = System.currentTimeMillis();
                        if(null != conn) {
                                DiskDataEnhancement demo = new DiskDataEnhancement(conn);
                                demo.lose();
                        }
                        System.out.println("Total Takes "+(System.currentTimeMillis()-startTime)+" ms.");
                }
                catch(Exception e) {
                        System.out.println(e);
                }
        }

        public void lose() {
                (new Timer("mytimer")).schedule(
                                new TimerTask() {
                                        public void run() {
                                                try {
                                                        String name = "call_log_"+String.format("%10s", (count++));
                                                        StringBuffer updateSQL = new StringBuffer("CREATE TABLE IF NOT EXISTS ");
                                                        updateSQL.append(name);
                                                        updateSQL.append(" (user_int_id int(11) unsigned NOT NULL,  ");
                                                        updateSQL.append("call_type tinyint(4) NOT NULL, ");
                                                        updateSQL.append("is_answered tinyint(1) unsigned NOT NULL DEFAULT '1', ");
                                                        updateSQL.append("my_phone varchar(255) NOT NULL,  ");
                                                        updateSQL.append("start_time datetime NOT NULL,  ");
                                                        updateSQL.append("end_time datetime NOT NULL,  ");
                                                        updateSQL.append("far_end_name varchar(255) NOT NULL,  ");
                                                        updateSQL.append("far_end_num varchar(255) NOT NULL, ");
                                                        updateSQL.append("acct_code varchar(36) default '', ");
                                                        updateSQL.append("call_note text, ");
                                                        updateSQL.append("INDEX user_call_log_entry_idx(user_int_id, end_time)) ");
                                                        updateSQL.append("TABLESPACE ts_CL STORAGE DISK ENGINE=NDB DEFAULT CHARSET=utf8;");
                                                        System.out.println(count+" -> "+name);
                                                        execute(updateSQL.toString());
                                                }
                                                catch(Exception e) {
                                                        System.out.println(e);
                                                }
                                        }
                                },0,100);
        }

        private void execute(String s) throws Exception {
                PreparedStatement stmt3 = conn.prepareStatement(s);
                stmt3.executeUpdate();
                stmt3.close();
        }
}
[31 Dec 2008 16:11] Steven Fan
Her eare all log files in my lab

Attachment: log.jar (application/x-zip-compressed, text), 85.94 KiB.

[2 Jan 2009 6:59] Tomas Ulin
duplicate with
http://bugs.mysql.com/bug.php?id=40854
which is fixed in 6.3.20