MySQL error code ' 1064 ' Troubleshooting process

PM Self-Test code, the update on the card for 1.5 hours, greatly reducing the development of productivity, the process is sent out, good soldiers will not fall into the same trap. First, the exception stack is called out.

2016-03-28 17:23:38.420 main DEBUG [org.springframework.beans.factory.support.defaultlistablebeanfactory:463]- Finished creating instance of Bean ' Sybase ' 2016-03-28 17:23:38.420 main INFO [ ORG.SPRINGFRAMEWORK.JDBC.SUPPORT.SQLERRORCODESFACTORY:126]-Sqlerrorcodes loaded: [DB2, Derby, H2, HSQL, Informix, Ms-sql, MySQL, Oracle, PostgreSQL, sybase]2016-03-28 17:23:38.420 main DEBUG [ ORG.SPRINGFRAMEWORK.JDBC.SUPPORT.SQLERRORCODESFACTORY:199]-Looking up default sqlerrorcodes for DataSource [[email& nbsp;protected]]2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.sqlerrorcodesfactory:217]- Database product name cached for DataSource [[[email protected]]: name is ' MySQL ' 2016-03-28 17:23:38.423 main DEBUG [o RG.SPRINGFRAMEWORK.JDBC.SUPPORT.SQLERRORCODESFACTORY:175]-SQL error codes for ' MySQL ' found2016-03-28 17:23:38.423 Main DEBUG [org.springframework.jdbc.support.sqlerrorcodesqlexceptiontranslator:399]-translating SQLException with SQL state ' 42000 ', error code ' 1064 ', message [---The error occurred while applying a parameter map.  ---Check the mall.org.updatebyid-inlineparametermap.  ---Check the statement (update failed). ---Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' WHERE id = ' in line 1] ; SQL is [] for task [sqlmapclient operation]2016-03-28 17:23:38.424 main ERROR [ Com.jd.dd.mall.service.waiter.impl.OrgService? IMPL:97]-IM data access Exceptioncom.jd.im.data.exception.IMDataAccessException:IM data access Exceptionat Com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.handleUserException ( imsqlmapclienttemplateformultds.java:280) at Com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update ( imsqlmapclienttemplateformultds.java:160) at Com.jd.dd.mall.service.waiter.impl.OrgService? Impl.updatebyid (Orgservice? impl.java:106) at Com.jd.dd.mall.service.waiter.impl. Orgservice? Impl.add (Orgservice? impl.java:92) at Com.jd.dd.mall.web.controller.waiter.OrgManageController.addOrg (orgmanagecontroller.java:85) at SUN.REFLECT.NATIVEMETHODACCESSORIMPL.INVOKE0 (Native Method) at Sun.reflect.NativeMethodAccessorImpl.invoke ( nativemethodaccessorimpl.java:39) at Sun.reflect.DelegatingMethodAccessorImpl.invoke ( DELEGATINGMETHODACCESSORIMPL.JAVA:25) at Java.lang.reflect.Method.invoke (method.java:597) at Org.springframework.web.method.support.InvocableHandlerMethod.invoke (invocablehandlermethod.java:219) at Org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest (invocablehandlermethod.java:132 ) at Org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle ( SERVLETINVOCABLEHANDLERMETHOD.JAVA:104) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod ( requestmappinghandleradapter.java:746) at Org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlEradapter.handleinternal (requestmappinghandleradapter.java:687) at Org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle ( ABSTRACTHANDLERMETHODADAPTER.JAVA:80) at Org.springframework.web.servlet.DispatcherServlet.doDispatch ( dispatcherservlet.java:925) at Org.springframework.web.servlet.DispatcherServlet.doService ( dispatcherservlet.java:856) at Org.springframework.web.servlet.FrameworkServlet.processRequest ( frameworkservlet.java:915) at Org.springframework.web.servlet.FrameworkServlet.doPost (frameworkservlet.java:822) At Javax.servlet.http.HttpServlet.service (httpservlet.java:727) at Org.springframework.web.servlet.FrameworkServlet.service (frameworkservlet.java:796) at Org.springframework.test.web.servlet.TestDispatcherServlet.service (testdispatcherservlet.java:66) at Javax.servlet.http.HttpServlet.service (httpservlet.java:820) at org.springframework.mock.web.mockfilterchain$ Servletfilterproxy.dofilter (mockfilterchain.java:168) at Org.springframework.mock.web.MockFilterChaiN.dofilter (mockfilterchain.java:136) at Org.springframework.test.web.servlet.MockMvc.perform (mockmvc.java:134) at Com.jd.service.waiter.OrgControllerTest.add (orgcontrollertest.java:53) at SUN.REFLECT.NATIVEMETHODACCESSORIMPL.INVOKE0 (Native Method) at Sun.reflect.NativeMethodAccessorImpl.invoke ( nativemethodaccessorimpl.java:39) at Sun.reflect.DelegatingMethodAccessorImpl.invoke ( DELEGATINGMETHODACCESSORIMPL.JAVA:25) at Java.lang.reflect.Method.invoke (method.java:597) at Org.junit.runners.model.frameworkmethod$1.runreflectivecall (frameworkmethod.java:44) at Org.junit.internal.runners.model.ReflectiveCallable.run (reflectivecallable.java:15) at Org.junit.runners.model.FrameworkMethod.invokeExplosively (frameworkmethod.java:41) at Org.junit.internal.runners.statements.InvokeMethod.evaluate (INVOKEMETHOD.JAVA:20) at Org.junit.internal.runners.statements.RunBefores.evaluate (runbefores.java:28) at Org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate ( Runbeforetestmethodcallbacks.java:74) at Org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate ( runaftertestmethodcallbacks.java:83) at Org.springframework.test.context.junit4.statements.SpringRepeat.evaluate (springrepeat.java:72) at Org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild ( springjunit4classrunner.java:231) at Org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild ( springjunit4classrunner.java:88) at Org.junit.runners.parentrunner$3.run (parentrunner.java:193) at Org.junit.runners.parentrunner$1.schedule (parentrunner.java:52) at Org.junit.runners.ParentRunner.runChildren ( parentrunner.java:191) at org.junit.runners.parentrunner.access$000 (parentrunner.java:42) at Org.junit.runners.parentrunner$2.evaluate (parentrunner.java:184) at Org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate ( RUNBEFORETESTCLASSCALLBACKS.JAVA:61) at Org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(runaftertestclasscallbacks.java:71) at Org.junit.runners.ParentRunner.run (parentrunner.java:236) at Org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run (springjunit4classrunner.java:174) at Org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run (JUNIT4TESTREFERENCE.JAVA:50) at Org.eclipse.jdt.internal.junit.runner.TestExecution.run (testexecution.java:38) at Org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests (remotetestrunner.java:459) at Org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests (remotetestrunner.java:675) at Org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run (remotetestrunner.java:382) at Org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main (remotetestrunner.java:192) caused by: Org.springframework.jdbc.BadSqlGrammarException:SqlMapClient operation; Bad SQL Grammar []; Nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:---The error occurred while applying a Paramet  ER map. ---Check the MALL.ORG.UPDATebyid-inlineparametermap.  ---Check the statement (update failed). ---Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You has an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE ID = 1a T Org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate ( sqlerrorcodesqlexceptiontranslator.java:233) at Org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate ( abstractfallbacksqlexceptiontranslator.java:72) at Org.springframework.orm.ibatis.SqlMapClientTemplate.execute ( sqlmapclienttemplate.java:206) at Org.springframework.orm.ibatis.SqlMapClientTemplate.update ( sqlmapclienttemplate.java:381) at Com.jd.im.data.dataresource.IMSqlMapClientTemplateForMultDs.update ( imsqlmapclienttemplateformultds.java:134) ...  Morecaused by:com.ibatis.common.jdbc.exception.NestedSQLException:---The error occurred while applying a parameter  Map. ---Check the Mall.org.updatebyid-inlineparametermap.---Check the statement (update failed). ---Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:You has an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE ID = 1a T Com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate (mappedstatement.java:107) at Com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update (sqlmapexecutordelegate.java:457) at Com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update (sqlmapsessionimpl.java:90) at Org.springframework.orm.ibatis.sqlmapclienttemplate$9.doinsqlmapclient (sqlmapclienttemplate.java:383) at Org.springframework.orm.ibatis.sqlmapclienttemplate$9.doinsqlmapclient (sqlmapclienttemplate.java:381) at Org.springframework.orm.ibatis.SqlMapClientTemplate.execute (sqlmapclienttemplate.java:203) ... More

and my Ibatis sql.

<update id= "Updatebyid"  parameterclass= "Org" >update orgsetname = #name: Varchar#,<isnotnull property= " Tree_level ">tree_level = #tree_level #, </isnotnull><isnotnull property=" yn ">yn = #yn #, </isnotnull ><isnotnull property= "route" >route = #route: varchar#, </isNotNull> ' version ' = ' version ' + 1,< Isnotnull property= "Editor" >editor = #editor: varchar#  </isnotnull>where id = #id #</update>

Like this stack does not explicitly specify the cause of the SQL problem, the troubleshooting must be based on error code to check. Baidu took this 1064 error code. It says that the table's column names overlap with the MySQL keyword. OK, I put this SQL close to the MySQL client's query interface and found that the name field became the color of the keyword. I thought I found the problem, so I put the name back and forth in the quotation marks. Run the unit test again, still reported 1064 error.

No, can only put ibatis execution of SQL out, put into the MySQL client to see. ok,log4j Add the log, the Ibatis execution SQL to fight out. Run the unit test again, and SQL didn't hit it.

There is a kind of frustration that calls every day not to be called ground!

No way, only the naked eye to see SQL. If the editor field is null, then the where is preceded by a number of extra commas. Adjust the next SQL.

<update id= "Updatebyid"  parameterclass= "ORG" >update orgset id = #id #, <isnotnull property= "name" > ' Name ' = #name: varchar#, </isnotnull><isnotnull property= "tree_level" >tree_level = #tree_level #, </ Isnotnull><isnotnull property= "yn" >yn = #yn #, </isnotnull><isnotnull property= "route" >route = # route:varchar#, </isnotnull><isnotnull property= "editor" >editor = #editor: varchar#, </isNotNull> ' Version ' = ' version ' + 1WHERE id = #id #</update>

after running the unit test, SQL executes successfully. MySQL error code is not a reliable! Obviously the SQL syntax has a problem, but reported 1064 code.

Remove the anti-quotes on the name and version fields, and SQL will still execute normally. Spit Groove!

MySQL error code ' 1064 ' Troubleshooting process