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.
Add the
IGNOREkeyword when you insert data into thet_inserttable.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 affectedQuery data in the
t_inserttable 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.
Note
ON DUPLICATE KEY UPDATE column_name = expris specified: If the data to be inserted is duplicate with values of the primary key or unique key, you can use thecolumn_name = exprstatement to update the data in conflicting rows of the table. Thecolumn_name = exprstatement can assign values to one or more columns of the conflicting rows. Separate the columns with commas (,).ON DUPLICATE KEY UPDATE column_name = expris not specified: If the data to be inserted is duplicate with values of the primary key or unique key, the system returns an error.
Execute the
INSERT INTO ON DUPLICATE KEY UPDATEstatement to avoid constraint conflicts when you insert data into thet_inserttable.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 affectedQuery data in the
t_inserttable 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