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 2020/11/22 14:27:53 UTC
[GitHub] [shardingsphere] wittech opened a new issue #8296: shardingsphere-proxy select * from XXX error
wittech opened a new issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296
mysql 8.0
when i use select * from table_a ,and table_a has a column named 'key' or 'name' （the mysql keywords）, shardingsphere-proxy can not excute.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu closed issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-731913330
1. What is the ShardingSphere version do you use?
2. What is the error message?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-733408962
@terrymanu @yl-yue No problem with parser. the problem with rewrite of rendering `selectItem`,I'll check it,
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yl-yue edited a comment on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
yl-yue edited a comment on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-732642987
sharding-proxy: 5.0.0-RC1-SNAPSHOT
```java
[INFO ] 12:15:41.580 [ShardingSphere-Command-10] ShardingSphere-SQL - Actual SQL: common ::: SELECT id, create_time, update_time, is_deleted, sort_idx, delete_time, key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nation, nation_key, native_place, native_place_key, is_certified, photo, person_photos, birthday, address, name_pinyin, age, work_phone, unit_code, unit_name, unit_key, work_time, residential_address, lng, lat, residence_community_name, residence_community_code, email, cellphone AS cellphone, contact, marriage, marriage_key, school, speciality, education, education_key, is_act_user, act_user_time, person_status, extend_field, is_permission_control_data, is_check_data, data_source_description, data_quality_integrity, data_quality_validity, data_quality_validity_alarm, card_type_name, card_type_key, card_code, before_name, zongjiao_name, zongjiao_key, work_name, work_key, huji_name, huji_key, blood_name, blood_key, height, is_farmer, is_laowu, is
_fuyi FROM `ssc_demo`.`base_person` LIMIT 0,1000
[ERROR] 12:15:41.690 [ShardingSphere-Command-10] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLSyntaxErrorException: 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 'key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nati' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:781)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.statement.accessor.impl.StatementAccessor.execute(StatementAccessor.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:84)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:48)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:76)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:56)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:99)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:95)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:80)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:66)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.executeWithDriver(JDBCExecuteEngine.java:131)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.execute(JDBCExecuteEngine.java:124)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.execute(JDBCExecuteEngine.java:104)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecute(JDBCDatabaseCommunicationEngine.java:92)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:78)
at org.apache.shardingsphere.proxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:55)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:57)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:76)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yl-yue commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
yl-yue commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-734053062
@jingshanglu Logical SQL has been added in the comments above.
Given my current familiarity with ShardingSphere, I have temporarily solved this problem with a clunky piece of code, but there should be more elegant solutions such as dialect recognition.
```java
package org.apache.shardingsphere.infra.rewrite.sql.impl;
public final class DefaultSQLBuilder extends AbstractSQLBuilder {
public DefaultSQLBuilder(final SQLRewriteContext context) {
super(context);
}
@Override
protected String getSQLTokenText(final SQLToken sqlToken) {
String[] columnNames = sqlToken.toString().split(",");
String[] keywords = {"key"};
StringBuffer sql = new StringBuffer();
for (String columnName : columnNames) {
boolean isKeyword = false;
for (String keyword : keywords) {
if (columnName.trim().equals(keyword.trim())) {
sql.append("`");
sql.append(columnName.trim());
sql.append("`,");
isKeyword = true;
break;
}
}
if (!isKeyword) {
sql.append(columnName);
sql.append(",");
}
}
String suffix = ",";
int end = sql.length();
int start = end - suffix.length();
String condition = sql.substring(start, end);
if (suffix.equals(condition)) {
return sql.delete(start, end).toString();
}
return sql.toString();
}
}
```
Before the change:
```java
@Override
protected String getSQLTokenText(final SQLToken sqlToken) {
return sqlToken.toString();
}
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-733409399
@yl-yue Can you show the logic sql?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yl-yue edited a comment on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
yl-yue edited a comment on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-732642987
sharding-proxy: 5.0.0-RC1-SNAPSHOT
```java
[INFO ] 11:17:32.885 [ShardingSphere-Command-11] ShardingSphere-SQL - Logic SQL: SELECT * FROM `ssc_demo`.`base_person` LIMIT 0,1000
[INFO ] 11:17:32.885 [ShardingSphere-Command-11] ShardingSphere-SQL - SQLStatement: MySQLSelectStatement(limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@36daddee], lock=Optional.empty)
[INFO ] 12:15:41.580 [ShardingSphere-Command-10] ShardingSphere-SQL - Actual SQL: common ::: SELECT id, create_time, update_time, is_deleted, sort_idx, delete_time, key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nation, nation_key, native_place, native_place_key, is_certified, photo, person_photos, birthday, address, name_pinyin, age, work_phone, unit_code, unit_name, unit_key, work_time, residential_address, lng, lat, residence_community_name, residence_community_code, email, cellphone AS cellphone, contact, marriage, marriage_key, school, speciality, education, education_key, is_act_user, act_user_time, person_status, extend_field, is_permission_control_data, is_check_data, data_source_description, data_quality_integrity, data_quality_validity, data_quality_validity_alarm, card_type_name, card_type_key, card_code, before_name, zongjiao_name, zongjiao_key, work_name, work_key, huji_name, huji_key, blood_name, blood_key, height, is_farmer, is_laowu, is
_fuyi FROM `ssc_demo`.`base_person` LIMIT 0,1000
[ERROR] 12:15:41.690 [ShardingSphere-Command-10] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLSyntaxErrorException: 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 'key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nati' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:781)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.statement.accessor.impl.StatementAccessor.execute(StatementAccessor.java:43)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:84)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:48)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:76)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:56)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:99)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:95)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:80)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:66)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.executeWithDriver(JDBCExecuteEngine.java:131)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.execute(JDBCExecuteEngine.java:124)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.execute.engine.jdbc.JDBCExecuteEngine.execute(JDBCExecuteEngine.java:104)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecute(JDBCDatabaseCommunicationEngine.java:92)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:78)
at org.apache.shardingsphere.proxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:55)
at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:57)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:76)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] yl-yue commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
yl-yue commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-732642987
sharding-proxy: 5.0.0-RC1-SNAPSHOT
```java
[INFO ] 12:15:41.580 [ShardingSphere-Command-10] ShardingSphere-SQL - Actual SQL: common ::: SELECT id, create_time, update_time, is_deleted, sort_idx, delete_time, key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nation, nation_key, native_place, native_place_key, is_certified, photo, person_photos, birthday, address, name_pinyin, age, work_phone, unit_code, unit_name, unit_key, work_time, residential_address, lng, lat, residence_community_name, residence_community_code, email, cellphone AS cellphone, contact, marriage, marriage_key, school, speciality, education, education_key, is_act_user, act_user_time, person_status, extend_field, is_permission_control_data, is_check_data, data_source_description, data_quality_integrity, data_quality_validity, data_quality_validity_alarm, card_type_name, card_type_key, card_code, before_name, zongjiao_name, zongjiao_key, work_name, work_key, huji_name, huji_key, blood_name, blood_key, height, is_farmer, is_laowu, is
_fuyi FROM `ssc_demo`.`base_person` LIMIT 0,1000
[ERROR] 12:15:41.690 [ShardingSphere-Command-10] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.sql.SQLSyntaxErrorException: 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 'key, id_card, card_start_time, card_end_time, name, sex, sex_key, sex_name, nati' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:781)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:103)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.statement.accessor.impl.StatementAccessor.execute(StatementAccessor.java:43)
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] terrymanu commented on issue #8296: shardingsphere-proxy select * from XXX error
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #8296:
URL: https://github.com/apache/shardingsphere/issues/8296#issuecomment-733039293
@jingshanglu Please check why the SQL exec in MySQL error but can pass parse engine.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org