Unable to execute batch upsert in prepared statement

[kulbhushanc]

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

[shawkins]

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.

[kulbhushanc]

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

[rareddy]

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?

[kulbhushanc]

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

[shawkins]

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.

[kulbhushanc]

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