[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5799 at 8/2/19 11:32 AM:
----------------------------------------------------------------
Hello Steven,
only the third script should produce an error.
I am not sure what you mean with run against master. So you did not use the docker-compose file to run the Teiid server and the mysql db in the containers?
was (Author: cjohn001):
Hello Steven,
only the third script should produce an error. I am not sure what you mean with ran against master. So you did not use the docker-compose file to run things in the containers?
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5799:
---------------------------------------
Hello Steven,
only the third script should produce an error. I am not sure what you mean with ran against master. So you did not use the docker-compose file to run things in the containers?
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5799:
---------------------------------------
I simply extracted your scenario ddl and ran against master - none of your script scenarios produce an error.
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5799?page=com.atlassian.jira.plugin... ]
Work on TEIID-5799 started by Steven Hawkins.
---------------------------------------------
> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
> Key: TEIID-5799
> URL: https://issues.jboss.org/browse/TEIID-5799
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 13.0
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Blocker
> Attachments: svc-vdb-example.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5801:
----------------------------------
Issue Type: Quality Risk (was: Bug)
Priority: Major (was: Blocker)
> 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_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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5801:
---------------------------------------
> What do you think, is it possible to avoid the error on Teiid level?
This seems well below our level. You'll probably need to find the appropriate mysql timeout, for example:
https://confluence.atlassian.com/confkb/attachment-upload-failed-with-com...
> 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: Bug
> 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: Blocker
> Attachments: 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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5801:
-------------------------------------
Description:
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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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.
was:
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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
> 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: Bug
> 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: Blocker
> Attachments: 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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5801:
-------------------------------------
Attachment: server_teiid.log
> 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: Bug
> 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: Blocker
> Attachments: 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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Dmitrii Pogorelov (Jira)
[ https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin... ]
Dmitrii Pogorelov updated TEIID-5801:
-------------------------------------
Description:
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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
was:
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:noformat}
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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
> 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: Bug
> 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: Blocker
>
> 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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0
[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
by Dmitrii Pogorelov (Jira)
Dmitrii Pogorelov created TEIID-5801:
----------------------------------------
Summary: 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: Bug
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
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:noformat}
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@75284e6d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@5b8d92c7 connection handles=0 lastReturned=1564670796599 lastValidated=1564670796598 lastCheckedOut=1564670796678 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@ae21718 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@338041b4[pool=lingoda_read_replica] xaResource=LocalXAResourceImpl@1fcd6b81[connectionListener=75284e6d connectionManager=20f22ec1 warned=false currentXid=null productName=MySQL productVersion=5.6.34-log jndiName=java:/lingoda_read_replica] txSync=TransactionSynchronization@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?
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
5 years, 7 months
- 1
- 0