teiid unable to call database function when VDB imports more than one VDB.
kulbhushanc Jul 21, 2017 7:36 AMHi,
I am testing scenario with teiid 9.1.3 and WildFly 10.0.0
During the execution of database specific functions (a functions which is define by the user on a database) with Teiid, I come across confusing scenario, below are the steps to reproduce the scenario.
Scenario:
1. I deployed two mysql VDBs on teiid server namely SvcSourceVdb_mysqlConn_1, SvcSourceVdb_mysqlConn_2
2. SvcSourceVdb_mysqlConn_1 VDB pointing to the database MysqlDB_1 and SvcSourceVdb_mysqlConn_2 VDB is pointing to the MysqlDB_2
where MysqlDB_1 contains a table test having one column name, MysqlDB_2 database contains a function myFunction which returns static value 1000
3. I deployed one more VDB SvcSourceVdb_mysqlConn_3 by importing VDBs SvcSourceVdb_mysqlConn_1 and SvcSourceVdb_mysqlConn_2
4. when I am trying to execute insert query INSERT INTO mysqlConn_1.MysqlDB_1.TEST VALUES (myFunction(?, ?)); in batch i.e. with the use of PreparedStatement of java it got succeed and I can see 1000 in my table test.
5. But if I add more than one statement in batch teiid try to evaluate the function against the mysqlConn_1 and failed to execute the batch. The exception stack trace is pasted below.
If I prepend myFunction with mysqlConn_2.myFunction(.....) then I got QueryResolver exception
Stack trace:
16:58:25,147 WARN [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue41) KgiblAxE1YAx Connector worker process failed for atomic-request=KgiblAxE1YAx.0.0.2: org.teiid.translator.jdbc.JDBCExecutionException: 1305 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?] SQL: INSERT INTO `mysqlConn_1`.`test` (`name`) VALUES (myFunction(?, ?))]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:330)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:86)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:405)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:367)
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:497)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)
at com.sun.proxy.$Proxy27.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.BatchUpdateException: FUNCTION mysqlConn_1.myFunction does not exist
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:422)
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:1190)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:289)
... 20 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: FUNCTION mysqlConn_1.myFunction does not exist // here it should evaluate function against mysqlConn_2
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:422)
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)
... 24 more
So it's working for batch size 1 and failed if the batch size is more than one.
Thanks,
Kulbhushan Chaskar.