Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-277

Incorrect error when dealing with typo in on duplicate key query

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 1.3.7
    • 1.4.3
    • Other
    • None

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              diego dupin Diego Dupin
              StuS Stu S
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.