| Bug #118354 | Unexpected syntax error with '{' and | ||
|---|---|---|---|
| Submitted: | 4 Jun 7:06 | Modified: | 5 Jun 6:40 |
| Reporter: | hongjun xiao | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.0.42 | OS: | Ubuntu (20.04.6 LTS) |
| Assigned to: | CPU Architecture: | Any | |
[4 Jun 11:03]
MySQL Verification Team
Hello hongjun xiao, Thank you for the report and test case. I'm not seeing any issues with the dummy table, could you please share output of show create table t3 and subset of data to reproduce? Thank you. -- mysql> create database database0; Query OK, 1 row affected (0.00 sec) mysql> use daatabase0; ERROR 1049 (42000): Unknown database 'daatabase0' mysql> use database0; Database changed Query OK, 0 rows affected (0.02 sec) mysql> insert into t3 values(); Query OK, 1 row affected (0.00 sec) -- import java.sql.*; public class Bug118354 { public static void main(String[] args) { String url = String .format("jdbc:mysql://**:3306"); // when connect with bash: mysql -u root, SQL works well. // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); // this SQL works in JDBC with ERROR: java.sql.SQLException: Not a valid escape sequence: {"), ("{}"))); String query1 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // same where clause, diff SET_VAR hint, JDBC works well // SELECT /*+ SET_VAR(innodb_tmpdir="\\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query2 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="\1")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query3 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\1\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query4 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // diff where clause, same SET_VAR hint, JDBC works well // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{}" NOT IN ("{}"); String query5 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{}\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{"); String query6 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{\");"); String query7 = ("SELECT t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); Statement s; try { Connection con = DriverManager.getConnection(url, "ushastry", ""); s = con.createStatement(); // s.setEscapeProcessing(false); DatabaseMetaData meta = con.getMetaData(); System.out.println("MySQL Server: " + meta.getDatabaseProductVersion()); System.out.println("Driver Name & Ver: " + meta.getDriverName() + " " + meta.getDriverVersion()); } catch (SQLException e) { System.out.println(e); return; } try { s.execute("use database0"); s.executeQuery(query7); // NO ERROR s.executeQuery(query6); // NO ERROR s.executeQuery(query5); // NO ERROR s.executeQuery(query4); // NO ERROR s.executeQuery(query3); // NO ERROR s.executeQuery(query2); // NO ERROR s.executeQuery(query1); // ERROR: java.sql.SQLException: Not a valid escape sequence: {"), ("{}"))); } catch (SQLException e) { System.out.println(e); } } } -- cd /Users/umshastr/Work/Connectors/ConnectorJ/Bug118354; JAVA_HOME=/opt/homebrew/Cellar/openjdk@17/17.0.15/libexec/openjdk.jdk/Contents/Home "/Applications/Apache NetBeans.app/Contents/Resources/netbeans/java/maven/bin/mvn" -Dexec.vmArgs= "-Dexec.args=${exec.vmArgs} -classpath %classpath ${exec.mainClass} ${exec.appArgs}" -Dexec.appArgs= -Dexec.mainClass=Bug118354 -Dexec.executable=/opt/homebrew/Cellar/openjdk@17/17.0.15/libexec/openjdk.jdk/Contents/Home/bin/java --no-transfer-progress process-classes org.codehaus.mojo:exec-maven-plugin:3.1.0:exec Scanning for projects... --------------------------< Oracle:Bug118354 >-------------------------- Building Bug118354 1.0-SNAPSHOT from pom.xml --------------------------------[ jar ]--------------------------------- --- resources:3.3.1:resources (default-resources) @ Bug118354 --- skip non existing resourceDirectory /Users/umshastr/Work/Connectors/ConnectorJ/Bug118354/src/main/resources --- compiler:3.13.0:compile (default-compile) @ Bug118354 --- Nothing to compile - all classes are up to date. --- exec:3.1.0:exec (default-cli) @ Bug118354 --- MySQL Server: 8.0.42 Driver Name & Ver: MySQL Connector/J mysql-connector-j-9.3.0 (Revision: 20ef8ee9eb4294a03858acccea0ddad3525f1ff9) ------------------------------------------------------------------------ BUILD SUCCESS ------------------------------------------------------------------------ Total time: 4.550 s Finished at: 2025-06-04T16:28:54+05:30 --------------------------------------------------- regards, Umesh
[4 Jun 15:30]
hongjun xiao
I had this problem with jdbc version 8.0, any table and data could reproduce the problem.
I realized that the new version of jdbc doesn't have that problem.
/t# java -cp .:/t/mysql-connector-j-9.3.0.jar ./JDBCtest.java
/t# java -cp .:/t/mysql-connector-java-8.0.30.jar ./JDBCtest.java
java.sql.SQLException: Not a valid escape sequence: {" NOT IN ("{}");
/t#
[5 Jun 6:40]
MySQL Verification Team
Thank you for confirming that issue is not seen on latest builds. For now I'll close the report but if you are able to reproduce on latest then please feel free to open the bug. Thank you. regards, Umesh
Description: The use of SET_VAR and the character '{' will result in a syntax error in certain cases, but not in others. It's weird and I don't know what the problem is. The detailed test is as follows: How to repeat: import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class JDBCtest { public static void main(String[] args) { String url = String .format("jdbc:mysql://localhost:3306"); // when connect with bash: mysql -u root, SQL works well. // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); // this SQL works in JDBC with ERROR: java.sql.SQLException: Not a valid escape sequence: {"), ("{}"))); String query1 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // same where clause, diff SET_VAR hint, JDBC works well // SELECT /*+ SET_VAR(innodb_tmpdir="\\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query2 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="\1")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query3 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\1\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{}"); String query4 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); // diff where clause, same SET_VAR hint, JDBC works well // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{}" NOT IN ("{}"); String query5 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{}\" NOT IN (\"{}\");"); // SELECT /*+ SET_VAR(innodb_tmpdir="\")*/ t3.c0 FROM t3 WHERE "{" NOT IN ("{"); String query6 = ("SELECT /*+ SET_VAR(innodb_tmpdir=\"\\\")*/ t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{\");"); String query7 = ("SELECT t3.c0 FROM t3 WHERE \"{\" NOT IN (\"{}\");"); Statement s; try { Connection con = DriverManager.getConnection(url, "root", "123"); s = con.createStatement(); // s.setEscapeProcessing(false); } catch (Exception e) { System.out.println(e); return; } try { s.execute("use database0"); s.executeQuery(query7); // NO ERROR s.executeQuery(query6); // NO ERROR s.executeQuery(query5); // NO ERROR s.executeQuery(query4); // NO ERROR s.executeQuery(query3); // NO ERROR s.executeQuery(query2); // NO ERROR s.executeQuery(query1); // ERROR: java.sql.SQLException: Not a valid escape sequence: {"), ("{}"))); } catch (Exception e) { System.out.println(e); } } }