[JBoss JIRA] (TEIID-5801) Communications link failure during commit() error message when copying a MySQL table to a DB
9:06 a.m.
[
https://issues.jboss.org/browse/TEIID-5801?page=com.atlassian.jira.plugin...
]
Dmitrii Pogorelov reopened TEIID-5801:
--------------------------------------
[~shawkins] I'm afraid that you will have to re-open the issue. The fix introduced a
bug which causes a query to hang up. The problem is reproduced only for MSSQL, for other
databases I couldn't reproduce the problem.
Steps of workflow:
1. In MSSQL create the test_a table in test_dwh schema using the following scripts:
{code:sql}
USE [test_dwh]
GO
/****** Object: Table [dbo].[test_a] Script Date: 22.08.2019 15:53:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_a](
[a] [int] NULL,
[b] [int] NULL
) ON [PRIMARY]
GO
{code}
2. Add MSSQL database configuration in standalone-teiid.xml:
{code:xml}
<datasource jndi-name="java:/mssql-test-dwh"
pool-name="mssql-test-dwh" enabled="true"
use-java-context="true">
<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_dwh</connection-url>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<driver>com.microsoft.sqlserver</driver>
<new-connection-sql>SET ANSI_WARNINGS
OFF</new-connection-sql>
<pool>
<min-pool-size>2</min-pool-size>
<max-pool-size>70</max-pool-size>
</pool>
<security>
<user-name>XXXXX</user-name>
<password>XXXXX</password>
</security>
<validation>
<check-valid-connection-sql>select
1</check-valid-connection-sql>
</validation>
<timeout>
<blocking-timeout-millis>120000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>
</datasource>
{code}
3. Add in test-vdb.xml java:/mssql-test-dwh configured in previous step as datasource:
{code:xml}
<model name="ms_dwh">
<property name="importer.useFullSchemaName"
value="false"/>
<property name="importer.tableTypes"
value="TABLE,VIEW"/>
<property name="importer.importKeys" value="false"/>
<property name="importer.schemaPattern" value="dbo"/>
<source name="dwh_ms" translator-name="sqlserver"
connection-jndi-name="java:/mssql-test-dwh"/>
</model>
{code}
4. Run the following query:
{code:sql}
begin
insert into ms_dwh.test_a select 1, 1 ;
SELECT COUNT(*) FROM ms_dwh.test_a;
end ;;
{code}
the query will hang up.
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
Fix For: 12.3
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@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)
1964
days inactive
1964
days old
0 comments
1 participants
participants (1)
-
Dmitrii Pogorelov (Jira)