[CONJ-1093] Check the manual that corresponds to your MariaDB server version for the right syntax to use near '?"%" Created: 2023-07-23  Updated: 2023-11-22  Resolved: 2023-11-22

Status: Closed
Project: MariaDB Connector/J
Component/s: MySQL compatibility
Affects Version/s: 3.1.4
Fix Version/s: N/A

Type: Bug Priority: Blocker
Reporter: Duckie Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Java

Attachments: PNG File error_SQL.png    
Issue Links:
Blocks
is blocked by CONJ-1097 JSON类型字段在select union中返回列类型错误 Open

 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

 Comments   
Comment by Diego Dupin [ 2023-07-26 ]

I need to check why mysql connector is not compatible with MariaDB server 11.1.1, but to the main point :
I imagine MariaDB Connector/J 4.3.1, is in fact MariaDB Connector/J 4.1.3.

It would seem the command executed cannot be prepared.
MySQL connector implementatiopn completly differ from MariaDB.
MySQL:

  • execute PREPARE command
  • read PREPARE response.
    • if ERROR, execute basic text command, replacing placeholder with text escaped parameters.
    • if not ERROR, execute EXECUTE command, then read EXECUTE response.

mariadb implementation for MariaDB server implementation:

  • Pipeline PREPARE and EXECUTE command.
  • Read PREPARE response,
    • if ERROR, skip EXECUTE error response
    • if not ERROR, read EXECUTE response.
      When using MySQL server, the behavior is similar to MySQL connector.
      The reason is that mariadb permit all prepared statement but "XA commands", mysql server has a lot more restrictions.

The permit to gain 40-50% performance (execution time).

As indicate before, the problem is that it would seems the command cannot be prepared. Could you share the executed command ? code and image doesn't show executed command.
if you execute :

prepare test from "<your command>";

This will probably result in this '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' error

Comment by Duckie [ 2023-07-27 ]

If i run :
prepare test from "SELECT * FROM rooms WHERE owner_name COLLATE UTF8MB4_GENERAL_CI LIKE LOWER("%rene%") AND rooms.state NOT LIKE '' ORDER BY rooms.users, rooms.id DESC LIMIT 035";
I get an error:
Error Code: 1054. Unknown column 'rene' in 'field list' 0.000 sec

Comment by Diego Dupin [ 2023-07-31 ]

Could it be just some wrong escaping ?
testing with 

@Test
 
public void testt() throws SQLException {
 
  Statement stmt = sharedConn.createStatement();
 
  stmt.execute("DROP TABLE IF EXISTS rooms ");
 
  stmt.execute("CREATE TABLE rooms (id int, owner_name varchar(32), state ENUM('','value1','value2'), users varchar(32))");
 
  try (PreparedStatement prep = sharedConn.prepareStatement("SELECT * FROM rooms WHERE owner_name COLLATE UTF8MB4_GENERAL_CI LIKE LOWER(\"%rene%\") AND rooms.state NOT LIKE '' ORDER BY rooms.users, rooms.id DESC LIMIT 035")) {
 
    prep.execute();
 
  }
 
}

is allright. same using : 

try (PreparedStatement prep = sharedConn.prepareStatement("SELECT * FROM rooms WHERE owner_name COLLATE UTF8MB4_GENERAL_CI LIKE LOWER(?) AND rooms.state NOT LIKE '' ORDER BY rooms.users, rooms.id DESC LIMIT 035")) {
 
    prep.setString(1, "%rene%");
 
    prep.execute();
 
  }

Could you share more details ?

Comment by Duckie [ 2023-08-04 ]

This is the complete call 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)) {
 
            statement.setString(1, (filterField.comparator == NavigatorFilterComparator.EQUALS ? value : "%" + value + "%"));
 
            try (ResultSet set = statement.executeQuery()) {
 
                while (set.next()) {
 
                    Room room = this.activeRooms.get(set.getInt("id"));
 
 
 
                    if (room == null) {
 
                        room = new Room(set);
 
                        this.activeRooms.put(set.getInt("id"), room);
 
                    }
 
 
 
                    if (!rooms.containsKey(set.getInt("category"))) {
 
                        rooms.put(set.getInt("category"), new ArrayList<>());
 
                    }
 
 
 
                    rooms.get(set.getInt("category")).add(room);
 
                }
 
            }
 
        } catch (SQLException e) {
 
            log.error("Caught SQL exception", e);
 
        }
 
 
 
        return rooms;
 
    }

Comment by Duckie [ 2023-08-04 ]

And i look like it goes wrong here : value : "%" + value + "%"
i tryed : value : "'%" + value + "%'" but also no luck

But the stange thing is that with the MySQL connector it just runs fine

Comment by Duckie [ 2023-08-04 ]

I finaly found it

It is using the following query :

INSERT INTO `navigator_filter` VALUES ('owner', 'getOwnerName', 'equals_ignore_case', 'SELECT * FROM rooms WHERE owner_name COLLATE UTF8MB4_GENERAL_CI LIKE LOWER(\"%\"?\"%\")');

I had to change this to :

INSERT INTO `navigator_filter` VALUES ('owner', 'getOwnerName', 'equals_ignore_case', 'SELECT * FROM rooms WHERE owner_name COLLATE UTF8MB4_GENERAL_CI LIKE (?)');

Generated at Thu Feb 08 03:20:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.