alniks alniks - 2 months ago 19
Java Question

Batch insert in MariaDB with on duplicate key update

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?

Answer

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.