[teiid-issues] [JBoss JIRA] (TEIID-5402) UPSERT in batch mode.
Steven Hawkins (JIRA)
issues at jboss.org
Thu Jun 28 11:41:01 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13598474#comment-13598474 ]
Steven Hawkins commented on TEIID-5402:
---------------------------------------
The issue manifests itself when a translator does not have support for upsert, but does have support for bulk update. The plan produced will still push an upsert which is not expected.
> 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)
More information about the teiid-issues
mailing list