Doesn't propagate errors when using 'On Duplicate Key' - instead a 0/number is inserted?

[StuAtGit]

I noticed and odd error. When using a @SqlUpdate with a "Insert into ... on duplicate key...", I had a column in the insert into that was incorrectly spelled, like so:

   insert into some_table (col1, col2_with_typo) values (:var1, :var2) on duplicate key update col1=col1 + :var1

Instead of getting a SQL exception about a misnamed column, I was getting a random integer inserted into the SQL string, so I was getting an exception like:

"Caused by: 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 '0 on duplicate key update col1 = col1 + :var1' at line 1
Query is : insert into some_table (col1, col2_with_typo) values ('some value', 1)0 on duplicate key update col1 = col1 +1'

Note the odd, spurious 0 after the parentheses - this is what actually happened - this somehow got inserted into the query. Sometimes it was a 1.

The odd part was after removing the "on duplicate key.." clause, I got an error on the lines like "column not found col2_with_typo".

I fixed the typo, and it worked fine.

It would be better to propagate the actual SQLException back up to the user, instead of inserting spurious text into the query.

[stevenschlansker]

That's very curious actually -- the insert into some_table (col1, col2_with_typo) portion of the statement should not be modified by jdbi at all. Only the colon prefixed portion is -- e.g. :var1 turns into ?.

I don't suppose you can check whether the equivalent JDBC statement triggers some bizarre error? Or maybe providing a test case?

[StuAtGit]

Good point. I'll try to see what JDBC does. Mainly it was only triggered by my own mistake (the typo), so I wanted to get the thing documented somewhere.

[StuAtGit]

And yeah, I stepped through the jdbi code, and for the life of me, couldn't figure out what code what have inserted it.

[stevenschlansker]

I agree, I don't see how this is really possible. I spent a bit of time trying to figure out what I'd need to reproduce it but didn't come up with a compelling approach. Let us know as you figure out anything more or have a reproducer test case.

[StuAtGit]

And you are correct!

I can post the entire program, but the basics should be enough:

public class App 
{
    public static void jdbcTest() throws SQLException {
        String user = "user";
        String password = "pass";
        String host = "localhost";
        String db = "db";
        try( Connection connection = DriverManager.getConnection("jdbc:mariadb://" + host + ":3306/" + db + "?user=" + user + "&password=" + password) ) {
            try ( PreparedStatement st =
                          connection.prepareStatement("insert into test_jdbi_bug (id, data, counter) 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();
            }
        }
    }

    public static void main( String[] args ) throws SQLException {
        jdbcTest();
    }
}

With a schema like so:

CREATE TABLE `test_jdbi_bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(45) NOT NULL,
  `counter` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

Ran it a few times, and then changed "counter" to "countered" and got:

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)

    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>1.3.7</version>
    </dependency>
  </dependencies>

Server version was 5.5 (I think, I actually tried against a handy devint work server, not localhost), I can try against 10.x, but have a feeling this is in the driver anyways.

[StuAtGit]

Guess I should rename the test table though ;)

[stevenschlansker]

Thanks for clearing this up!

[StuAtGit]

No problem - thanks for the quick tip on tracking it down :)

Filed a bug against mariadb connectorj... in case the next person finds this issue via JDBI:

https://jira.mariadb.org/browse/CONJ-277