Description:
With sql_mode=NO_BACKSLASH_ESCAPES I receive the follow syntax exception if not all string columns are updated/modify. You can reproduce it with the follow test case.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6B65792773''' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1312)
at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1248)
at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2337)
at mysql.NoBackSlashEscapesTest.test(NoBackSlashEscapesTest.java:44)
at mysql.NoBackSlashEscapesTest.withNoBackslashEscape(NoBackSlashEscapesTest.java:19)
How to repeat:
package testsuite.regression;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import testsuite.BaseTestCase;
public class NoBackSlashEscapesTest extends BaseTestCase {
public NoBackSlashEscapesTest(String name) throws ClassNotFoundException {
super(name);
}
public void testWithNoBackslashEscape() throws SQLException {
test(true);
}
public void testWithoutNoBackslashEscape() throws SQLException {
test(false);
}
private void test(boolean escape) throws SQLException {
Properties props = new Properties();
if (escape) {
props.put("sessionVariables", "sql_mode = 'NO_BACKSLASH_ESCAPES'");
}
Connection conn = null;
try {
conn = getConnectionWithProps(dbUrl, props);
Statement st = conn.createStatement();
try {
st.execute("CREATE TABLE NoBackSlashEscapesTest( id varchar(50) NOT NULL, data longtext, start DATETIME, PRIMARY KEY (id) )");
st.execute("INSERT INTO NoBackSlashEscapesTest(id,data,start) VALUES( 'key''s', 'my data', {ts '2005-01-05 13:59:20'})");
PreparedStatement pr = conn.prepareStatement("SELECT * FROM NoBackSlashEscapesTest", ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pr.executeQuery();
assertTrue(rs.next());
String text = "any\\other\ntext's\r\032\u0000\"";
rs.updateString("data", text);
rs.updateRow();
assertEquals(text, rs.getString("data"));
rs.close();
rs = pr.executeQuery();
assertTrue(rs.next());
assertEquals(text, rs.getString("data"));
} finally {
st.execute("DROP TABLE NoBackSlashEscapesTest");
}
} finally {
if (conn != null) {
conn.close();
}
}
}
}
Description: With sql_mode=NO_BACKSLASH_ESCAPES I receive the follow syntax exception if not all string columns are updated/modify. You can reproduce it with the follow test case. com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6B65792773''' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:422) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1312) at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1248) at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2337) at mysql.NoBackSlashEscapesTest.test(NoBackSlashEscapesTest.java:44) at mysql.NoBackSlashEscapesTest.withNoBackslashEscape(NoBackSlashEscapesTest.java:19) How to repeat: package testsuite.regression; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import testsuite.BaseTestCase; public class NoBackSlashEscapesTest extends BaseTestCase { public NoBackSlashEscapesTest(String name) throws ClassNotFoundException { super(name); } public void testWithNoBackslashEscape() throws SQLException { test(true); } public void testWithoutNoBackslashEscape() throws SQLException { test(false); } private void test(boolean escape) throws SQLException { Properties props = new Properties(); if (escape) { props.put("sessionVariables", "sql_mode = 'NO_BACKSLASH_ESCAPES'"); } Connection conn = null; try { conn = getConnectionWithProps(dbUrl, props); Statement st = conn.createStatement(); try { st.execute("CREATE TABLE NoBackSlashEscapesTest( id varchar(50) NOT NULL, data longtext, start DATETIME, PRIMARY KEY (id) )"); st.execute("INSERT INTO NoBackSlashEscapesTest(id,data,start) VALUES( 'key''s', 'my data', {ts '2005-01-05 13:59:20'})"); PreparedStatement pr = conn.prepareStatement("SELECT * FROM NoBackSlashEscapesTest", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = pr.executeQuery(); assertTrue(rs.next()); String text = "any\\other\ntext's\r\032\u0000\""; rs.updateString("data", text); rs.updateRow(); assertEquals(text, rs.getString("data")); rs.close(); rs = pr.executeQuery(); assertTrue(rs.next()); assertEquals(text, rs.getString("data")); } finally { st.execute("DROP TABLE NoBackSlashEscapesTest"); } } finally { if (conn != null) { conn.close(); } } } }