Description:
One of the features of my Java application is importing records from files into the database. If I use files with large number of records MySQL eventually hangs. At first I thought I had some deadlocks inside my application, but then I managed to reproduce the hang in the standalone program (see attached code).
The hang happens after arbitrary amount of records have been inserted - 300000, 400000 or 600000. I could never insert more than 900000 records. After the hang MySQL enters funny "deadlocked" state - mysqladmin reports that the server is alive, but any queries from mysql tool hang. Also mysqladmin fails to shutdown the server so I use TaskManager.
Error log reports nothing. Binary log appears to be corrupt (not sure) - at least mysqlbinlog starts displaying funny characters in the middle of valid queries. Also when I restart mysql without deleting logs, more weird things start happening - valid tables are not recognized etc. When I restart mysql after deleting logs, everything is OK.
It seems also that the problem is somehow related to the type of records being inserted. I could not reproduce the problem with text fields only, but I can reproduce it when I use text, integer and datetime fields.
The JDBC driver I am using is MySQLConnector 2.0.14. Theoretically it could be driver's problem, but I doubt it.
How to repeat:
See the snippet of the code below, which reproduces the problem after some 400000-500000 records have been inserted.
/** Initialise everything */
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/BASDB");
connection.setAutoCommit (false);
Statement stmt = connection.createStatement ();
stmt.execute ("CREATE TABLE TEST1 (ID INT PRIMARY KEY, BASVERSION int, BASTIMESTAMP datetime, TEXT VARCHAR(20), NMB int, ATTRDATE datetime) TYPE = BDB");
stmt.execute ("CREATE TABLE BAS_IDGEN (MAX_ID int)");
Connection c1 = DriverManager.getConnection("jdbc:mysql://localhost/BASDB");
c1.setAutoCommit (true);
PreparedStatement readStmt = c1.prepareStatement ("SELECT * FROM BAS_IDGEN");
PreparedStatement updateStmt = c1.prepareStatement ("UPDATE BAS_IDGEN SET " +
"MAX_ID=?");
Statement insertStmt = c1.createStatement ();
insertStmt.execute ("INSERT INTO BAS_IDGEN (MAX_ID) Values (0)");
String stmtStr = "INSERT INTO TEST1 (ID,BASVERSION,BASTIMESTAMP,TEXT,NMB,ATTRDATE) VALUES(?,?,?,?,?,?)";
PreparedStatement pstmt = connection.prepareStatement (stmtStr);
/** Start the test */
for (int i = 0; i < 1500000; ++i)
{
/* generate new id in a separate table */
ResultSet rs = readStmt.executeQuery ();
rs.next ();
int maxUsedId = rs.getInt (1);
updateStmt.clearParameters ();
updateStmt.setInt (1, ++maxUsedId);
updateStmt.executeUpdate ();
/** Insert new record */
pstmt.clearParameters ();
pstmt.setInt (1, i);
pstmt.setInt (2, 1);
SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd");
pstmt.setString (3, sdf.format (new Date ()));
pstmt.setString (4, "Test");
pstmt.setInt (5, 10);
pstmt.setString (6, "1963-06-19");
pstmt.execute ();
/* Commit every 1000 records */
if ((i % 1000) == 0)
{
System.out.println ("Committing " + i + " records");
connection.commit ();
connection.close ();
pstmt.close ();
connection = DriverManager.getConnection("jdbc:mysql://localhost/BASDB");
connection.setAutoCommit (false);
connection.setTransactionIsolation (Connection.TRANSACTION_SERIALIZABLE);
pstmt = connection.prepareStatement (stmtStr);
}
}
Description: One of the features of my Java application is importing records from files into the database. If I use files with large number of records MySQL eventually hangs. At first I thought I had some deadlocks inside my application, but then I managed to reproduce the hang in the standalone program (see attached code). The hang happens after arbitrary amount of records have been inserted - 300000, 400000 or 600000. I could never insert more than 900000 records. After the hang MySQL enters funny "deadlocked" state - mysqladmin reports that the server is alive, but any queries from mysql tool hang. Also mysqladmin fails to shutdown the server so I use TaskManager. Error log reports nothing. Binary log appears to be corrupt (not sure) - at least mysqlbinlog starts displaying funny characters in the middle of valid queries. Also when I restart mysql without deleting logs, more weird things start happening - valid tables are not recognized etc. When I restart mysql after deleting logs, everything is OK. It seems also that the problem is somehow related to the type of records being inserted. I could not reproduce the problem with text fields only, but I can reproduce it when I use text, integer and datetime fields. The JDBC driver I am using is MySQLConnector 2.0.14. Theoretically it could be driver's problem, but I doubt it. How to repeat: See the snippet of the code below, which reproduces the problem after some 400000-500000 records have been inserted. /** Initialise everything */ Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/BASDB"); connection.setAutoCommit (false); Statement stmt = connection.createStatement (); stmt.execute ("CREATE TABLE TEST1 (ID INT PRIMARY KEY, BASVERSION int, BASTIMESTAMP datetime, TEXT VARCHAR(20), NMB int, ATTRDATE datetime) TYPE = BDB"); stmt.execute ("CREATE TABLE BAS_IDGEN (MAX_ID int)"); Connection c1 = DriverManager.getConnection("jdbc:mysql://localhost/BASDB"); c1.setAutoCommit (true); PreparedStatement readStmt = c1.prepareStatement ("SELECT * FROM BAS_IDGEN"); PreparedStatement updateStmt = c1.prepareStatement ("UPDATE BAS_IDGEN SET " + "MAX_ID=?"); Statement insertStmt = c1.createStatement (); insertStmt.execute ("INSERT INTO BAS_IDGEN (MAX_ID) Values (0)"); String stmtStr = "INSERT INTO TEST1 (ID,BASVERSION,BASTIMESTAMP,TEXT,NMB,ATTRDATE) VALUES(?,?,?,?,?,?)"; PreparedStatement pstmt = connection.prepareStatement (stmtStr); /** Start the test */ for (int i = 0; i < 1500000; ++i) { /* generate new id in a separate table */ ResultSet rs = readStmt.executeQuery (); rs.next (); int maxUsedId = rs.getInt (1); updateStmt.clearParameters (); updateStmt.setInt (1, ++maxUsedId); updateStmt.executeUpdate (); /** Insert new record */ pstmt.clearParameters (); pstmt.setInt (1, i); pstmt.setInt (2, 1); SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd"); pstmt.setString (3, sdf.format (new Date ())); pstmt.setString (4, "Test"); pstmt.setInt (5, 10); pstmt.setString (6, "1963-06-19"); pstmt.execute (); /* Commit every 1000 records */ if ((i % 1000) == 0) { System.out.println ("Committing " + i + " records"); connection.commit (); connection.close (); pstmt.close (); connection = DriverManager.getConnection("jdbc:mysql://localhost/BASDB"); connection.setAutoCommit (false); connection.setTransactionIsolation (Connection.TRANSACTION_SERIALIZABLE); pstmt = connection.prepareStatement (stmtStr); } }