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

Check the manual that corresponds to your MariaDB server version for the right syntax to use near '?"%"

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Not a Bug
    • 3.1.4
    • N/A
    • MySQL compatibility
    • None
    • Java

    Description

      After upgrading the Database server from 10.11.4 and in java change the connector to MariaDB Connector i get the following error in my application:

      Caught SQL exception
      java.sql.SQLSyntaxErrorException: (conn=11282) 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 '?"%") AND rooms.state NOT LIKE ''  ORDER BY rooms.users, rooms.id DESC LIMIT 035' at line 1
      

      The code in Java:

      public THashMap<Integer, List<Room>> findRooms(NavigatorFilterField filterField, String value, int category, boolean showInvisible) {
              THashMap<Integer, List<Room>> rooms = new THashMap<>();
              String query = filterField.databaseQuery + " AND rooms.state NOT LIKE " + (showInvisible ? "''" : "'invisible'") + (category >= 0 ? "AND rooms.category = '" + category + "'" : "") + "  ORDER BY rooms.users, rooms.id DESC LIMIT " + (page * NavigatorManager.MAXIMUM_RESULTS_PER_PAGE) + "" + ((page * NavigatorManager.MAXIMUM_RESULTS_PER_PAGE) + NavigatorManager.MAXIMUM_RESULTS_PER_PAGE);
              try (Connection connection = Emulator.getDatabase().getDataSource().getConnection(); PreparedStatement statement = connection.prepareStatement(query)) {
                  // Get the search expresion : "'%" + value + "%'"
                  statement.setString(1, (filterField.comparator == NavigatorFilterComparator.EQUALS ? value : "%" + value + "%" ));
      

      This works fine with:
      MariaDB 10.11.4 + MySQL Connector
      MySQL 8.1.0 + MySQL Connector

      This fails with:
      MariaDB 11.1.1 + MySQL Connector, this results in error:
      Unknown system variable 'transaction_isolation'"

      MariaDB 11.1.1 + MariaDB Connector/J 4.3.1, this results:
      java.sql.SQLSyntaxErrorException: (conn=11282) 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 '?"%") AND rooms.state NOT LIKE '' ORDER BY rooms.users, rooms.id DESC LIMIT 035' at line 1

      If i run the Select in HeidiSQL then i get the result, so that looks fine

      Attachments

        Issue Links

          Activity

            People

              diego dupin Diego Dupin
              DuckieTM Duckie
              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.