Bug #35772 MySql Cluster Crashes and fails to recover
Submitted: 2 Apr 2008 14:27 Modified: 19 Dec 2008 18:15
Reporter: Hasnain Iqbal Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:teleco version mysql-5.1.23 ndb-6.3.8 OS:Linux (Linux 2.6.18-4-686 #1 SMP i686 GNU/Linux)
Assigned to: Jonas Oreland CPU Architecture:Any
Tags: crash recovery, NDB Node failure

[2 Apr 2008 14:27] Hasnain Iqbal
Description:
Hi,

We are using MySql cluster (teleco version mysql-5.1.23 ndb-6.3.8) compiled on Pentium Xeon with Linux version (Linux evgsics11 2.6.18-4-686 #1 SMP Wed Feb 21 16:06:54 UTC 2007 i686 GNU/Linux). When we try to insert 10000 values through normal JDBC or NDB-J it gives us Node failure exception:

Executed SQL:INSERT INTO ndbj_hello_world (id, firstName, lastName, dummyData) VALUES (?, ?, ?, ?) -> 1047
Executed SQL:INSERT INTO ndbj_hello_world (id, firstName, lastName, dummyData) VALUES (?, ?, ?, ?) -> 1048
Exception in thread "main" java.sql.SQLException: Got temporary error 4010 'Node failure caused abort of transaction' from NDB
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1056)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3376)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)

After the exception if you check node status on mgm node the neither of the data node nor the sql server is connected. And if we try to restart the nodes again even then it wont start up, to make the node run again you have to do a complete init.

Configuration:

[NDBD DEFAULT]
#The number of copies of the data stored on different nodes in the cluster
NoOfReplicas=2

#The amount of main memory (RAM) used to store columns and ordered indexes in tables, plus some overhead
DataMemory=1024M

#The amount of main memory (RAM) used to hash indexes in tables, plus some overhead
IndexMemory=205M

#If you have lots of tables (>1000), then you may get 'Error 773' and need to increase this (25 would be reasonable)
StringMemory=25

#The amount of disk space (NoOfFragmentLogFiles * 64MB) used to store the Redo Log (used for node recovery)
NoOfFragmentLogFiles=7

#The speed at which LocalCheckpoints (LCP) are written to disk
DiskCheckpointSpeed=100M

#The speed at which LocalCheckpoints (LCP) are written to disk, as part of a node recovery 
DiskCheckpointSpeedInRestart=100M

#The size of the RedoLog Buffer in memory; Reduce for improved disk throughput (but slower recovery time)
DiskSyncSize=4M

#The size of the RedoBuffer used to buffer writes to the disk subsystem. Increase for high write-rate or slow disk.
RedoBuffer=8M

#The maximum time in ms that is permitted to lapse between operations in the same transaction before the transaction is aborted.
TransactionInactiveTimeout=2000

#Time in ms between global checkpoint groups are flushed from memory to disk
TimeBetweenGlobalCheckpoints=1000

#Time in ms between local checkpoints of memory to local disk. Increase this if system is highly loaded to improve node restart times.
TimeBetweenLocalCheckpoints=20

#Represents the number of seconds between memory usage reports written to the cluster log
MemReportFrequency=300

#This prevents ndbd processes and DB memory from being swapped out to disk
LockPagesInMainMemory=1

#
# TRANSACTION PARAMETERS
#

#The number of transaction records available at each data node. Increase this if you have a higher number of concurrent transactions
MaxNoOfConcurrentTransactions=4096

#The number of operation records available at each data node. Increase this if you have a higher number of concurrent operations
MaxNoOfConcurrentOperations=32768

#The number of operation records available at each data node for queries that use a hash index. Increase this if you have a higher number of concurrent operations
MaxNoOfConcurrentIndexOperations=8192

#
# Scans and Buffering
#

# Max number of parallel scans. Max value is 500.
MaxNoOfConcurrentScans=256

#Note: 'alter table' requires 3 times the number of attributes that are in the original table. 
MaxNoOfAttributes=1000

#A table is required not just for a table, but also for every unique hash index and every ordered index. Maximum value is 1600, minimum is 8.
MaxNoOfTables=128

#Each ordered index requires 10KB of memory.
MaxNoOfOrderedIndexes=128

#Each unique hash index requires 15KB of memory.
MaxNoOfUniqueHashIndexes=64

#Replication, unique index operations, backups and order index operations require trigger objects.
MaxNoOfTriggers=768

# 
#Backup Parameters
#The following must hold when updating backup params:
# 1. BackupDataBufferSize >= BackupWriteSize + 188KB
# 2. BackupLogBufferSize >= BackupWriteSize + 16KB
# 3. BackupMaxWriteSize >= BackupWriteSize
#

#Increase if slow disk subsystem when making a backup. 
BackupDataBufferSize=2M

#Increase if slow disk subsystem when making a backup. 
BackupLogBufferSize=2M

#Default size of msgs written to disk. This value must be less than or equal to BackupMaxWriteSize.
BackupWriteSize=32000

#Max size of msgs written to disk. This value must be greater than or equal to than BackupMaxWriteSize.
BackupMaxWriteSize=256000

#
# Real-time params
#

#Setting this to '1' enables real-time scheduling of ndb threads.
#RealtimeScheduler=0

#Time in microseconds for threads to be executed in the scheduler before being sent.
#SchedulerExecutionTimer=50

#Time in microseconds for threads to be executed in the scheduler before sleeping.
#SchedulerSpinTimer=0

#Whether a ndbd process should halt or be restarted if there is an error in it.
StopOnError=true

#where disk-based user data will be used for the cluster
Diskless=false

#Data nodes attempt to use O_DIRECT when writing LCPs, backups and redo logs.
#Should be disabled for 2.4 or older kernels, enabled for 2.6 or higher kernels
ODirect=true

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]
# I found  this settting buggy with 5.1.17-beta, when starting ndb_mgmd process.
# SendBufferMemory=2M

# Management Server
[NDB_MGMD]
HostName=evgsics11
PortNumber=1186
DataDir=/home/jim/.mysql/logs
LogDestination=FILE:filename=/home/jim/.mysql/logs/cluster.log,maxsize=1000000,maxfiles=6
NodeID=63

[NDBD]
HostName=evgsics12
DataDir=/home/jim/.mysql/logs
FileSystemPath=/home/jim/.mysql/ndb/ndb_data/1
NodeID=1
#Assign this data node to a CPU-ID
#LockExecuteThreadToCPU=
#LockMaintThreadToCPU=

[NDBD]
HostName=evgsics14
DataDir=/home/jim/.mysql/logs
FileSystemPath=/home/jim/.mysql/ndb/ndb_data/2
NodeID=2
#Assign this data node to a CPU-ID
#LockExecuteThreadToCPU=
#LockMaintThreadToCPU=

# Setup node IDs for mySQL API-servers (clients of the cluster)
[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

[MYSQLD]

How to repeat:
Simply write a Java program to insert 1 million rows in a table at a time. OR you can write a simple batch file which has 1 million insert statements and execute them.

Java Program:
package com.mysql.cluster.ndbj.examples;

import com.mysql.cluster.ndbj.*;
import java.sql.ResultSet;
import java.sql.*;

import com.mysql.cluster.ndbj.AbortOption; 
import com.mysql.cluster.ndbj.ExecType;
/**
 * You need to create a the following table in the "test" database:
 * <br><code>
 * mysql-client>create table ndbj_hello 
 * (`id` INTEGER NOT NULL, 
 * `firstName` VARCHAR(255) NOT NULL, 
 * `lastName` VARCHAR(255) NOT NULL,
 * PRIMARY KEY(id)
 * ) ENGINE=ndb;
 * </code>
 * 
 * <BR>Run the program using the command:
 * >java com.mysql.cluster.ndbj.examples.HelloWorldInsertSQL
 * 
 * <BR>You can test the result of the program in the "test" database:
 * <br><code>
 * mysql-client>SELECT * FROM ndbj_hello; 
 * </code>
 * 
 * @author jdowling
 */
public class HelloWorldInsertSQL {

	public static void main(String[] args) throws SQLException {

		/*
        if (args.length != 1) {
            System.err.println("Correct Usage: prog <connectstring>");
            System.exit(-1);
        }*/
        
        String table_name = "ndbj_hello_world";
        try { 
          Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) { 
          System.out.println("MySQL JDBC Driver not found"); 
        }

	org.apache.commons.dbcp.BasicDataSource ds = new org.apache.commons.dbcp.BasicDataSource();
	ds.setDriverClassName("com.mysql.jdbc.Driver");
   	ds.setUrl("jdbc:mysql://evgsics11/test?relaxAutoCommit=true");
    	ds.setUsername("root");
    	ds.setPassword("");
    	ds.setPoolPreparedStatements(true);

        Connection sqlconn = ds.getConnection();

	String catalog = null;
	String schemaPattern = null;
	// Specify null for tableNamePattern, it should return the names
	// of all the tables in the database
	String tableNamePattern = null;
	String[] types = { "TABLE" };

	ResultSet rs = null;
	rs = sqlconn.getMetaData().getTables(catalog, schemaPattern, tableNamePattern,types);

	while (rs.next()) {
		System.out.println("TABLE name : " + rs.getString("TABLE_NAME"));
	}

        System.out.println("Dropping and recreating schema");
        System.out.println("Just checking -- Hasnain M Iqbal");

        Statement s = sqlconn.createStatement(); 

	System.out.println(table_name);
	
        s.executeUpdate("DROP TABLE if exists " + table_name);
        s.executeUpdate("CREATE TABLE if not exists " +
    		      table_name + 
    		      "    (id INT not null," +
    		      "     firstName varchar(255) not null, " +
    		      "     lastName  varchar(255) NOT NULL," +
		      "     dummyData VARBINARY(7522), " +
    		      "     PRIMARY KEY (id) )" + 
    		      "  ENGINE=NDBCLUSTER");

        
		try {
			for(int i = 10; i <= 10010 ; i++){
				byte[] data = new byte[7522];

				String st = "INSERT INTO ndbj_hello_world "
			                    + "(id, firstName, lastName, dummyData) VALUES (?, ?, ?, ?)" ;

				String sql = st;
				PreparedStatement statement = null;

				statement = sqlconn.prepareStatement(sql);

			//(id, firstName, lastName, dummyData) 
			

				statement.setLong(1, i );
				statement.setString(2, "Hasnain");
				statement.setString(3, "Iqbal");
				statement.setBytes(4, data);
			
				boolean success = statement.execute();
				System.out.println("Executed SQL:" + sql + " -> " + i);			
			
				statement.close();

			}
			System.out.println("Done");
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {

			sqlconn.close();			
			
		}
	}

}
[2 Apr 2008 14:36] Jonas Oreland
please use ndb_error_reporter to collect all logfiles and attach them to this bug
/jonas
[2 Apr 2008 14:41] Hasnain Iqbal
Cluster log

Attachment: cluster.log (application/octet-stream, text), 282.12 KiB.

[2 Apr 2008 14:41] Hasnain Iqbal
Java File

Attachment: HelloWorldInsertSQL.java (text/plain), 3.64 KiB.

[2 Apr 2008 14:41] Hasnain Iqbal
Configuration File

Attachment: config-2node.ini (application/octet-stream, text), 5.10 KiB.

[2 Apr 2008 14:42] Hasnain Iqbal
Log file attached.
[2 Apr 2008 14:44] Jonas Oreland
ndb_{1/2}_{error/trace}* is needed aswell
(would be picked up if using ndb_error_reporter)

/Jonas
[3 Apr 2008 12:30] Hasnain Iqbal
NDB 1 Error trace Part 1

Attachment: ndb_1_trace_err (1).zip (application/x-zip-compressed, text), 484.00 KiB.

[3 Apr 2008 12:30] Hasnain Iqbal
NDB Error Trace Part 2

Attachment: ndb_1_trace_err (2) .zip (application/x-zip-compressed, text), 296.58 KiB.

[3 Apr 2008 12:32] Hasnain Iqbal
NDB 2 Error Trace Part 1

Attachment: ndb_2_trace_err (1).zip (application/x-zip-compressed, text), 481.59 KiB.

[3 Apr 2008 12:32] Hasnain Iqbal
NDB Error Trace Part 2

Attachment: ndb_2_trace_err (2).zip (application/x-zip-compressed, text), 229.82 KiB.

[3 Apr 2008 12:33] Hasnain Iqbal
I have added ndb_{1/2}_trace_err files. Each node error file is in parts as the size was grt then 500 Kb
[14 Apr 2008 16:47] Jim Dowling
adding ndb_error_reporter output

Attachment: ndb_error_report_20080414184617.tar.bz2 (application/x-bzip, text), 2.27 KiB.

[19 Dec 2008 18:15] Jonas Oreland
hi,

i think this has been fixed,
please retest with 6.3.20

closing this bug report,
as code has also been rewritten since 6.3.15