|
A complete background can be found here:
https://github.com/jdbi/jdbi/issues/309
But to sum up, if I issue a query with an "on duplicate key" clause, and a parameter I need to bind, and one of the columns has a typo in it, say for a schema like so:
CREATE TABLE `test_bug` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`data` varchar(45) NOT NULL,
|
`counter` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`id`)
|
);
|
I issue a query like so:
try ( PreparedStatement st =
|
connection.prepareStatement("insert into test_bug (id, data, counters) values (?,?,?) on duplicate key update counter = counter + ?") ) {
|
st.setInt(1, 1);
|
st.setString(2, "test");
|
st.setInt(3, 1);
|
st.setInt(4, 5);
|
st.execute();
|
}
|
(note counters instead of counter)
I get an error like so:
Exception in thread "main" 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 '5 on duplicate key update counter = counter +' at line 1
|
Query is : insert into test_jdbi_bug (id, data, counters) values (1,'test',1)5 on duplicate key update counter = counter +
|
at org.mariadb.jdbc.internal.util.ExceptionMapper.get(ExceptionMapper.java:125)
|
at org.mariadb.jdbc.internal.util.ExceptionMapper.throwException(ExceptionMapper.java:69)
|
at org.mariadb.jdbc.MariaDbStatement.executeQueryEpilog(MariaDbStatement.java:213)
|
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:185)
|
at org.mariadb.jdbc.MariaDbClientPreparedStatement.execute(MariaDbClientPreparedStatement.java:129)
|
at com.shareplaylearn.App.jdbcTest(App.java:23)
|
at com.shareplaylearn.App.main(App.java:29)
|
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
|
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
|
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
|
at java.lang.reflect.Method.invoke(Method.java:498)
|
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
|
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: 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 '5 on duplicate key update counter = counter +' at line 1
|
Query is : insert into test_jdbi_bug (id, data, counters) values (1,'test',1)5 on duplicate key update counter = counter +
|
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:870)
|
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQueries(AbstractQueryProtocol.java:789)
|
at org.mariadb.jdbc.MariaDbClientPreparedStatement.executeInternal(MariaDbClientPreparedStatement.java:177)
|
... 8 more
|
Note the '5' character that was bizarrely inserted into the query.
Instead of the expected error (you can see the expected error by trimming off the On Duplicate clause:
try ( PreparedStatement st =
|
connection.prepareStatement("insert into test_jdbi_bug (id, data, counters) values (?,?,?)") ) { //+
|
//" on duplicate key update counter = counter + ?") ) {
|
st.setInt(1, 1);
|
st.setString(2, "test");
|
st.setInt(3, 1);
|
///st.setInt(4, 5);
|
st.execute();
|
}
|
...(yields the below, as expected)
|
Exception in thread "main" java.sql.SQLSyntaxErrorException: Unknown column 'counters' in 'field list'
|
Query is : insert into test_jdbi_bug (id, data, counters) values (1,'test',1)
|
There are two things wrong with the error you get with the on duplicate clause:
- it's incredibly misleading and makes a simple, common mistake very hard to track down
- it seems to indicate the sql parser has gone a bit off the reservation.
|