Bug #35990 Error code 1062 to 1296 (unknown) after setting sql_mode to STRICT_TRANS_TABLES
Submitted: 11 Apr 2008 10:28 Modified: 16 Oct 2009 12:03
Reporter: Sébastien Launay Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:ndbcluster 6.2, 6.3, 7.0 OS:Any (any)
Assigned to: Martin Skold CPU Architecture:Any
Tags: 5.0, 5.0.51a-3ubuntu5, 5.1 -BK

[11 Apr 2008 10:28] Sébastien Launay
Description:
When i insert a row that already exists because of a primary key constraint i have the following error using mysql command line interface:
ERROR 1062 (23000): Duplicate entry 'code' for key 1
which is the right behaviour.

But, when i use the Java connector which set the SQL mode to STRICT_TRANS_TABLES, i have the following error :
ERROR 1296 (HY000): Got error 630 'Tuple already existed when attempting to insert' from NDB
which is the unknown error code (1296) with the engine error code 630.

And, i can not use the error code to determine if a constraint has been violated, especially using Spring Framework JDBC error translation to DataIntegrityViolationException.

If the error code was 1062 or 630 instead of 1296 the translation would have worked.

How to repeat:
# Start a mysql session the following table
create table test (code varchar(64) not null, PRIMARY KEY (code)) ENGINE=NDBCluster;
Query OK, 0 rows affected (0.75 sec)

# Try to add two entries
mysql>  insert into test values ('code');
Query OK, 1 row affected (0.11 sec)

mysql>  insert into test values ('code');
ERROR 1062 (23000): Duplicate entry 'code' for key 1

# Then set the STRICT_TRANS_TABLES sql mode and retry
mysql> set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.17 sec)

mysql>  insert into test values ('code');
ERROR 1296 (HY000): Got error 630 'Tuple already existed when attempting to insert' from NDB
[11 Apr 2008 21:07] Sveta Smirnova
Thank you for the report.

Verified as described.
[7 Oct 2009 15:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86039

3015 Martin Skold	2009-10-07
      Bug#35990 Error code 1062 to 1296 (unknown) after setting sql_mode to STRICT_TRANS_TABLES: Disable ndb error warnings in strict mode
      modified:
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/t/ndb_insert.test
        sql/ha_ndbcluster.cc
[14 Oct 2009 14:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86832

3104 Martin Skold	2009-10-14 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_alter_table_backup.result
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_insert.test
        sql/ha_ndbcluster.cc
[15 Oct 2009 12:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86978

3124 Martin Skold	2009-10-15 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_alter_table_backup.result
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/r/ndb_update.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_insert.test
        mysql-test/suite/ndb/t/ndb_update.test
        sql/ha_ndbcluster.cc
        sql/sql_update.cc
[15 Oct 2009 14:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86985

3080 Martin Skold	2009-10-15 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_alter_table_backup.result
        mysql-test/suite/ndb/r/ndb_auto_increment.result
        mysql-test/suite/ndb/r/ndb_insert.result
        mysql-test/suite/ndb/r/ndb_update.result
        mysql-test/suite/ndb/t/ndb_auto_increment.test
        mysql-test/suite/ndb/t/ndb_insert.test
        mysql-test/suite/ndb/t/ndb_update.test
        sql/ha_ndbcluster.cc
        sql/sql_update.cc
[16 Oct 2009 12:03] Jon Stephens
Documented bugfix in the NDB-6.2.19, 6.3.28, and 7.0.9 changelogs, as follows:

        When the MySQL server SQL mode included STRICT_TRANS_TABLES, NDB
        storage engine warnings and error codes were returned when
        errors occurred, instead of the MySQL server errors and error 
        codes expected by some programming APIs (such as Connector/J) and 
        applications.

Closed.