Duplicate data inserted, with ERROR 1062 reported

This topic describes how to handle ERROR 1062, where duplicate data is entered during data insertion to OceanBase Database in MySQL mode.

Symptom

When you insert data into the t_insert table with primary keys, this error is returned, indicating that the primary key values in the inserted data are duplicate with the existing primary key values.

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

• MySQL error code: 1062

For more information about the error codes, see Error code overview.

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 dropped, 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 keys or unique keys. 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