MyBatis is throwing "ERROR: relation "dual" does not exist" when working with postgres

[Isaac Hazan]

I am out of ideas about this one.

I am using MyBatis to connect for  simple select in postgres.

Here is my setup:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.2-1004-jdbc41</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.2.3</version>
</dependency>


I am having a mapper with the following query:

<select id="getLicenseUsage"  parameterType="long"  resultMap="licenseUsage">
    select ('1970-01-01 00:00:00 GMT'::timestamp + (event_time/1000)::text::interval)::date as day, license_key, count(distinct event_id) as recos, max(id) as id
    from venm_raw
    where target_id is not null and id > #{fromId}
    group by license_key,day;
</select>


Whenever the query is executed i get the following errors:
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ERROR: relation "dual" does not exist)
### The error may exist in file [/opt/tomcat/webapps/ROOT/WEB-INF/classes/META-INF/mappers/redshift/LicenseUsageMapper.xml]
### The error may involve com.qualcomm.vuforia.redshift.mappers.LicenseUsageMapper.getLicenseUsage
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ERROR: relation "dual" does not exist)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
        at com.sun.proxy.$Proxy36.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198)
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:114)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
        at com.sun.proxy.$Proxy49.getLicenseUsage(Unknown Source)
        at com.qualcomm.vuforia.sumtables.summarizers.SummarizerProcessor.process(SummarizerProcessor.java:60)
        at org.apache.camel.processor.DelegateSyncProcessor.process(DelegateSyncProcessor.java:63)
        at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:72)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:416)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:80)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)
        at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:105)
        at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:87)
        at org.apache.camel.processor.aggregate.AggregateProcessor$1.run(AggregateProcessor.java:548)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at org.apache.camel.util.concurrent.SynchronousExecutorService.execute(SynchronousExecutorService.java:62)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:110)
        at org.apache.camel.processor.aggregate.AggregateProcessor.onSubmitCompletion(AggregateProcessor.java:540)
        at org.apache.camel.processor.aggregate.AggregateProcessor.access$900(AggregateProcessor.java:82)
        at org.apache.camel.processor.aggregate.AggregateProcessor$AggregationTimeoutMap.onEviction(AggregateProcessor.java:853)
        at org.apache.camel.processor.aggregate.AggregateProcessor$AggregationTimeoutMap.onEviction(AggregateProcessor.java:814)
        at org.apache.camel.support.DefaultTimeoutMap.purge(DefaultTimeoutMap.java:212)
        at org.apache.camel.processor.aggregate.AggregateProcessor$AggregationTimeoutMap.purge(AggregateProcessor.java:826)
        at org.apache.camel.support.DefaultTimeoutMap.run(DefaultTimeoutMap.java:162)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)

While looking in the different similar errors, it seems that MyBatis is doing behind the scenes a query that would expect the existence of table named "dual" like in oracle. This is just guess but this is what it looks like from the different readings over the net.

Is this a bug?

I tried the following with no success:
 - Tried different versions of MyBatis: 3.2.3 and 3.2.8
 - Simplified the query to be very basic.

Help is much appreciated, thx in advance.

[Jose María Zaragoza]

Hi.


Probably your connection pool has defined some kind of pingQuery ( or
validationQuery ) to test the connection before being borrowed.
Have a look to your connection pool settings

Regards



>
> --
> You received this message because you are subscribed to the Google Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mybatis-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.