We recently updated our database to mariadb 10, but ever since the update I get an error when I try to Insert into the db, with the error of duplicate entry on one of our unique key constraint and I use the ON DUPLICATE KEY in my sql script.
For inserting I use a class that extends BatchSqlUpdate, but in that class I only set the data source, the insert sql, the batch size = 100 and use declareParameter() to set the sql parameters.
However we found a workaround if we change the batch size 1, the code works perfectly so I think it's might not caused by my code, but we need a proper solution and I'm out of ideas.
Currently I use:
INSERT INTO table(ldb, lid, scid, sid, valid)
VALUES(?, ?, (SELECT id FROM cserver WHERE ldb=? AND lid=?), ?, 1)
ON DUPLICATE KEY id=LAST_INSERTED_ID(id),
ldb=?, lid=? scid=(SELECT id FROM cserver WHERE ldb=? AND lid=?), sid, valid=1;
I have tried to read through the code and debug through it. Some advice said that it might was caused by the libraries so I tried to change some dependencies, but I can't change the spring-jdbc version to 6 or higher, and other changes didn't got me any results.
I would like to know what is the error. If it's caused by the db or the dependency or whatever at this point.
This is a bug in MariaDB which was initially reported here on Stack Overflow and which is unfortunately still not fixed.
The subselect always returns the same value (see example in MDEV-30523) which results in your case in a duplicate key error.
MariaDB Bugtracker: MDEV-30523