2 Replies Latest reply on Jul 26, 2017 6:05 PM by shawkins

    teiid unable to call database function when VDB imports more than one VDB.

    kulbhushanc

      Hi,

       

      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.