异常说明：
Caused by: io.shardingjdbc.core.exception.ShardingJdbcException: io.shardingjdbc.core.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: #42000
at io.shardingjdbc.core.executor.threadlocal.ExecutorExceptionHandler.handleException(ExecutorExceptionHandler.java:61)
at io.shardingjdbc.core.executor.ExecutorEngine.execute(ExecutorEngine.java:134)
at io.shardingjdbc.core.executor.ExecutorEngine.executePreparedStatement(ExecutorEngine.java:96)
at io.shardingjdbc.core.executor.type.prepared.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:93)
at io.shardingjdbc.core.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:141)
at sun.reflect.GeneratedMethodAccessor241.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.
P
r
o
x
y
288.
e
x
e
c
u
t
e
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
s
t
a
t
e
m
e
n
t
.
P
r
e
p
a
r
e
d
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
q
u
e
r
y
(
P
r
e
p
a
r
e
d
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
j
a
v
a
:
63
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
e
x
e
c
u
t
o
r
.
s
t
a
t
e
m
e
n
t
.
R
o
u
t
i
n
g
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
q
u
e
r
y
(
R
o
u
t
i
n
g
S
t
a
t
e
m
e
n
t
H
a
n
d
l
e
r
.
j
a
v
a
:
79
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
G
e
n
e
r
a
t
e
d
M
e
t
h
o
d
A
c
c
e
s
s
o
r
236.
i
n
v
o
k
e
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
43
)
a
t
j
a
v
a
.
l
a
n
g
.
r
e
f
l
e
c
t
.
M
e
t
h
o
d
.
i
n
v
o
k
e
(
M
e
t
h
o
d
.
j
a
v
a
:
498
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
p
l
u
g
i
n
.
P
l
u
g
i
n
.
i
n
v
o
k
e
(
P
l
u
g
i
n
.
j
a
v
a
:
63
)
a
t
c
o
m
.
s
u
n
.
p
r
o
x
y
.
Proxy288.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) at sun.reflect.GeneratedMethodAccessor236.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) at com.sun.proxy.
Proxy288.execute(UnknownSource)atorg.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)atorg.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)atsun.reflect.GeneratedMethodAccessor236.invoke(UnknownSource)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.lang.reflect.Method.invoke(Method.java:498)atorg.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)atcom.sun.proxy.Proxy287.query(Unknown Source)
at sun.reflect.GeneratedMethodAccessor236.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.
P
r
o
x
y
287.
q
u
e
r
y
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
G
e
n
e
r
a
t
e
d
M
e
t
h
o
d
A
c
c
e
s
s
o
r
236.
i
n
v
o
k
e
(
U
n
k
n
o
w
n
S
o
u
r
c
e
)
a
t
s
u
n
.
r
e
f
l
e
c
t
.
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
i
n
v
o
k
e
(
D
e
l
e
g
a
t
i
n
g
M
e
t
h
o
d
A
c
c
e
s
s
o
r
I
m
p
l
.
j
a
v
a
:
43
)
a
t
j
a
v
a
.
l
a
n
g
.
r
e
f
l
e
c
t
.
M
e
t
h
o
d
.
i
n
v
o
k
e
(
M
e
t
h
o
d
.
j
a
v
a
:
498
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
p
l
u
g
i
n
.
I
n
v
o
c
a
t
i
o
n
.
p
r
o
c
e
e
d
(
I
n
v
o
c
a
t
i
o
n
.
j
a
v
a
:
49
)
a
t
c
o
m
.
b
a
o
m
i
d
o
u
.
m
y
b
a
t
i
s
p
l
u
s
.
p
l
u
g
i
n
s
.
P
e
r
f
o
r
m
a
n
c
e
I
n
t
e
r
c
e
p
t
o
r
.
i
n
t
e
r
c
e
p
t
(
P
e
r
f
o
r
m
a
n
c
e
I
n
t
e
r
c
e
p
t
o
r
.
j
a
v
a
:
151
)
a
t
o
r
g
.
a
p
a
c
h
e
.
i
b
a
t
i
s
.
p
l
u
g
i
n
.
P
l
u
g
i
n
.
i
n
v
o
k
e
(
P
l
u
g
i
n
.
j
a
v
a
:
61
)
a
t
c
o
m
.
s
u
n
.
p
r
o
x
y
.
Proxy287.query(Unknown Source) at sun.reflect.GeneratedMethodAccessor236.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) at com.baomidou.mybatisplus.plugins.PerformanceInterceptor.intercept(PerformanceInterceptor.java:151) at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) at com.sun.proxy.
Proxy287.query(UnknownSource)atsun.reflect.GeneratedMethodAccessor236.invoke(UnknownSource)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.lang.reflect.Method.invoke(Method.java:498)atorg.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)atcom.baomidou.mybatisplus.plugins.PerformanceInterceptor.intercept(PerformanceInterceptor.java:151)atorg.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)atcom.sun.proxy.Proxy287.query(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
… 121 common frames omitted
Caused by: io.shardingjdbc.core.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: #42000
at io.shardingjdbc.core.executor.threadlocal.ExecutorExceptionHandler.handleException(ExecutorExceptionHandler.java:61)
at io.shardingjdbc.core.executor.ExecutorEngine.executeInternal(ExecutorEngine.java:188)
at io.shardingjdbc.core.executor.ExecutorEngine.syncExecute(ExecutorEngine.java:162)
at io.shardingjdbc.core.executor.ExecutorEngine.execute(ExecutorEngine.java:126)
… 154 common frames omitted
从其他地方找到如下解决办法：
执行这样的SQL语句可重现异常 select age, name from test group by name
mysql5.1，不报异常
mysql5.5，不报异常
mysql5.6，不报异常
mysql5.7.22，报异常（具体从5.7哪个版本开始还没有确定下来，网上有人说5.7.5）
mysql从5.7的某个版本开始调整了默认参数sql_mode值为：
mysql> show variables like ‘sql_mode’;
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
其中ONLY_FULL_GROUP_BY就是影响了上面SQL语句是否报异常
解决方法：
1、改写SQL语句，毕竟上面的写法不是标准的SQL语句
2、登陆mysql服务器，执行以下命令，在global与session级都修改
mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
mysql> set session sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
3、结合第2种方法，在my.cnf文件中，指定sql_mode的值：
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
注：2与3结合可不用重启服务器，且即使重启配置也依然有效。
经本人测试：
执行完2和3后还是不行，最后重启mysql数据库可以
扫一扫
6133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
