[teiid-issues] [JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
Dmitrii Pogorelov (Jira)
issues at jboss.org
Mon Aug 5 07:19:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13766904#comment-13766904 ]
Dmitrii Pogorelov edited comment on TEIID-5801 at 8/5/19 7:18 AM:
------------------------------------------------------------------
[~shawkins] thx a lot for your reply.
> So one thing that you can override if you want is to set the transactionSupport property on the mysql translator to NONE - but that may not be what you want generally.
yes, exactly, setting transactionSupport property on the mysql translator to NONE is not what I want to do.
I have the following two thoughts about current situation:
1)
> If however you are dealing with an XA or local txn resource, they should still participate in the transaction if they are executed more than 1 statement or a non read-only statement. Unfortunately the code is making the check for serial execution for a single source command and so assumes the worst - it doesn't know the full processor plan at that point.
In my case statement contains two data sources, one is participated on read-only statement (MySQL) another one is participated in insert command (not read-only statement). Is it possible to check anyhow how does teiid use every data source in a statement? I mean, for example, one data source is used only for reading, then we can use parallel execution, another one is used only for changing data and in this case the data source can't use parallel execution.
2) About my case, I have the following suspicious what is going on there: it seems that the MySQL data base or whatever is using there MySQL driver (MySQL or Maria DB) closes a connection after some timeout. The main problem in "insert into ... select *" command in Teiid is that data source which we read data from should wait now until the second data source finishes inserts, though transaction from the first data source can't help Teiid to rollback changes done in the second data source. MySQL DB (I don't know why, I tried to reproduce the strange situation when connections are closed after some time) sees some connection in waiting state and closes them. Is it possible to implement such a check not to wait for another data sources if they are working independently?
I think if we add a smarter logic on parallel execution from the first point where I mentioned about it above and stop to wait for ending of insert operations which are being executed in scope of another data source it will solve my problem and maybe the same problems of another people in the future.
was (Author: dalex005):
[~shawkins] thx a lot for your reply.
> So one thing that you can override if you want is to set the transactionSupport property on the mysql translator to NONE - but that may not be what you want generally.
yes, exactly, setting transactionSupport property on the mysql translator to NONE is not what I want to do.
I have the following two thoughts about current situation:
1)
> If however you are dealing with an XA or local txn resource, they should still participate in the transaction if they are executed more than 1 statement or a non read-only statement. Unfortunately the code is making the check for serial execution for a single source command and so assumes the worst - it doesn't know the full processor plan at that point.
In my case statement contains two data sources, one is participated on read-only statement (MySQL) another one is participated in insert command (not read-only statement). Is it possible to check anyhow how does teiid use every data source in a statement? I mean, for example, one data source is used only for reading, then we can use parallel execution, another one is used only for changing data and this case the data source can't use parallel execution.
2) About my case, I have the following suspicious what is going on there: it seems that the MySQL data base or whatever is using there MySQL driver (MySQL or Maria DB) closes a connection after some timeout. The main problem in "insert into ... select *" command in Teiid is that data source which we read data from should wait now until the second data source finishes inserts, though transaction from the first data source can't help Teiid to rollback changes done in the second data source. MySQL DB (I don't know why, I tried to reproduce the strange situation when connections are closed after some time) sees some connection in waiting state and closes them. Is it possible to implement such a check not to wait for another data sources if they are working independently?
I think if we add a smarter logic on parallel execution from the first point where I mentioned about it above and stop to wait for ending of insert operations which are being executed in scope of another data source it will solve my problem and maybe the same problems of another people in the future.
> Communications link failure during commit() error message when copying a MySQL table to a DB
> --------------------------------------------------------------------------------------------
>
> Key: TEIID-5801
> URL: https://issues.jboss.org/browse/TEIID-5801
> Project: Teiid
> Issue Type: Quality Risk
> Components: Query Engine
> Affects Versions: 12.0
> Environment: teiid-12.0.0 on WildFly Full 14.0.1.Final (WildFly Core 6.0.2.Final)
> Reporter: Dmitrii Pogorelov
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: server_1_fail.log, server_2_works.log, server_teiid.log
>
>
> When copying a MySQL table, for example, to PostgreSQL:
> {code:sql}
> insert into dwh_pg.test_target SELECT * FROM my.test_source ;;
> {code}
> in the end of the process Teiid throws out the following stacktrace (though rows are inserted in PostgreSQL successfully, seems that Teiid can't close read transaction for MySQL):
> {code}
> 2019-08-01 16:48:23,119 WARN [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (Worker3_QueryProcessorQueue34) TidBkmeGWJN8 IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener at 75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection at 5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool at ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool at 338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl at 1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization at 1367866468{tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a8008c:33252ff9:5d42fad3:11 status: ActionStatus.PREPARING >, owner=Local transaction context for provider JBoss JTA transaction provider) wasTrackByTx=true enlisted=true cancel=false}]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1700) at org.jboss.jca.adapters.jdbc.local.LocalManagedConnection.commit(LocalManagedConnection.java:96) at org.jboss.jca.core.tx.jbossts.LocalXAResourceImpl.commit(LocalXAResourceImpl.java:172) at com.arjuna.ats.internal.jta.resources.arjunacore.XAOnePhaseResource.commit(XAOnePhaseResource.java:120) at com.arjuna.ats.internal.arjuna.abstractrecords.LastResourceRecord.topLevelPrepare(LastResourceRecord.java:152) at com.arjuna.ats.arjuna.coordinator.BasicAction.doPrepare(BasicAction.java:2664) at com.arjuna.ats.arjuna.coordinator.BasicAction.doPrepare(BasicAction.java:2614) at com.arjuna.ats.arjuna.coordinator.BasicAction.prepare(BasicAction.java:2157) at com.arjuna.ats.arjuna.coordinator.BasicAction.End(BasicAction.java:1503) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:96) at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:162) at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1288) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:126) at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:89) at org.wildfly.transaction.client.LocalTransaction.commitAndDissociate(LocalTransaction.java:77) at org.wildfly.transaction.client.ContextTransactionManager.commit(ContextTransactionManager.java:71) at org.teiid.dqp.internal.process.TransactionServerImpl.commitDirect(TransactionServerImpl.java:384) at org.teiid.dqp.internal.process.TransactionServerImpl.commit(TransactionServerImpl.java:515) 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.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:117) at com.sun.proxy.$Proxy25.commit(Unknown Source) at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:514) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:43) at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285) at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:281) at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:113)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:199) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)
> {code}
> I tried to reproduce the problem with local MySQL and PostgreSQL but couldn't. The problem can be reproduced only when using remote MySQL and PostgreSQL. On my local machine the error appears with limit more than 200000 rows, on another machines the exception appeared when setting limit 400000 and more. It seems it's related maybe somehow with MySQL timeouts or network delays. If I copy the table from remote MySQL to local PostgeSQL the error doesn't appear, and vice versa, if I copy the table from local MySQL to remote PostgreSQL the error doesn't appear again. I don't have an access to the remote MySQL to have a look at its internal options. I also tried to set net_write_timeout=1800 jdbc property for data source of the remote MySQL, tcpKeepAlive=true, tried to set ThreadBound MySQL translator property to true value - it didn't help at all. What do you think, is it possible to avoid the error on Teiid level?
> I also attached a server log with org.teiid.CONNECTOR and org.teiid.PROCESSOR log outputs.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
More information about the teiid-issues
mailing list