Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

The datasource proxy's problem #5291

Closed
1 task done
ZacharyBear opened this issue Feb 2, 2023 · 16 comments
Closed
1 task done

The datasource proxy's problem #5291

ZacharyBear opened this issue Feb 2, 2023 · 16 comments
Labels
task: help-wanted Extra attention is needed type: bug Category issues or prs related to bug.

Comments

@ZacharyBear
Copy link

ZacharyBear commented Feb 2, 2023 •
edited
Loading

  • I have searched the issues of this repository and believe that this is not a duplicate.

Ⅰ. Issue Description

Seata's datasource proxy's sql checker has a problem, it'll replace the character '`'.

Ⅱ. Describe what happened

There's a field named 'all' in our table. But all is a sql keyword. We use MyBatisPlus, I used put a @TableFiled("`all`") annotation to avoid the sql syntax error, and it works.
Today, I imorted seata to our project. When I execute the sql include field 'all', a SQLSyntaxErrorException has been threw. It gave a message: 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 'all,xxx FROM t_access_record ' at line 1. Seems, seata's datasource proxy replaced the characted '`'.
Normally, mybatis-plus don't replace the character. But it has dirty-reading problem. Seata resolve these distrubut-system's problem, but its datasource proxy is not ideal.

Ⅲ. Describe what you expected to happen

I want to have an option to decide whether the data source agent should replace some characters.

Ⅳ. How to reproduce it (as minimally and precisely as possible)

  1. Create an table, contains a field named 'all'
  2. Include MyBatis-Plus, put an anotation '@TableField("`all`")' to the field
  3. Enable GlobalTransaction, and use the field to execute update statement.

Ⅴ. Anything else we need to know?

No.

Ⅵ. Environment:

  • JDK version : 1.8
  • Seata version: 1.6.1
  • OS : Windows
  • Others: IDEA 2021.1, SpringBoot 2.3.12.RELEASE
@funky-eyes
Copy link
Contributor

Please provide the original field column names and sql and seata proxy generated sql and log details

@ZacharyBear
Copy link
Author

Select statements also make this problem:

trace info:


Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@28a2d9c5] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1b2daf7d] will not be managed by Spring
==>  Preparing: SELECT id,plan_id,is_shield,`all`,central,bottom,day,intime,inuser,uptime,upuser,deleted FROM t_access_record WHERE id=? AND deleted=0
==> Parameters: 1(Integer)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@28a2d9c5]
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'all' in 'field list'
### The error may exist in xxxMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,plan_id,is_shield,`all`,central,bottom,day,intime,inuser,uptime,upuser,deleted FROM t_access_record WHERE id=?  AND deleted=0
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'all' in 'field list'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'all' in 'field list'
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy126.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy127.selectById(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:344)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	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:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy129.selectById(Unknown Source)
	at xxx(xxx.java:26)
	at xxx$$FastClassBySpringCGLIB$$3b130068.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at io.seata.spring.annotation.GlobalTransactionalInterceptor$2.execute(GlobalTransactionalInterceptor.java:204)
	at io.seata.tm.api.TransactionalTemplate.execute(TransactionalTemplate.java:130)
	at io.seata.spring.annotation.GlobalTransactionalInterceptor.handleGlobalTransaction(GlobalTransactionalInterceptor.java:201)
	at io.seata.spring.annotation.GlobalTransactionalInterceptor.invoke(GlobalTransactionalInterceptor.java:171)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at xxx$$EnhancerBySpringCGLIB$$e449c658.plus(<generated>)
	at xxx.test(xxx.java:23)
	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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:893)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1707)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'all' in 'field list'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
	at io.seata.rm.datasource.PreparedStatementProxy.lambda$execute$0(PreparedStatementProxy.java:55)
	at io.seata.rm.datasource.exec.PlainExecutor.execute(PlainExecutor.java:49)
	at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:137)
	at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:56)
	at io.seata.rm.datasource.PreparedStatementProxy.execute(PreparedStatementProxy.java:55)
	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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy200.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
	at com.sun.proxy.$Proxy196.query(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
	at com.sun.proxy.$Proxy195.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76)
	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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	... 82 more

@ZacharyBear
Copy link
Author

Please provide the original field column names and sql and seata proxy generated sql and log details

Stack information as above

@funky-eyes
Copy link
Contributor

funky-eyes commented Feb 2, 2023 •
edited
Loading

This is a strange problem, PlainExecutor does not modify any sql of the business, have you tried the case of uncommenting globaltransational?

@ZacharyBear
Copy link
Author

This is a strange problem, PlainExecutor does not modify any sql of the business, have you tried the case of uncommenting globaltransational?

I just removed @GlobalTransactional annotation, and the programe worked right. There's no any exception been threw.

@ZacharyBear
Copy link
Author

This is a strange problem, PlainExecutor does not modify any sql of the business, have you tried the case of uncommenting globaltransational?

Actually, disable seata.enable-auto-data-source-proxy option can also resolve this exception. Well, does it means Seata's distributed transaction mechanism will fail？

@ZacharyBear
Copy link
Author

@a364176773
I've debug and looked the seata's source code up.
In io.seata.rm.datasource.exec.UpdateExecutor the method buildBeforeImageSQL had been called. At line 93, it means get the columns from TableData(get description from db). It is true that the colmun name has not been processed. Then the problem happends, all is a sql keyword.
Get The Columns

This is the generated sql:
Generate Result
(Ignore the yellow texts. MyBatis-Plus dont add `` character, but it'll check the column by the alias. Seata check from the table fields from db)

I thought seata will call the SelectForUpdateExecutor's execute method, but it didn't. That's why select statement wont make exceptions.

@funky-eyes
Copy link
Contributor

This is a strange problem, PlainExecutor does not modify any sql of the business, have you tried the case of uncommenting globaltransational?

Actually, disable seata.enable-auto-data-source-proxy option can also resolve this exception. Well, does it means Seata's distributed transaction mechanism will fail？

Yes

@funky-eyes
Copy link
Contributor

funky-eyes commented Feb 3, 2023 •
edited
Loading

@a364176773 I've debug and looked the seata's source code up. In io.seata.rm.datasource.exec.UpdateExecutor the method buildBeforeImageSQL had been called. At line 93, it means get the columns from TableData(get description from db). It is true that the colmun name has not been processed. Then the problem happends, all is a sql keyword. Get The Columns

This is the generated sql: Generate Result (Ignore the yellow texts. MyBatis-Plus dont add `` character, but it'll check the column by the alias. Seata check from the table fields from db)

I thought seata will call the SelectForUpdateExecutor's execute method, but it didn't. That's why select statement wont make exceptions.

So your SQL is select for update ? Why don't I see SelectForUpdateExecutor in your stack information?

@funky-eyes
Copy link
Contributor

This is a bug, but the information you provided is not quite accurate

@funky-eyes funky-eyes added type: bug Category issues or prs related to bug. task: help-wanted Extra attention is needed labels Feb 3, 2023
@funky-eyes
Copy link
Contributor

image
This code will handle keywords, can you debug this piece of code? UpdateExecutor#getNeedUpdateColumns

@slievrly
Copy link
Member

slievrly commented Feb 6, 2023

@ZenkieBear please give me more information about druid dependency.

@ZacharyBear
Copy link
Author

ZacharyBear commented Feb 6, 2023 via email

@funky-eyes
Copy link
Contributor

The problem exists and I will fix it, but the problem with select sql I did not reproduce successfully

@funky-eyes funky-eyes mentioned this issue Feb 6, 2023
Merged
1 task
@ZacharyBear
Copy link
Author

ZacharyBear commented Feb 6, 2023 via email

@ZacharyBear
Copy link
Author

ZacharyBear commented Feb 6, 2023 via email

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
task: help-wanted Extra attention is needed type: bug Category issues or prs related to bug.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@slievrly @funky-eyes @ZacharyBear