alniks alniks - 1 year ago 236
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:


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 =' 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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download