Batch insert in MariaDB with on duplicate key update

Question

I have the following table cl:

id - int(10) primary key
contact - int(10)
list - int(10)

With a unique index on contact and list. When I run concurrently the following query in batch by 100 records:

INSERT INTO cl(list, contact) VALUES (?, ?) ON DUPLICATE KEY UPDATE cl.id = cl.id

Under high pressure it fails in about 20% with the following error:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE cl.id = cl.id' at line 1

80% of the queries runs just fine. When I rerun failed queries with the same parameters, again 20% fail.

Why some of the queries fail and then produce no errors when executed for the second time?

I think that your sql doesn't make sense(not in a rude way)....I think that you should do something like, for example, if you want to update the list then: INSERT INTO cl(list, contact) VALUES (?, ?) ON DUPLICATE KEY UPDATE list = ?...or if you want to update contact, then you should try: INSERT INTO cl(list, contact) VALUES (?, ?) ON DUPLICATE KEY UPDATE contact = ?...or if you want to update both: INSERT INTO cl(list, contact) VALUES (?, ?) ON DUPLICATE KEY UPDATE list = ?,contact = ?.... — Hackerman
– Hackerman, Commented Sep 22, 2016 at 13:15
The idea is do not change already available records in the table. In any case, the failure is not due to the fact that a duplicate is there: there are no records with a same key in the table when the query is run. — alniks
– alniks, Commented Sep 22, 2016 at 13:49
But I still don't understand...on key duplicate update is mainly used when you are passing the id as parameter also, like: INSERT INTO cl(id,list, contact) VALUES (?,?, ?) ON DUPLICATE KEY UPDATE cl.id = cl.id...you are not passing the id, and if the id is also autoincrement, then you should never going to reach the on dup key update part..... — Hackerman
– Hackerman, Commented Sep 22, 2016 at 14:04
On duplicate reached when unique combination of list and contact is hit due to unique index on contact and list. — alniks
– alniks, Commented Sep 22, 2016 at 14:09
Mmmmm....still unclear....btw on the documentation this phrase looks interesting: It is not recommended to use this statement on tables with more than one unique index..... — Hackerman
– Hackerman, Commented Sep 22, 2016 at 14:24

alniks · Accepted Answer · 2016-09-27 15:15:45Z

1

Changing query to

INSERT IGNORE INTO cl(list, contact) VALUES (?, ?)

solved the issue. Though it is unclear why mysql was throwing java.sql.SQLSyntaxErrorException instead of something more unambiguous.

answered Sep 27, 2016 at 15:15

alniks

4252 gold badges6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Batch insert in MariaDB with on duplicate key update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related