[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 commented on TEIID-5801:
------------------------------------------
[~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@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, 5 months
[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,
please reject the issue. It was a failure in my VDB, I had a missing END in a BEGIN/END block which the parser seems to have ignored.
> 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, 5 months
[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 6:35 PM:
---------------------------------------------------------------
Hello Steven,
I unfortunately had a bug in my curl script. Again too late for me yesterday :) All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 results in the relevant path in the INSERT trigger
{code:java}
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
{code}
Steven, please keep the issue on hold for a moment. It could be that I found an error in my database layout and that it therefore is not working. I come back on you as soon as it is clear to me what is going on.
was (Author: cjohn001):
Hello Steven,
I unfortunately had a bug in my curl script. Again too late for me yesterday :) All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 results in the relevant path in the INSERT trigger
{code:java}
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
{code}
> 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, 5 months
[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 5:49 PM:
---------------------------------------------------------------
Hello Steven,
I unfortunately had a bug in my curl script. Again too late for me yesterday :) All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 results in the relevant path in the INSERT trigger
{code:java}
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
{code}
was (Author: cjohn001):
Hello Steven,
I unfortunately had a bug in my curl script. All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
{code:java}
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
{code}
> 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, 5 months
[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 5:47 PM:
---------------------------------------------------------------
Hello Steven,
I unfortunately had a bug in my curl script. All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
{code:java}
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
{code}
was (Author: cjohn001):
Hello Steven,
I unfortunately had a bug in my curl script. All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
> 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, 5 months
[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 5:45 PM:
---------------------------------------------------------------
Hello Steven,
I unfortunately had a bug in my curl script. All three scripts, also the curlCmdDK3.sh had to work. Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
was (Author: cjohn001):
Hello Steven,
I unfortunately had a bug in my curl script. Also the curlCmdDK3.sh had to work.
Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
> 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, 5 months
[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 5:45 PM:
---------------------------------------------------------------
Hello Steven,
I unfortunately had a bug in my curl script. Also the curlCmdDK3.sh had to work.
Please use the following command. Then you will see the error message.
fkDatabaseKey":3 is the relevant path in the INSERT trigger
curl --user user:pwd -i -X POST -H 'Content-Type: application/json;charset=UTF-8;IEEE754Compatible=true' -d '{"fkProfile":"1","AddedDateTime":"2019-07-05T19:12:55Z","MealNumber":"5","AmountInG":20.31,"fkDatabaseKey":3,"product_name":"Ein Produkt","energy_100g":2259,"carbohydrates_100g":60.08,"proteins_100g":24.03,"fat_100g":15.89}' http://localhost:18080/odata4/svc/my_nutri_diary/Diary
was (Author: cjohn001):
Hello Steven,
deleting the .m2 folder did the job on the MAC as well. Sorry for bothering you with the issue and thanks for your help!
Best regards,
Christoph
> 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, 5 months
[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 5:43 PM:
---------------------------------------------------------------
Please see next comment. This one here was wrong
was (Author: cjohn001):
Hello Steven,
I have built Teiid now on my linux machine. Indeed the commands are working with this build. I would realy also like to get things working on my development notebook. Might it be that there are some dependencies which are not updated with mvn clean install? Or is it known that my java version is too old or that the oracle JRE is not supported?
I have now delete the entire .m2 folder and download all dependencies again. My hope is that this fixes the issue on my mac.
> 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, 5 months
[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:
---------------------------------------
> that is eventually the commit command was executed (if I'm not mistaken).
You are changing when the exception is happening and that makes a lot of difference to the execution model.
In the standard case a JTA transaction is being created and the connection is being associated with it. If the connection errors out on commit, then the transaction is considered as having failed and the exception is passed up from there.
In the native case there's no JTA transaction that has been started and the exception is instead happening when you are obtaining a connection from the pool, which the pool layer will just swallow.
> The "insert into .... select *" command will be divided on two commands: select and insert.
Correct, unless it's all from the same source and the source supports an insert with a query expression.
> But if I call aqr.setSerial(false); for select command and aqr.setSerial(true); for insert command then the "insert into .... select *" doesn't fail, transaction is being committed without errors (I attached server_2_works.log). Could you tell me, please, why does aqr.setSerial method allow to avoid transaction errors?
The serialization of executions under a transaction is there to guard against corner cases in transactional branch scenarios, when using the same source multiple times under a transaction (since it will be the same connection object which are generally not thread safe), and so that the thread bound transaction is easily used/propagated. It depends upon several of factors whether it's safe/advisable to allow non-serial execution under a transaction - the code already checks to see if it's a non-transaction resource and will allow that to be executed concurrently. 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.
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.
Otherwise we would need more logic or a hint to know when to allow the parallel execution.
> 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@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, 5 months
[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,
deleting the .m2 folder did the job on the MAC as well. Sorry for bothering you with the issue and thanks for your help!
Best regards,
Christoph
> 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, 5 months