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: | |
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
[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