| Bug #6592 | ndb table, insert, Duplicate entry '-1' for key 1 | ||
|---|---|---|---|
| Submitted: | 12 Nov 2004 13:37 | Modified: | 16 Dec 2004 3:42 |
| Reporter: | yujing zheng | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.7 | OS: | Linux (Redhat Enterprise Linux AS 3) |
| Assigned to: | CPU Architecture: | Any | |
[15 Nov 2004 12:41]
Tomas Ulin
We need a reproducable test case. This info is not enough. Simpler testcase would be preferable. If it is possible to get with the mysql client directly that would be good.
[16 Nov 2004 3:42]
yujing zheng
1. Install 2 databases with cluster support with the ip are 192.168.1.253 and 192.168.1.252.
2. configure cluster like this:
[ndbd(NDB)] 1 node(s)
id=2 @192.168.1.253 (Version: 3.5.3, Nodegroup: 0, Master)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.1.253 (Version: 3.5.3)
[mysqld(API)] 4 node(s)
id=3 @192.168.1.253 (Version: 3.5.3)
id=4 @192.168.1.252 (Version: 3.5.3)
id=5 (not connected, accepting connect from 192.168.1.252)
id=6 (not connected, accepting connect from 192.168.1.252)
3. create table in the database 'test':
CREATE TABLE ndb_table1(
id int(11) not null auto_increment primary key,
name varchar(20) not null
)TYPE=NDB;
GRANT ALL PRIVILEGES ON test.* TO test;
4. write the banchmark program with connector/J 3.0.15:
import java.sql.*
public class MySQLBenchmark {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
for(int i = 0;i < 100;i++){
Thread thread = new BenchmarkThread();
thread.start();
}
}
public static long benchNormalStatementInsert(Connection conn)
throws SQLException {
Statement stmt = conn.createStatement();
long start = System.currentTimeMillis();
for (int i = 0; i < 1000; i++) {
stmt.executeUpdate("INSERT INTO ndb_table1(name) values('"
+ "testName" + i + "')");
}
stmt.close();
return System.currentTimeMillis() - start;
}
public static long benchPreparedStatementInsert(Connection conn)
throws SQLException {
long start = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT INTO
ndb_table1(name) values(?)");
for (int i = 0; i < 1000; i++) {
stmt.setString(1, "testName" + i);
stmt.executeUpdate();
}
stmt.close();
return System.currentTimeMillis() - start;
}
public static long benchBatchStatementInsert(Connection conn)
throws SQLException {
long start = System.currentTimeMillis();
PreparedStatement stmt = conn.prepareStatement("INSERT INTO
ndb_table1(name) values(?)");
stmt = conn.prepareStatement("INSERT INTO ndb_table1(name)
values(?)");
for (int i = 0; i < 1000; i++) {
stmt.setString(1, "testName" + i);
stmt.addBatch();
}
stmt.executeBatch();
stmt.close();
return System.currentTimeMillis() - start;
}
public static class BenchmarkThread extends Thread {
public void run() {
try {
for (int i = 0; i < 10; i++) {
Connection conn =
DriverManager.getConnection("jdbc:mysql://192.168.1.253,192.168.1.252/test?autoReconnect=true&failOverReadOnly=true",
"test", null);
System.out.println(getName() + ": Normal Statement
Insert: " + benchNormalStatementInsert(conn));
System.out.println(getName() + ": Prepared
Statement Insert: " + benchPreparedStatementInsert(conn));
System.out.println(getName() + ": Batch Statement
Insert: " + benchBatchStatementInsert(conn));
conn.close();
}
} catch (SQLException sqle) {
System.err.println("Thread:" + getName());
sqle.printStackTrace(System.err);
return;
}
}
}
}
run it and the error will be printed in console:
... ...
Thread-95: Batch Statement Insert: 62406
Thread-99: Batch Statement Insert: 62187
Thread:Thread-23
java.sql.SQLException: Duplicate key or integrity constraint violation
message from server: "Duplicate entry '-1' for key 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2244)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2192)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205)
at
MySQLBenchmark.benchmarkNormalStatementInsert(MySQLBenchmark.java:24)
at MySQLBenchmark$BenchmarkThread.run(MySQLBenchmark.java:59)
Thread-2: Normal Statement Insert: 61297
Thread-3: Normal Statement Insert: 60484
... ...
[14 Feb 2005 22:54]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: When I insert data to clustered mysql servers for my benchmark, sometimes this exception occurred: ... ... Thread-95: Batch Statement Insert: 62406 Thread-99: Batch Statement Insert: 62187 Thread:Thread-23 java.sql.SQLException: Duplicate key or integrity constraint violation message from server: "Duplicate entry '-1' for key 1" at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278) at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224) at com.mysql.jdbc.Connection.execSQL(Connection.java:2244) at com.mysql.jdbc.Connection.execSQL(Connection.java:2192) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259) at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205) at MySQLBenchmark.benchmarkNormalStatementInsert(MySQLBenchmark.java:24) at MySQLBenchmark$BenchmarkThread.run(MySQLBenchmark.java:59) Thread-2: Normal Statement Insert: 61297 Thread-3: Normal Statement Insert: 60484 ... ... How to repeat: The cluster status is: NDB> show Cluster Configuration --------------------- [ndbd(NDB)] 1 node(s) id=2 @192.168.1.253 (Version: 3.5.3, Nodegroup: 0, Master) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.1.253 (Version: 3.5.3) [mysqld(API)] 4 node(s) id=3 @192.168.1.253 (Version: 3.5.3) id=4 @192.168.1.252 (Version: 3.5.3) id=5 (not connected, accepting connect from 192.168.1.252) id=6 (not connected, accepting connect from 192.168.1.252) Table structure in database test: CREATE TABLE ndb_table1( id int(11) not null auto_increment primary key, name varchar(20) not null )TYPE=NDB; You must grant user 'test' to both servers(why can't it grant users from one server and apply to all servers? isn't it a bug?): GRANT ALL PRIVILEGES ON test.* TO test; My computer ip address is 192.168.1.3 and the java test code is: import java.sql.* public class MySQLBenchmark { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); for(int i = 0;i < 100;i++){ Thread thread = new BenchmarkThread(); thread.start(); } } public static long benchNormalStatementInsert(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); long start = System.currentTimeMillis(); for (int i = 0; i < 1000; i++) { stmt.executeUpdate("INSERT INTO ndb_table1(name) values('" + "testName" + i + "')"); } stmt.close(); return System.currentTimeMillis() - start; } public static long benchPreparedStatementInsert(Connection conn) throws SQLException { long start = System.currentTimeMillis(); PreparedStatement stmt = conn.prepareStatement("INSERT INTO ndb_table1(name) values(?)"); for (int i = 0; i < 1000; i++) { stmt.setString(1, "testName" + i); stmt.executeUpdate(); } stmt.close(); return System.currentTimeMillis() - start; } public static long benchBatchStatementInsert(Connection conn) throws SQLException { long start = System.currentTimeMillis(); PreparedStatement stmt = conn.prepareStatement("INSERT INTO ndb_table1(name) values(?)"); stmt = conn.prepareStatement("INSERT INTO ndb_table1(name) values(?)"); for (int i = 0; i < 1000; i++) { stmt.setString(1, "testName" + i); stmt.addBatch(); } stmt.executeBatch(); stmt.close(); return System.currentTimeMillis() - start; } public static class BenchmarkThread extends Thread { public void run() { try { for (int i = 0; i < 10; i++) { Connection conn = DriverManager.getConnection("jdbc:mysql://192.168.1.253,192.168.1.252/test?autoReconnect=true&failOverReadOnly=true", "test", null); System.out.println(getName() + ": Normal Statement Insert: " + benchNormalStatementInsert(conn)); System.out.println(getName() + ": Prepared Statement Insert: " + benchPreparedStatementInsert(conn)); System.out.println(getName() + ": Batch Statement Insert: " + benchBatchStatementInsert(conn)); conn.close(); } } catch (SQLException sqle) { System.err.println("Thread:" + getName()); sqle.printStackTrace(System.err); return; } } } } run it, then you can find them.... Suggested fix: fix it pls...