软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
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年7日历
2018-7-19 13:47:14
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --