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

[12 Nov 2004 13:37] yujing zheng
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...
[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".