You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/05/25 07:10:22 UTC
[GitHub] [shardingsphere] TeslaCN commented on issue #17888: PrepareStatement cause MySQLSyntaxErrorException
TeslaCN commented on issue #17888:
URL: https://github.com/apache/shardingsphere/issues/17888#issuecomment-1136860682
Hi @wingkingbobo
I tried your codes but I didn't reproduced this issue.
I've tried MySQL JDBC Driver 5.1.45, 5.1.47, 8.0.28.
```java
package icu.wwj.hello.world.jdbc;
import lombok.Getter;
import lombok.Setter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
public class MySQLProxyPreparedStatement {
private static final String JDBC_URL_SHARDING_PROXY = "jdbc:mysql://127.0.0.1:13306/db_17888?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
// private static final String JDBC_URL_SHARDING_PROXY = "jdbc:mysql://127.0.0.1:13306/db_17888?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&useServerPrepStmts=true";
public static void main(String[] args) {
new MySQLProxyPreparedStatement().run();
}
public void run() {
String jdbcUrl = JDBC_URL_SHARDING_PROXY;
try {
testInsert(createRemarkList(1).get(0), jdbcUrl);
testBatchInsert(createRemarkList(5), jdbcUrl);
} catch (Exception e) {
e.printStackTrace();
}
}
private List<RemarkPO> createRemarkList(int count) {
List<RemarkPO> remarks = new ArrayList<>();
for (int i = 0; i < count; i++) {
RemarkPO remark = new RemarkPO();
remark.setUserId(4);
remark.setContent("test" + i);
remark.setTime(new Date(System.currentTimeMillis()));
remarks.add(remark);
}
return remarks;
}
private boolean testInsert(RemarkPO remark, String jdbcUrl) throws Exception {
Connection connection = DriverManager.getConnection(jdbcUrl, "root", "root");
String sql = "INSERT INTO remark(userid, content, `time`) VALUES (?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, remark.getUserId());
statement.setString(2, remark.getContent());
statement.setDate(3, toSqlDate(remark.getTime()));
statement.execute();
return true;
}
private boolean testBatchInsert(List<RemarkPO> remarks, String jdbcUrl) throws Exception {
if (remarks.isEmpty()) {
return false;
}
Connection connection = DriverManager.getConnection(jdbcUrl, "root", "root");
StringBuilder sb = new StringBuilder("INSERT INTO remark(userid, content, `time`) VALUES ");
for (int i = 0; i < remarks.size(); i++) {
if (i != 0) {
sb.append(",");
}
sb.append(" (?, ?, ?)");
}
String sql = sb.toString();
PreparedStatement statement = connection.prepareStatement(sql);
int stride = 3;
for (int i = 0; i < remarks.size(); i++) {
int offset = stride * i;
RemarkPO remark = remarks.get(i);
statement.setLong(1 + offset, remark.getUserId());
statement.setString(2 + offset, remark.getContent());
statement.setDate(3 + offset, toSqlDate(remark.getTime()));
}
statement.execute();
return true;
}
private java.sql.Date toSqlDate(Date date) {
return new java.sql.Date(date.getTime());
}
@Getter
@Setter
private static class RemarkPO {
private long id;
private long userId;
private String content;
private Date time;
}
}
```
Proxy logs:
```
[INFO ] 2022-05-25 15:07:43.115 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: /* mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[INFO ] 2022-05-25 15:07:43.115 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-25 15:07:43.115 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p1 ::: /* mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_tim
eout
[INFO ] 2022-05-25 15:07:43.156 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: SELECT @@session.transaction_read_only
[INFO ] 2022-05-25 15:07:43.156 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-25 15:07:43.156 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p ::: SELECT @@session.transaction_read_only
[INFO ] 2022-05-25 15:07:43.171 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: INSERT INTO remark(userid, content, `time`) VALUES (4, 'test0', '2022-05-25')
[INFO ] 2022-05-25 15:07:43.172 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-25 15:07:43.172 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p1 ::: INSERT INTO remark(userid, content, `time`) VALUES (4, 'test0', '2022-05-25')
[INFO ] 2022-05-25 15:07:43.192 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: /* mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
[INFO ] 2022-05-25 15:07:43.193 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-25 15:07:43.193 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p1 ::: /* mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_tim
eout
[INFO ] 2022-05-25 15:07:43.202 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: SELECT @@session.transaction_read_only
[INFO ] 2022-05-25 15:07:43.202 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-05-25 15:07:43.202 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p ::: SELECT @@session.transaction_read_only
[INFO ] 2022-05-25 15:07:43.209 [ShardingSphere-Command-4] ShardingSphere-SQL - Logic SQL: INSERT INTO remark(userid, content, `time`) VALUES (4, 'test0', '2022-05-25'), (4, 'test1', '2022-05-25'), (4, 'test2', '2022-05-25'), (4, 'test3', '2022-05-25'), (4, 'test4', '2022-05-25')
[INFO ] 2022-05-25 15:07:43.209 [ShardingSphere-Command-4] ShardingSphere-SQL - SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
[INFO ] 2022-05-25 15:07:43.209 [ShardingSphere-Command-4] ShardingSphere-SQL - Actual SQL: p1 ::: INSERT INTO remark(userid, content, `time`) VALUES (4, 'test0', '2022-05-25'), (4, 'test1', '2022-05-25'), (4, 'test2', '2022-05-25'), (4, 'test3', '2022-05-25'), (4, 'test4', '2022-05-25')
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org