gitcat
gitcat 提问于
2018-12-03  ·  5371 次阅读

SQL Error: 1064, SQLState: 42000 错误

0

在测试 Spingboot 和 JPA 集成的时候，发现全局异常处理器报了一个 SQL 语法的错误。

2018-12-01 10:26:50,728 WARN  [org.hibernate.util.JDBCExceptionReporter:77] - SQL Error: 1064, SQLState: 42000
2018-12-01 10:26:50,729 ERROR [org.hibernate.util.JDBCExceptionReporter:78] - 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 'read, id) values ('sdf', 'sdf', 'asdfsd', '2018-12-01 10:26:50', 23)' at line 1
2018-12-01 10:26:50,730 ERROR [org.hibernate.event.def.AbstractFlushingEventListener:301] - Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
    at org.hibernate.ejb.AbstractEntityManagerImpl$1.beforeCompletion(AbstractEntityManagerImpl.java:515)
    at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:114)
    at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.beforeCompletion(TwoPhaseCoordinator.java:247)
    at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:86)
    at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:177)
    at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1389)
    at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135)
    at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:87)
    at org.jboss.aspects.tx.TxPolicy.endTransaction(TxPolicy.java:175)
    at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:87)
    at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:191)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.aspects.tx.TxPropagationInterceptor.invoke(TxPropagationInterceptor.java:95)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.ejb3.stateless.StatelessInstanceInterceptor.invoke(StatelessInstanceInterceptor.java:62)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.aspects.security.AuthenticationInterceptor.invoke(AuthenticationInterceptor.java:77)
    at org.jboss.ejb3.security.Ejb3AuthenticationInterceptor.invoke(Ejb3AuthenticationInterceptor.java:110)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.ejb3.ENCPropagationInterceptor.invoke(ENCPropagationInterceptor.java:46)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.ejb3.asynchronous.AsynchronousInterceptor.invoke(AsynchronousInterceptor.java:106)
    at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:101)
    at org.jboss.ejb3.stateless.StatelessContainer.dynamicInvoke(StatelessContainer.java:304)
    at org.jboss.aop.Dispatcher.invoke(Dispatcher.java:106)
    at org.jboss.aspects.remoting.AOPRemotingInvocationHandler.invoke(AOPRemotingInvocationHandler.java:82)
    at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:809)
    at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:608)
    at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:406)
    at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:173)
Caused by: java.sql.BatchUpdateException: 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 'read, id) values ('sdf', 'sdf', 'asdfsd', '2018-12-01 10:26:50', 23)' at line 1
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2054)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1467)
    at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeBatch(StatementWrapper.java:721)
    at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
    ... 36 more
Caused by: 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 'read, id) values ('sdf', 'sdf', 'asdfsd', '2018-12-01 10:26:50', 23)' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2006)
    ... 41 more

这就是 MySQL 烦人的 SQL Error: 1064, SQLState: 42000 错误。

mysql-error-1064.jpg

我当时打断点把 JPA 生成的 SQL 拿到本地执行是可以的，反复分析真是百思不得其解。

举报
如果觉得这对你有用，请随意赞赏，给与作者支持
本站文章除注明转载外，均为本站原创或编译。转载请征得作者同意，并务必注明出处。
转载请注明：文章转载自 代码饭社区 [https://www.daimafans.com]
回答 (2)
2 个回答
0

回复@gitcat 说：赞你一个！😂

0

这个问题最后的分析出来是我的表字段中有一个 read 来表示内容是否已读，而这个字段名称是 MySQL 的关键字，所以在 JPA 生成 SQL 执行的时候报错了。

所以说如果 MySQL 报 SQL Error: 1064, SQLState: 42000 错误的时候，一定要先看一下是否字段或表名什么的使用了关键字。

我的解决方法是更改了 read 这个字段名称，从而避免了使用关键字的问题，如果必须使用，可以参考我刚刚更新的资料： 《MySQL SQL Error: 1064, SQLState: 42000 错误》