Duplicate data inserted, with ERROR 1062 reported

This topic describes how to handle ERROR 1062, where duplicate data is inserted into the MySQL mode of OceanBase Database.

Symptom

When you insert data into the t_insert table with a primary key, this error is returned, indicating that a primary key value in the inserted data is duplicate with an existing primary key value.

obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   | 10001 | 2022-10-12 15:17:17 |
|  2 | US   | 10002 | 2022-10-12 16:29:16 |
|  3 | EN   | 10003 | 2022-10-12 16:29:26 |
+----+------+-------+---------------------+
3 rows in set

obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'

The error codes corresponding to this error message are as follows:

• Error code: ERROR 1062

• Error code in OceanBase Database: 5024

• Error code compatible with MySQL Database: 1062

For more information about the error codes, see Overview of error messages.

Possible causes

When you insert data into a table with a UNIQUE constraint, the system reports an error when the inserted data is duplicate with an existing record.

Troubleshooting procedure

You can take either of the following two methods to solve the conflict.

Method 1: If you use INSERT IGNORE INTO to avoid constraint conflicts, the IGNORE keyword can be used to ignore the impact of an insert failure caused by a constraint conflict.

1. Add the IGNORE keyword when you insert data into the t_insert table.

   obclient> SELECT * FROM t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
   |  2 | US   | 10002 | 2022-10-12 16:29:16 |
   |  3 | EN   | 10003 | 2022-10-12 16:29:26 |
   +----+------+-------+---------------------+
   3 rows in set
   
   obclient> INSERT IGNORE INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004);
   Query OK, 1 row affected
   

2. Query data in the t_insert table again. You can see that records with primary key values duplicate with existing ones are discarded, and records with primary key values different from existing ones are inserted.

   obclient> SELECT * FROM t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
   |  2 | US   | 10002 | 2022-10-12 16:29:16 |
   |  3 | EN   | 10003 | 2022-10-12 16:29:26 |
   |  4 | JP   | 10004 | 2022-10-12 17:02:52 |
   +----+------+-------+---------------------+
   4 rows in set
   

Method 2: If you use INSERT INTO ON DUPLICATE KEY UPDATE to avoid constraint conflicts, you can specify the action to take on duplicate primary key values or unique key values. In the following example, the conflicting content is updated.

1. Execute the INSERT INTO ON DUPLICATE KEY UPDATE statement to avoid constraint conflicts when you insert data into the t_insert table.

      obclient> SELECT * FROM t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
   |  2 | US   | 10002 | 2022-10-12 16:29:16 |
   |  3 | EN   | 10003 | 2022-10-12 16:29:26 |
   +----+------+-------+---------------------+
   3 rows in set
   
   obclient> INSERT INTO t_insert(id, name, value) VALUES (3,'UK', 10003),(4, 'JP', 10004) ON DUPLICATE KEY UPDATE name=VALUES(name);
   Query OK, 1 row affected
   

2. Query data in the t_insert table again. You can see that records with primary key values duplicate with existing ones are updated, and records with primary key values different from existing ones are inserted.

   obclient> select * from t_insert;
   +----+------+-------+---------------------+
   | id | name | value | gmt_create          |
   +----+------+-------+---------------------+
   |  1 | CN   | 10001 | 2022-10-12 15:17:17 |
   |  2 | US   | 10002 | 2022-10-12 16:29:16 |
   |  3 | UK   | 10003 | 2022-10-12 16:29:26 |
   |  4 | JP   | 10004 | 2022-10-12 18:04:32 |
   +----+------+-------+---------------------+
   4 rows in set