Hibernate or browser caching DB results?

[Mike London]

I have a situation where I check to see if the user's login is still valid. This check is a simple database check of a date field.

What I'm noticing is that if I change the value in the database, the hibernate code doesn't always "see" the new value and says that the user's account has expired when, in fact, I just updated it and saved the results (separately, in MYSQL).

Once I re-start Tomcat, all is OK again.

I've tried clearing the browser cache and using session.flush() on the hibernate logic, but neither of these things seems to fix the fact that the hibernate code is not getting the current DB value.

Is there another way to make sure I'm getting the most current database data?

Thanks for any suggestions.

Mike

[Paul Sturrock]

There are a few places this could happen. your session handling could be suspect - can you show use the code you use to get the latest data from the db? Usually, opeing a new session and calling a get method will read the database. If you are reusing a session you may get cached data. Are you using a second level cache or query cache?

What I'm noticing is that if I change the value in the database, the hibernate code doesn't always "see" the new value and says that the user's account has expired when, in fact, I just updated it and saved the results (separately, in MYSQL).

I don't know MySQL very well, but does your SQL client auto-commit? I.e. have you really updated the data or does it just appear you have?

Once I re-start Tomcat, all is OK again.

This is authentication code, right? Tomcat (like most containers) has an authentication cache (because reauthenticating for every call is drag on performance). Depending on what you are doing and how you are doing it you may be hitting this.

[Mike London]

Hi Paul,

Here is the basic code I'm using to read from the database:

 
    UsersDAO dao = new UsersDAO();
    Transaction transaction = null;
 
    Session session = dao.getSession();
    session.flush();  // tried removing this line
      
    try
    {
      transaction = session.beginTransaction();
      List userList = dao.findByUsrLogin(userLogin);
.
.     // sometimes userList values seem to be cached
.
 
    catch (HibernateException e)
    {
      transaction.rollback();
    }
    finally
    {
      try
      {
         dao.getSession().close();
       }
       catch (Exception e)
       {

[ Edited to use code tags - Paul Sturrock ]
[ June 17, 2008: Message edited by: Paul Sturrock ]

[Paul Sturrock]

dao.getSession()

How does this get a session? Is is an existing session or a new one?

transaction = session.beginTransaction();

Why are you performing a select query withing a transaction? Is there more going on than a simple get?

 List userList = dao.findByUsrLogin(userLogin);

What does findByUsrLogin do? Does it run a query? Do you use a query cache?

catch (HibernateException e)
    {
      transaction.rollback();
    }

Are any exceptions being thrown? Do you log the exception message, or ignore it (as above)?

[Mike London]

Hi Paul,

A couple of responses to your questions.

1. The code that I'm using references boilerplate code that MyEclipse creates for you automatically. I didn't hand-craft the underlying session code or anything like that. And,....

2. This code works perfectly locally.

Both local and remote (server) the code is using Tomcat 5.5, MySQL 5.0.x (though different actual release numbers). Remote uses Apache and Linux where development is Windows and just Tomcat for both Web server and servlet container. Both local and remote use the same JDBC driver version (though I also tried the 3.x and 5.0 versions on the server to see if that would help. It didn't).

I also tried adding autoReconnect="true" as shown below (from hibernate config file), but that didn't seem to help either...

<property name="connection.url">
jdbc:mysql://localhost:3306/TestProject?autoReconnect="true"
</property>

-------------

I'm not sure how to actually track down this problem.

There are LOTS of postings about this error, but no fixes that I've seen.

Thanks again for any thing you can suggest.

Mike

[Mike London]

Added these lines too to hibernate config (autoreconnect=true added the way below instead of the way mentioned in the posting directly above):

<property name="connection.autoReconnect">true</property>
<property name="connection.autoReconnectForPools">true</property>
<property name="connection.is-connection-validation-required">true</property>

Don't know if this will help, but I'll let you know.

Thanks

Mike

[Madan Prabhu Durai]

By using session.evict and sessionFactory.evict methods. in addition to that call twice the retrieve code for your data.