~~
查询时,表连接的连接字段在数据库中不存在,导致bad SQL grammar []、Unknown error 1054 。
~~
查询时线上日志报错bad SQL grammar [];
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054
### The error may exist in URL [jar:file:/src/middle-customer-service-provider/middle-customer-service-provider-1.0.0.jar!/BOOT-INF/classes!/mapper/MiaoPlusOrderMapper.xml]
### The error may involve cn.miao.middle.customer.dao.miaoPlus.MiaoPlusOrderDao.selectOrderByUserId-Inline
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM tbl_order o LEFT JOIN tbl_order_item toi ON o.order_sn = toi.order_sn WHERE o.profile_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054, dubbo version: 2.8.4, current host: ...
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054
### The error may exist in URL [jar:file:/src/middle-customer-service-provider/middle-customer-service-provider-1.0.0.jar!/BOOT-INF/classes!/mapper/MiaoPlusOrderMapper.xml]
### The error may involve cn.miao.middle.customer.dao.miaoPlus.MiaoPlusOrderDao.selectOrderByUserId-Inline
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM tbl_order o LEFT JOIN tbl_order_item toi ON o.order_sn = toi.order_sn WHERE o.profile_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1054
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy70.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy90.selectOrderByUserId(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy91.selectOrderByUserId(Unknown Source)
at cn.miao.middle.customer.service.OrderServcieImpl.getOrderListByUserIdAndOrgType(OrderServcieImpl.java:43)
at com.alibaba.dubbo.common.bytecode.Wrapper7.invokeMethod(Wrapper7.java)
at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)
本地调用该方法,没有问题;定位到sql,将sql在本地数据库执行,依旧是没有问题。
<select id="selectOrderByUserId" parameterType="java.lang.Long" resultMap="couponMap">
SELECT o.id,toi.commodity_name,toi.commodity_amount
FROM tbl_order o
LEFT JOIN tbl_order_item toi ON o.sn=toi.order_sn
WHERE o.profile_id = #{userId}
ORDER BY o.pay_time DESC
</select>
将sql在线上数据库执行时,发现error。查看数据库后发现线上tbl_order_item这张表没有连接字段order_sn。
即线上数据库与本地数据库字段不一致,导致bad SQL grammar [];Unknown error 1054;