软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
  软件世界网 -> 数据库 -> mysqlerrorcode'1064'排查过程 -> 正文阅读

[数据库]mysqlerrorcode'1064'排查过程


     下午自测代码,在这个update上卡了一个半小时,大大的降低了开发的生产力,把排查过程发出来,好的士兵不会掉进同一个陷阱。先把异常堆栈打出来。

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 [com.jd.im.data.dataresource.JdDataSourceDbcp@14021a9]
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:217] - Database product name cached for DataSource [com.jd.im.data.dataresource.JdDataSourceDbcp@14021a9]: name is 'MySQL'
2016-03-28 17:23:38.423 main DEBUG [org.springframework.jdbc.support.SQLErrorCodesFactory:175] - SQL error codes for 'MySQL' found
2016-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 have 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 = 12' at line 1]; SQL was [] 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 exception
com.jd.im.data.exception.IMDataAccessException: IM data access exception
	at 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 parameter map.  
--- Check the mall.org.updateById-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: 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 'WHERE id = 12' at line 1
	at 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)
	... 54 more
Caused 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 have 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 = 12' at line 1
	at 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)
	... 56 more

    还有我的Ibatis的SQL


<update id="updateById"  parameterClass="Org">
		UPDATE org
		SET	name = #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>

    像这种堆栈没有明确指明原因的sql问题,排查问题肯定是根据error code去查。百度了下这个1064 error code。说是表的列名跟Mysql的关键字重叠了。OK,我把这个sql贴近mysql客户端的查询界面,发现name字段变成了关键字的颜色。我以为找到问题了,于是把name前后加反引号。再跑下单元测试,依然报1064错误。

    没辙,只能把ibatis的执行的sql打出来,放进mysql客户端看看。OK,log4j加日志,把ibatis的执行sql打出来。再跑一遍单元测试,sql并没有打出来。
    有种叫天天不应叫地地不灵的沮丧感!
    没办法,只能肉眼看sql。如果editor字段为null,那么where前面就多个多余的逗号了。调整下sql。

	<update id="updateById"  parameterClass="Org">
		UPDATE org
		SET 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` + 1
		WHERE id = #id#
	</update>

    在跑下单元测试,sql成功的执行。mysql的error code一点都不靠谱!明明是sql语法有问题,却报1064的code。
    把name和version字段上的反引号去掉,sql依然可以正常的执行。吐槽!
......显示全文...
    点击查看全文


上一篇文章      下一篇文章      查看所有文章
2016-03-28 21:48:11  
数据库 最新文章
Python&MySQL&PyQt
最新用python来操作mysql完全解析
mongodb的安装详解
1.PDO简介
《MySQL必知必会学习笔记》:高级联结
【翻译自mos文章】怎么对Microsoft(Office)
MyCAT全局表描述及示例
ocp
关于SQL数据表存储过程表名前缀换成dbo代码
数据库调优教程(二)慢查询数据准备
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 新闻资讯 小游戏 Chinese Culture 股票 三丰软件 开发 中国文化 网文精选 阅读网 看图 日历 万年历 2018年11日历
2018-11-14 19:43:55
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --