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.