MySQL Bugs: #41803: My sql Cluster old tables disappear when adding new tables

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

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

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

Please provided the log file group and tablespace creation statements too.

Also, could you reproduce it using in-memory tables?

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()

Also, if possible, trying it with later release would be fabulous, if you can repeated 100% with MySQL Cluster 6.3.15.

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();
        }
}

Her eare all log files in my lab

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

duplicate with
http://bugs.mysql.com/bug.php?id=40854
which is fixed in 6.3.20