Can't get select mapping to flush local cache or not use it.

[GoogleCodeExporter]

My Batis 3.0.2

Basically I'm using the following select to retrieve the next value from a 
hsqldb sequence: 

<select  id="getNextId" flushCache="true" useCache="false" resultType="long">
        call NEXT VALUE FOR PBSEQ;
    </select>

Repeated calls return the same value. After using a debugger I noticed that the 
first call is retrieved from the database but the value is cached and returned 
in subsequent calls to getNextId. 

You can see the problem if you put a debug marker on the query method of the 
BaseExecutor class. I understand that normally a select should be cacheable, 
but not when selecting from a sequence. I assumed the flushCache and useCache 
settings were meant for such situations, but they seem to have no effect on the 
local cache.

The workaround I have right now is to call SqlSession.clearCache() before
calling the getNextId method. 

Original issue reported on code.google.com by s...@taylorit.com on 30 Sep 2010 at 6:12

[GoogleCodeExporter]

we have the same problem
when we select Sequence 2 times ,cache will return the same value

if we call SqlSession.clearCache() ,it works .
but in the same session, other cached data be cleared
we hopes the first Level Cache can be config eg. useCache flushCache 

Original comment by garth...@gmail.com on 18 Feb 2011 at 10:20

[GoogleCodeExporter]

>> we hopes the first Level Cache can be config

It would be great if we could have the option (in 3.0.5) to disable the local 
cache completely. See also related issues / comments that suggest similar - eg. 
#58,  #139, ... . Many thanks.

Original comment by montgolf...@gmail.com on 18 Mar 2011 at 4:12

[GoogleCodeExporter]

If there is a caching executor, I don't quite see why the basic executor needs 
a cache at all, in particular if the caching executor does not clear the 
delegate's cache when its own cache is cleared. This causes much of the 
undesired behaviour (which is also not in line with what the documentation 
claims).
If you configure "don't use cache" for a query, the caching executor will not 
do so, but the delegate does (at the moment).
two ideas for fixes: remove local cache from basic executor, clear delegates 
cache on all calls from the caching executor.

Original comment by nit...@itemis.de on 30 Mar 2011 at 6:31

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hi,

i must toally agree. I have this effect when selecting a list of objects and 
then manipulating this list. 

The second select call does return the modified list and this is definitvely 
not what it should do.

Please provide a way to completely disable caching.

Original comment by Juergen....@gmail.com on 5 Apr 2011 at 5:51

[GoogleCodeExporter]

I Reproduced the same behavior with version 3.0.4...

Original comment by bas.stok...@gmail.com on 12 Jul 2011 at 9:38

[GoogleCodeExporter]

I have the same annoying problem with version 3.0.5

Another workaround I found is to use a dummy ResultHandler. 

---
      list = resultHandler == null ? (List) localCache.getObject(key) : null;
      if (list != null) {
        handleLocallyCachedOutputParameters(ms, key, parameter);
      } else {
        list = queryFromDatabase(ms, parameter, rowBounds, resultHandler, key);
      }
---

Original comment by fglez....@gmail.com on 26 Jul 2011 at 6:02

[GoogleCodeExporter]

Should be fixed in r3884.

Original comment by eduardo.macarron on 5 Sep 2011 at 10:22

• Added labels: Component-SqlMaps, Target-Release3.0.6

[GoogleCodeExporter]

Original comment by eduardo.macarron on 6 Sep 2011 at 2:21

• Changed state: Fixed

[GoogleCodeExporter]

Please reopen the issue. Its still with version 3.0.6.

Original comment by jayarami...@gmail.com on 25 Oct 2011 at 2:36

[GoogleCodeExporter]

In 3.0.6 a cache flush will also flush local session cache. 

A test was added:
/trunk/src/test/java/org/apache/ibatis/submitted/force_flush_on_select/ForceFlus
hOnSelectTest.java  

Please, have a look at the test and provide an update to proof the fail. 

Original comment by eduardo.macarron on 25 Oct 2011 at 4:17

[GoogleCodeExporter]

Hello Thanks for your quick response,

I went through the codes that already in the SVN for the fixes its mentioned. 

I tried the same from my end to my application but faces the same issue.

PFB, for the mapper.xml contents I am using:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.sears.someproject.ItemHeaderMapper">

    <select id="getItemHeader" resultType="ItemHeader" parameterType="ItemHeader" useCache="false" flushCache="true">
        SELECT item_No,Item_desc,Item_Weight
        FROM Item
        WHERE item_No = #{itemNo}
    </select>
</mapper>

If I saw an difference is you are making call for update statement, that in 
terms flushing the query cache. here goes your code snippet:

Person person = people.get(0);
            person.setFirstName("Simone");

            personMapper.update(person);

            people = personMapper.selectAll();

            assertEquals("Simone", people.get(0).getFirstName());

            sqlSession.commit();
        } finally {
                sqlSession.close();
        }

Where as What it suppose to be, it should get flushed for all the select query 
with out any insert or update, by use of either flushCache="true" or 
useCache="false"

But its not the case, I am finding, 

What I found from your mapper xml is 
<select id="update" parameterType="Person" resultType="Person" 
flushCache="true">
        UPDATE person
        SET firstName = #{firstName},
        lastName = #{lastName}
        WHERE id = #{id}
    </select>
you are using update statement here.

If I am wrong please correct me.

My requirement is to disable the flush each time a select query is fired or 
prepared for data base hit. 

Please let me know your feed back on this, or please guide me how to disable, 
for the select query.

Thanks,
Jayaram. 

Original comment by jayarami...@gmail.com on 25 Oct 2011 at 5:34

[GoogleCodeExporter]

Hi Jayaram. Maybe the test is a bit confusing because it uses an update inside 
a mapped select.

I have updated it in r3989. Have a look at it again.

Original comment by eduardo.macarron on 25 Oct 2011 at 7:55

[GoogleCodeExporter]

Hello Eduardo,

Thank you for quick change, still have an issue,

I ma getting confused on the latest code you have that is:

 PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
personMapper.selectByIdFlush(1);
updateDatabase(sqlSession.getConnection());
Person updatedPerson = personMapper.selectByIdFlush(1);
assertEquals("Simone", updatedPerson.getFirstName());

1st of all executing one select ok then what you are doing with that 
updatedateBase, please clear me, 

then you are querying for one more time with the same query, thats fine.

When I am trying to run your piece of code with out that updatedateBase, and 
trying to figure out from the log message,

for the first hit log message are printing like connection opened. your query, 
then your setted value in the where clause, then result is coming as column etc.

for the second time execution of the same query its not at all trying to enter 
in to data base to hit. 

As per my understanding, data base supports query cache, So in order to achieve 
the flushcache, do i need to change in the confuguration of mysql ?

Also , Please let me help, If I will turn on EHCACHE, then is this first level 
cache will still their, I mean it will over ride or not.

I spent complete day for POC of this as you guys are confident enough that this 
issue has been fixed so Am trying to make it work by any chance ..but still not 
able to , stuck on the same as previous.

Here is my application details:

Spring3.0 + Mybatis3.0.6+JBoss 5.0

I am using JNDI data source look up from the application server, and also 
trying to make the insatnce of mapper in the spring way and trying to make call 
of the query just like

Item item = itemHeaderMapper.getItem(Item); 

But seems to be help less,

internally, what did till now is , for a singel user for the first time hit to 
the application its hitting all the query and even adding some query to EHCACHE 
that I choose, but the query that are not enabled by @Cachable annotation , 
they should have to make a fresh hit to the data base , where as its not 
happening , instead of hitting its taking from the query cache.

Please do let me know if any extra modifications or any changes requires to 
make it work of flushedCache.

But I am dam sure, the flushedCache issue is still on.

Please correct me in case I went wrong some where.

Thanks,
Jayaram.

Original comment by jayarami...@gmail.com on 25 Oct 2011 at 9:53

[GoogleCodeExporter]

Hi Jayaram. updateDatabase changes a record in the database but as MyBatis uses 
a local session in SqlSession it should not see it.

This is the normal behaviour:
At the beginning database contains "John", then we update the database (John 
becames Simone) out of MyBatis, then we perform the select again and it should 
still contain "John" because this second time that value was got from local 
SqlSession cache.

But when you se the flushCache="true" then mybatis flushes its session cache 
and the second select gets the real value of the database.

Original comment by eduardo.macarron on 26 Oct 2011 at 5:41

[GoogleCodeExporter]

Hello Eduardo,

Thanks a lot for the clarification.

It was my mistake of implementation, sorry for the inconvenience caused by me.  

have small query to clarify, is this flushCache has nay impact on EHCACHE, or 
its only for local session cache flush?

Thank you very much for all your response.

Thanks,
jayaram.

Original comment by jayarami...@gmail.com on 27 Oct 2011 at 2:18

[GoogleCodeExporter]

it flushes both.

good to know your problem got solved!

Original comment by eduardo.macarron on 27 Oct 2011 at 7:50

[GoogleCodeExporter]

OMG, then it will be a huge bad impact on my project.

I currently tried with flushCache="true" on the select statements, as I don't 
want to make it cachable and corrupting my EHCACHE object.

Please give me suggestion on this below:

My requirement is to do a lot query a data base and store those in EHCACHE key 
generator, I disabled the the query cache as it was modifying the same 
singleton object that created by the spring mapperFactory. as the serialized 
object once modified will not get saved by EHCACHE so.

If currently this flushCache will flush my EHCACHE then its not have any use of 
using EHCACHE, is there any way of disabling the query cache and storing it 
EHCACHE?

I am using in the below way :

<bean id="blogMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    <property name="mapperInterface" value="org.mybatis.example.BlogMapper" />
</bean>

and in the mapper.xml

<mapper namespace="org.mybatis.example.BlogMapper" flushCache="true">
    <select id="selectBlog" parameterType="int" resultType="Blog">
        select * from Blog where id = #{id}
    </select>
</mapper>

and in the interface I am using @Cachable annotaion to make use of EHCACHE

Please let me know if flushCache will have any impact to EHCACHE on this, If so 
Please let me know any other alternative of this.

Your help is appreciated.

Thanks,
Jayaram.

Original comment by jayarami...@gmail.com on 27 Oct 2011 at 8:09

[GoogleCodeExporter]

    @Cacheable(cacheName="weatherCache")
    public BlogselectBlog(int zipCode) {
        //Some Code
    }

Original comment by jayarami...@gmail.com on 27 Oct 2011 at 8:11

[GoogleCodeExporter]

Hello Eduardo,

This will be my impact that I got from EHCACHE documentation:

Can non-Serializable objects be stored in a cache?

As of ehcache-1.2, they can be stored in caches with MemoryStores. If an 
attempt is made to replicate or overflow a non-serializable element to disk, 
the element is removed and a warning logged.

as of query cache, the object will not be serailzable so its may have impact

Please have a try and let me know any alternative on this.

Original comment by jayarami...@gmail.com on 27 Oct 2011 at 8:22

[GoogleCodeExporter]

If you use @Cacheable on any bean you are using Ehcache to cache all calls to 
that bean. That cache is OVER MyBatis. MyBatis will not know it exists. It will 
not store any Object on it and will not flush it. So flushCache="true" will 
only flush local session cache.

You can use Ehcache as an internal cache for MyBatis. In this case MyBatis will 
cache result Objects internally. This cache IS flushed by flushCache="true".

When using Spring you can choose whether to use a cache at Spring level or 
inside MyBatis. Both them should work OK. In fact I suppose you will find more 
documentation on using Ehcache at Spring level than at MyBatis level.

Original comment by eduardo.macarron on 27 Oct 2011 at 8:35

[GoogleCodeExporter]

Thanks a lot Eduardo.

Original comment by jayarami...@gmail.com on 28 Oct 2011 at 7:27

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Hello,
On my MyBatis mapper file I put a <cache /> element to enable it. I assume that 
configuration is enabling cache at MyBatis level.
To that mapper file I also added a delete method with flushCache="true" and two 
select statements, one with useCache="false" and I let the other using the 
cache.
My test is the following: I perform an insert, a select and a delete on the 
same object.
Next I test the select that uses the cache and I still get the instance while 
the select that does not return anything.
I would expect that both return nothing.

I am using MyBatis 3.0.6.

Original comment by olivier....@gmail.com on 29 Mar 2012 at 8:39

[GoogleCodeExporter]

Even mybatis3.1.1 has the bug.
Select query is getting cached. There is no way to disable cache of select 
query. Below is a test case,

1. open a mybatis session.
2. execute a select query.
3. Leave the connection open in some static variable.
4. delete all the records from table on which above select query is executed.
5. execute the same select query on above open mybatis session. It will show 
the same result, whereas actually all data from the table is deleted.

useCache in select tag does not work in this case also in code I've tried to 
clear cache using SqlSession.clearCache(). But even then I get same old value.

Please suggest what to do?

Original comment by shan2005...@gmail.com on 4 Jun 2012 at 8:00

[GoogleCodeExporter]

Hello,

to work arround this problem we introduced this mybatis plugin

package org.apache.ibatis.issues.fix126;

import java.util.Properties;

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
/**
 * Plugin to Fix the Mybatis ISSUE 126
 * BaseExecutor has allways a local cache wich can not be switched off
 *
 */
@Intercepts({@Signature(type= Executor.class, method = "query",args = 
{MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class Fix implements Interceptor{

    public Object intercept(Invocation invocation) throws Throwable {

        try {
            Executor executor = (Executor) invocation.getTarget();
            executor.clearLocalCache();
        } catch (Throwable t) {
            t.printStackTrace();
        }

        // let the invocation do its way
        return invocation.proceed();
    }

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {

    }

}

which is just included in the sqlmapconfig
<plugins>
    <plugin interceptor="org.apache.ibatis.issues.fix126.Fix"/>
</plugins>

This works silently for over a year.

Original comment by Juergen....@gmail.com on 5 Jun 2012 at 5:03

[GoogleCodeExporter]

Why not provide a option to disable cache entirely?

Original comment by Ruinan....@gmail.com on 2 Dec 2012 at 6:01