6 Replies Latest reply on Jun 28, 2018 6:09 AM by Kulbhushan Chaskar

    Unable to execute batch upsert in prepared statement

    Kulbhushan Chaskar Master

      Hi,

       

      I am getting exception when performing upsert using prepared statement batch.

      teiid version: 9.1.3

      jboss version: Wildfly 10.0.0

      teiid AdminShell: 9.1.3

       

      Scenario:

      I am trying to perform upsert in batch. If batch size is 1, it executes well.

      If its is greater than 1, then it throw below exception.

       

      Stack Trace:

      WARNING: Unable to read: unexpected exception

      java.io.OptionalDataException

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:286)

      at java.io.ObjectInputStream.readExternalData(Unknown Source)

      at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.net.socket.Message.readExternal(Message.java:56)

      at java.io.ObjectInputStream.readExternalData(Unknown Source)

      at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:108)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)

      at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      at java.lang.reflect.Method.invoke(Unknown Source)

      at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

      at com.sun.proxy.$Proxy1.read(Unknown Source)

      at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)

      at org.teiid.jdbc.PreparedStatementImpl.executeBatch(PreparedStatementImpl.java:251)

      at com.mySql.WithTeiid.executeSelect(WithTeiid.java:199)

      at com.mySql.WithTeiid.main(WithTeiid.java:83)

       

       

      org.teiid.jdbc.TeiidSQLException: org.teiid.net.socket.SingleInstanceCommunicationException

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:559)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:404)

      at org.teiid.jdbc.PreparedStatementImpl.executeBatch(PreparedStatementImpl.java:251)

      at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:199)

      at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:83)

      Caused by: org.teiid.net.socket.SingleInstanceCommunicationException

      at org.teiid.net.socket.SocketServerInstanceImpl.exceptionOccurred(SocketServerInstanceImpl.java:226)

      at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:310)

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

      at java.lang.reflect.Method.invoke(Unknown Source)

      at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

      at com.sun.proxy.$Proxy1.read(Unknown Source)

      at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:405)

      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:551)

      ... 4 more

      Caused by: java.io.OptionalDataException

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.client.ResultsMessage.readExternal(ResultsMessage.java:286)

      at java.io.ObjectInputStream.readExternalData(Unknown Source)

      at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.net.socket.Message.readExternal(Message.java:56)

      at java.io.ObjectInputStream.readExternalData(Unknown Source)

      at java.io.ObjectInputStream.readOrdinaryObject(Unknown Source)

      at java.io.ObjectInputStream.readObject0(Unknown Source)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.netty.handler.codec.serialization.ObjectDecoderInputStream.readObjectOverride(ObjectDecoderInputStream.java:108)

      at java.io.ObjectInputStream.readObject(Unknown Source)

      at org.teiid.net.socket.OioOjbectChannelFactory$OioObjectChannel.read(OioOjbectChannelFactory.java:117)

      at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:306)

      ... 12 more

       

       

      Thanks,

      Kulbhushan Chaskar

        • 1. Re: Unable to execute batch upsert in prepared statement
          Steven Hawkins Master

          That does not look like a known issue that I can recall.  That is a very low level IO exception so there isn't likely to be a workaround.  You should try to reproduce it against a later version (10.3.1 preferably) and open an issue if it still occurs.

          • 2. Re: Unable to execute batch upsert in prepared statement
            Kulbhushan Chaskar Master

            Hi Steven,

             

            I checked with new version 10.3 and 10.2.1, and still getting the same issue. I added upsert query(which I used) and teiid exception into this.

            Could you please let me know that there is some syntax error or I can proceed to open issue for the same.

             

            Upsert Query:

            upsert into <connection_name>.test.account(account_number,balance,pri) values(?,?,?);

             

            where test is database name of MySQL and account is table name.

            MySQL driver jar: mysql-connector-java-5.1.37-bin.jar

             

             

            Teiid log:

            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.

             

             

            The query above mention executes when size of batch is one.

             

             

            Thanks,

            Kulbhushan Chaskar

            • 3. Re: Unable to execute batch upsert in prepared statement
              Ramesh Reddy Master

              I did not find "UPSERT" support as above directly in MySQL documents,  they have like "INSERT ... ON DUPLICATE KEY UPDATE" may be MySQL translator needs to be updated with this? Or wrong translator name being used in the above VDB on the model?

              • 4. Re: Unable to execute batch upsert in prepared statement
                Kulbhushan Chaskar Master

                Hi Ramesh,

                 

                Below is the link from where I got the "UPSERT"  command for teiid.

                teiid-documents/DML_Commands.adoc at master · teiid/teiid-documents · GitHub

                 

                This issue is not only with MySQL, it is also reproducible for SqlServer as well. And as I mentioned above the same is executable when the batch size is 1, if batch size is greater than 1 then I am getting these logs/exceptions.

                 

                Is it "UPSERT" command in teiid do not have support to execute in batch ?

                 

                 

                Thanks,

                Kulbhushan Chaskar

                • 5. Re: Unable to execute batch upsert in prepared statement
                  Steven Hawkins Master

                  To clarify what Ramesh is saying - mysql currently isn't marked as having upsert support, so it's surprising to see an upsert statement in the source sql.  Did you use a mysql translator?

                   

                  > Is it "UPSERT" command in teiid do not have support to execute in batch ?

                   

                  Yes it should.  If you are using a standard translator, please capture an issue on this showing/describing the user JDBC as well.

                  • 6. Re: Unable to execute batch upsert in prepared statement
                    Kulbhushan Chaskar Master

                    Hi Steven,

                     

                    > Did you use a mysql translator?

                     

                    Yes, I used MySQL translator. As teiid provide 2 MySQL translators - MySQL and MySQL5. I tried with both and still facing same issue.

                     

                    I locked this issue in JIRA,

                    https://issues.jboss.org/browse/TEIID-5402

                     

                    Also, I tested this for SQLServer. Same issue I faced(Executes well for batch size 1 but not for batch size greater than zero).

                    SQL Server 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.

                     

                     

                    Thanks,

                    Kulbhushan Chaskar