[JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5402?page=com.atlassian.jira.plugin... ]
Work on TEIID-5402 started by Steven Hawkins.
---------------------------------------------
> UPSERT in batch mode.
> ---------------------
>
> Key: TEIID-5402
> URL: https://issues.jboss.org/browse/TEIID-5402
> Project: Teiid
> Issue Type: Bug
> Reporter: Kulbhushan Chaskar
> Assignee: Steven Hawkins
> Labels: Teiid
> Fix For: 11.0
>
>
> Unable to execute UPSERT in batch. If batch size is 1, then it executes well. If batch size greater than 1 then getting logs.
> I checked with MySQL database and SQLServer database.
> *MySQL logs:*
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `aakash_test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> 9:15:50,309 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue55) ws3B9oHdsaA9 Connector worker process failed for atomic-request=ws3B9oHdsaA9.0.6.11: org.teiid.
> ranslator.TranslatorBatchException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`acco
> nt` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`,
> pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> aused by: org.teiid.translator.jdbc.JDBCExecutionException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT
> NTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCO
> NT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> aused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us
> near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> 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:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
> at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio
> for the right syntax to use near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> 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:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
> at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
> ... 25 more
> 9:15:50,338 WARN [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue56) ws3B9oHdsaA9 TEIID30020 Processing exception for request ws3B9oHdsaA9.0 'TEIID30504 MySqlConn_c
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> *SQLServer Logs:*
> count 44.
> 13:05:13,738 INFO [org.teiid.PROCESSOR.MATVIEWS] (Worker0_QueryProcessorQueue4) o8FeDIzU1i/F TEIID30014 Loaded materialized view table #MAT_PG_CATALOG.MATPG_DATATYPE wi
> th row count 52.
> 13:08:38,311 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue14) L+yP/GL+aiOm Connector worker process failed for atomic-request=L+yP/GL+aiOm.0.6.3: org.teiid.tr
> anslator.TranslatorBatchException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("cus
> tid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya']
> SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO
> "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prep
> ared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> Caused by: java.sql.BatchUpdateException: Incorrect syntax near the keyword 'INTO'.
> at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> 13:08:38,397 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) L+yP/GL+aiOm TEIID30020 Processing exception for request L+yP/GL+aiOm.0 'TEIID30504 sql_server:
> 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Pr
> epared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_ta
> b" ("custid", "custname") VALUES (?, ?)]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'INTO'.' JtdsStatement.java:1069. Enable more detailed
> logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5402?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5402:
----------------------------------
Fix Version/s: 11.0
> UPSERT in batch mode.
> ---------------------
>
> Key: TEIID-5402
> URL: https://issues.jboss.org/browse/TEIID-5402
> Project: Teiid
> Issue Type: Bug
> Reporter: Kulbhushan Chaskar
> Assignee: Steven Hawkins
> Labels: Teiid
> Fix For: 11.0
>
>
> Unable to execute UPSERT in batch. If batch size is 1, then it executes well. If batch size greater than 1 then getting logs.
> I checked with MySQL database and SQLServer database.
> *MySQL logs:*
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `aakash_test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> 9:15:50,309 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue55) ws3B9oHdsaA9 Connector worker process failed for atomic-request=ws3B9oHdsaA9.0.6.11: org.teiid.
> ranslator.TranslatorBatchException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`acco
> nt` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`,
> pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> aused by: org.teiid.translator.jdbc.JDBCExecutionException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT
> NTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCO
> NT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> aused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us
> near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> 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:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
> at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
> at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio
> for the right syntax to use near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
> 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:404)
> at com.mysql.jdbc.Util.getInstance(Util.java:387)
> at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
> at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
> at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
> at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
> at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
> at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
> at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
> at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
> ... 25 more
> 9:15:50,338 WARN [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue56) ws3B9oHdsaA9 TEIID30020 Processing exception for request ws3B9oHdsaA9.0 'TEIID30504 MySqlConn_c
> py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
> , `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
> ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
> ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
> ore detailed logging to see the entire stacktrace.
> *SQLServer Logs:*
> count 44.
> 13:05:13,738 INFO [org.teiid.PROCESSOR.MATVIEWS] (Worker0_QueryProcessorQueue4) o8FeDIzU1i/F TEIID30014 Loaded materialized view table #MAT_PG_CATALOG.MATPG_DATATYPE wi
> th row count 52.
> 13:08:38,311 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue14) L+yP/GL+aiOm Connector worker process failed for atomic-request=L+yP/GL+aiOm.0.6.3: org.teiid.tr
> anslator.TranslatorBatchException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("cus
> tid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya']
> SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
> 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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO
> "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prep
> ared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
> ... 20 more
> Caused by: java.sql.BatchUpdateException: Incorrect syntax near the keyword 'INTO'.
> at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
> at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
> ... 21 more
> 13:08:38,397 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) L+yP/GL+aiOm TEIID30020 Processing exception for request L+yP/GL+aiOm.0 'TEIID30504 sql_server:
> 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Pr
> epared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_ta
> b" ("custid", "custname") VALUES (?, ?)]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'INTO'.' JtdsStatement.java:1069. Enable more detailed
> logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5401?page=com.atlassian.jira.plugin... ]
Work on TEIID-5401 started by Steven Hawkins.
---------------------------------------------
> MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
> ----------------------------------------------------------------------------
>
> Key: TEIID-5401
> URL: https://issues.jboss.org/browse/TEIID-5401
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
>
> When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
> The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
> {code:xml|title=vdb}
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <vdb name="vdb-name" version="1">
> <model name="pm1" type="PHYSICAL">
> <source name="pm1" connection-jndi-name="java:/ds"
> translator-name="mysql5-override" />
> <metadata type="DDL">
> <![CDATA[
> CREATE FOREIGN TABLE g1(e1 integer, e2 string);
> ]]>
> </metadata>
> </model>
> <translator name="mysql5-override" type="mysql5">
> <property name="MaxInCriteriaSize" value="2" />
> <property name="MaxDependentInPredicates" value="2" />
> </translator>
> </vdb>
> {code}
> {code:sql|title=query}
> SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
> {code}
> pushed as 2 src commands:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
> {code}
> {code:java|title=preparedstatement}
> PreparedStatement ps = con
> .prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
> ps.setString(1, "a");
> ps.setString(2, "b");
> ps.setString(3, "c");
> ps.setString(4, "d");
> ps.setString(5, "e");
> ps.setString(6, "f");
> ps.setInt(7, 5);
> ResultSet rs2 = ps.executeQuery();
> {code}
> pushed as single SRC Command:
> {code:sql}
> SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
> {code}
> when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5390) Concurrent repeated add attempts create orphaned blocks
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5390?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5390.
-----------------------------------
Resolution: Done
Marking as resolved based upon user feedback. The add logic is now more defensive so any similar issue should be caught much sooner.
> Concurrent repeated add attempts create orphaned blocks
> -------------------------------------------------------
>
> Key: TEIID-5390
> URL: https://issues.jboss.org/browse/TEIID-5390
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 11.0, 10.3.2, 10.2.3
>
>
> If both a user thread and the buffer cleaner are evicting the same batch/page at the same time - and multiple attempts are made by one of the threads it will result in duplicate inode / data block entries being created, which then cannot be cleaned.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
by Jan Stastny (JIRA)
[ https://issues.jboss.org/browse/TEIID-5401?page=com.atlassian.jira.plugin... ]
Jan Stastny commented on TEIID-5401:
------------------------------------
It seems like TEIID-4901 is not applied in case of Prepared Statement.
I can reproduce the issue even without setting MaxDependentInPredicates property, just by issuing a long enough IN criteria. The query is never processed in parallel.
> MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
> ----------------------------------------------------------------------------
>
> Key: TEIID-5401
> URL: https://issues.jboss.org/browse/TEIID-5401
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
>
> When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
> The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
> {code:xml|title=vdb}
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <vdb name="vdb-name" version="1">
> <model name="pm1" type="PHYSICAL">
> <source name="pm1" connection-jndi-name="java:/ds"
> translator-name="mysql5-override" />
> <metadata type="DDL">
> <![CDATA[
> CREATE FOREIGN TABLE g1(e1 integer, e2 string);
> ]]>
> </metadata>
> </model>
> <translator name="mysql5-override" type="mysql5">
> <property name="MaxInCriteriaSize" value="2" />
> <property name="MaxDependentInPredicates" value="2" />
> </translator>
> </vdb>
> {code}
> {code:sql|title=query}
> SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
> {code}
> pushed as 2 src commands:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
> {code}
> {code:java|title=preparedstatement}
> PreparedStatement ps = con
> .prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
> ps.setString(1, "a");
> ps.setString(2, "b");
> ps.setString(3, "c");
> ps.setString(4, "d");
> ps.setString(5, "e");
> ps.setString(6, "f");
> ps.setInt(7, 5);
> ResultSet rs2 = ps.executeQuery();
> {code}
> pushed as single SRC Command:
> {code:sql}
> SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
> {code}
> when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
> {code:sql}
> SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
by Kulbhushan Chaskar (JIRA)
Kulbhushan Chaskar created TEIID-5402:
-----------------------------------------
Summary: UPSERT in batch mode.
Key: TEIID-5402
URL: https://issues.jboss.org/browse/TEIID-5402
Project: Teiid
Issue Type: Bug
Reporter: Kulbhushan Chaskar
Assignee: Steven Hawkins
Unable to execute UPSERT in batch. If batch size is 1, then it executes well. If batch size greater than 1 then getting logs.
I checked with MySQL database and SQLServer database.
*MySQL logs:*
py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `aakash_test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
ore detailed logging to see the entire stacktrace.
9:15:50,309 WARN [org.teiid.CONNECTOR] (Worker17_QueryProcessorQueue55) ws3B9oHdsaA9 Connector worker process failed for atomic-request=ws3B9oHdsaA9.0.6.11: org.teiid.
ranslator.TranslatorBatchException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`acco
nt` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`,
pri`) VALUES (?, ?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
aused by: org.teiid.translator.jdbc.JDBCExecutionException: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT
NTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCO
NT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
... 20 more
aused by: java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us
near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
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:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
... 21 more
aused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio
for the right syntax to use near 'UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1
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:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
... 25 more
9:15:50,338 WARN [org.teiid.PROCESSOR] (Worker16_QueryProcessorQueue56) ws3B9oHdsaA9 TEIID30020 Processing exception for request ws3B9oHdsaA9.0 'TEIID30504 MySqlConn_c
py: 1064 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: ['4441', 4241, '45'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE
, `pri`) VALUES (?, ?, ?), Prepared Values: ['4442', 4444, '94'] SQL: UPSERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES (?, ?, ?)]'. Origi
ally TeiidProcessingException 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UP
ERT INTO `test`.`account` (`ACCOUNT_NUMBER`, `BALANCE`, `pri`) VALUES ' at line 1' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable
ore detailed logging to see the entire stacktrace.
*SQLServer Logs:*
count 44.
13:05:13,738 INFO [org.teiid.PROCESSOR.MATVIEWS] (Worker0_QueryProcessorQueue4) o8FeDIzU1i/F TEIID30014 Loaded materialized view table #MAT_PG_CATALOG.MATPG_DATATYPE wi
th row count 52.
13:08:38,311 WARN [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue14) L+yP/GL+aiOm Connector worker process failed for atomic-request=L+yP/GL+aiOm.0.6.3: org.teiid.tr
anslator.TranslatorBatchException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("cus
tid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya']
SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:160)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:77)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:396)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:358)
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.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO
"DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prep
ared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:187)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:140)
... 20 more
Caused by: java.sql.BatchUpdateException: Incorrect syntax near the keyword 'INTO'.
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:1069)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1180)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeBatch(JDBCUpdateExecution.java:180)
... 21 more
13:08:38,397 WARN [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue15) L+yP/GL+aiOm TEIID30020 Processing exception for request L+yP/GL+aiOm.0 'TEIID30504 sql_server:
156 TEIID11012:TEIID11004 Error executing statement(s): [Prepared Values: [1, 'priyanka1'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Pr
epared Values: [4, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_tab" ("custid", "custname") VALUES (?, ?), Prepared Values: [5, 'priya'] SQL: UPSERT INTO "DQ"."dbo"."pri_ta
b" ("custid", "custname") VALUES (?, ?)]'. Originally TeiidProcessingException 'Incorrect syntax near the keyword 'INTO'.' JtdsStatement.java:1069. Enable more detailed
logging to see the entire stacktrace.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5401) MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
by Jan Stastny (JIRA)
Jan Stastny created TEIID-5401:
----------------------------------
Summary: MaxDependentInPredicates prevents IN criteria pushdown in Prepared Statement
Key: TEIID-5401
URL: https://issues.jboss.org/browse/TEIID-5401
Project: Teiid
Issue Type: Bug
Components: Query Engine
Affects Versions: 8.12.14.6_4
Reporter: Jan Stastny
Assignee: Steven Hawkins
When user sets translator override property MaxDependentInPredicates, then IN criteria is not pushed into underlying datasource, but only when using Prepared Statement.
The pushdown is not dependent on the limit vs IN criteria size. Simply, if the property is set, then the pushdown is not performed.
{code:xml|title=vdb}
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="vdb-name" version="1">
<model name="pm1" type="PHYSICAL">
<source name="pm1" connection-jndi-name="java:/ds"
translator-name="mysql5-override" />
<metadata type="DDL">
<![CDATA[
CREATE FOREIGN TABLE g1(e1 integer, e2 string);
]]>
</metadata>
</model>
<translator name="mysql5-override" type="mysql5">
<property name="MaxInCriteriaSize" value="2" />
<property name="MaxDependentInPredicates" value="2" />
</translator>
</vdb>
{code}
{code:sql|title=query}
SELECT e2 FROM g1 WHERE e2 IN ('a', 'b', 'c', 'd', 'e', 'f') AND e1<5 ORDER BY e2
{code}
pushed as 2 src commands:
{code:sql}
SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND (g_0.e2 IN ('a', 'b') OR g_0.e2 IN ('c', 'd'));
SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE g_0.e1 < 5 AND g_0.e2 IN ('e', 'f');
{code}
{code:java|title=preparedstatement}
PreparedStatement ps = con
.prepareStatement("SELECT e2 FROM g1 WHERE e2 IN (?, ?, ?, ?, ?, ?) AND e1<? ORDER BY e2");
ps.setString(1, "a");
ps.setString(2, "b");
ps.setString(3, "c");
ps.setString(4, "d");
ps.setString(5, "e");
ps.setString(6, "f");
ps.setInt(7, 5);
ResultSet rs2 = ps.executeQuery();
{code}
pushed as single SRC Command:
{code:sql}
SELECT g_0.e2 FROM g1 AS g_0 WHERE g_0.e1 < ?
{code}
when I remove the 'MaxDependentInPredicates' property, the ps gets pushed as:
{code:sql}
SELECT g_0.e2 AS c_0 FROM g1 AS g_0 WHERE (g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?) OR g_0.e2 IN (?, ?)) AND g_0.e1 < ? ORDER BY c_0
{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-4810) Translator Caching
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4810?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-4810.
-----------------------------------
Assignee: Steven Hawkins
Resolution: Done
Added cachePattern and cacheTtl as override properties to the delegating translator. This provides a very simple caching feature that can be applied to all translators. Any additional customization requires the use of custom translator or materialization can be used.
> Translator Caching
> ------------------
>
> Key: TEIID-4810
> URL: https://issues.jboss.org/browse/TEIID-4810
> Project: Teiid
> Issue Type: Enhancement
> Components: Misc. Connectors, Query Engine
> Affects Versions: 9.3
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 11.0
>
>
> we also have a whole different level of caching for custom translators
> we could expand on that
> we could cache on any source query
> that’s not something we’ve talked about putting any higher level UI around
> this would be a development and documentation effort
> this is different than the whole model materialisation
> the difference is that this is at a source query level
> vs when you are using cache hints and extension metadata
> we don’t have anything for the design-time user
> that’s another feature set around caching that we could put some exposure on it
> otherwise it’s not really exposed
> the idea there is that if you want to do some higher level caching
> you can build that into your models or your user query
> we could turn it on and off without having to use a custom translator
> MMX had a feature like that but it was convoluted because it was mixed up with the user level query caching and they had to be enabled together
> Attachments
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5396) Querying SQL Server variant type for a string fails
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-5396?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-5396:
---------------------------------
Fix Version/s: 8.12.14.6_4
> Querying SQL Server variant type for a string fails
> ---------------------------------------------------
>
> Key: TEIID-5396
> URL: https://issues.jboss.org/browse/TEIID-5396
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.14.6_4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Fix For: 11.0, 10.3.2, 10.2.3, 8.12.14.6_4
>
>
> Table on SQL Server source created as
> {code:sql}CREATE TABLE table_with_variant(id INT, var SQL_VARIANT){code}
> and loaded with int, float and string data.
> Running the following query against a dynamic VDB with metadata import
> {code:sql}SELECT cast(var AS string) FROM table_with_variant WHERE id = 1{code}
> fails with
> {noformat}java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.{noformat}
> This query used to work with DV 6.4.2, it appears it is a further regression from TEIID-5313 . Querying rows with non-string data works as expected.
> SOURCE SRC command with 6.4.2 (working):
> {code:sql}SELECT cast(g_0."var" AS varchar(4000)) FROM "dballo05"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
> {code:sql}SELECT g_0."var" FROM "dballo06"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months
[JBoss JIRA] (TEIID-5400) Further normalize source queries
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5400:
-------------------------------------
Summary: Further normalize source queries
Key: TEIID-5400
URL: https://issues.jboss.org/browse/TEIID-5400
Project: Teiid
Issue Type: Sub-task
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 11.1
To make better use of cache entries source queries should be more normalized, such as alpha sorting the projected columns.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
5 years, 10 months