Prepared Statement needs to be reprepared problem

[dangari]

My java application has been running smoothly for the last 2 years in one linux server.
Recently, we moved it to another linux server and it's then that it started giving the error as shown below:

java.sql.SQLException: Prepared statement needs to be re-prepared
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
    at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1160)

What is the likely cause of this and what are some of the measures that I can take to eradicate this.
Thank you kindly as you offer your assistance.

[masijade]

A long idle time between uses of the statement maybe? If that's case check out the session/connection timeout settings and the like for your db. Otherwise, simply catch the exception, check for this message (or, actually, the error code) and recreate the PreparedStatement.

[dangari]

A long idle time between uses of the statement maybe? If that's case check out the session/connection timeout settings and the like for your db. Otherwise, simply catch the exception, check for this message (or, actually, the error code) and recreate the PreparedStatement.

Thanks masijade.
Going the database way, I checked for timeout variables and this is how things stand for the machine:

mysql> SHOW VARIABLES LIKE '%TIMEOUT';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout        | 28800 |
| net_read_timeout           | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 28800 |
+----------------------------+-------+
10 rows in set (0.00 sec)

As I have limited knowledge on database administration, could you tell me what exactly can be tweaked among the 10?This is in a bid to avoid interrupting the service running by making a change in code.
Thanks again.

[~s.o.s~]

We need more details; what else was changed during the move? Which MySQL version are you using? Which client driver and what version? How frequently are you getting this error message?

A link you might find helpful: http://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared

[dangari]

We need more details; what else was changed during the move? Which MySQL version are you using? Which client driver and what version? How frequently are you getting this error message?

A link you might find helpful: http://stackoverflow.com/questions/4380813/how-to-get-rid-of-mysql-error-prepared-statement-needs-to-be-re-prepared

Thanks for the link s.o.s, really insightful!
Here are the details I have so far:
MySQL version: 5.1.55
MySQL client driver: connector/J 5.0.3
Frequency of error message: Every day at 10:02 PM local time.

Having looked at the URL above, I have a hunch that the database server version should be the same as the client driver version for optimal operation.Could this be the cause?

[jwenting]

ok, so the error happens every day at the same time.
That hints at something outside the application, most likely either in the database or the connection pool.

[~s.o.s~]

Having looked at the URL above, I have a hunch that the database server version should be the same as the client driver version for optimal operation.Could this be the cause?

You can try updating to a new version but I doubt that's the reason why this is happening. If it were, why wasn't it happening previously? Do you run any database heavy jobs around that time or is it that the hits to your database are maximum at that time? I've read about prepared statement failing due to dumps going on at the database level for MySQL.

A temporary solution (if you are in need for one) might be to put a try...catch around the prepare call and try preparing the statement again if it fails for the first time. Other than that, your best bet would be to try to understand *what* special processing happens at around that time or recollect what else was changed during the move.

[dangari]

s.o.s and jwenting, you are both absolutely right.
I went to the linux administrator and discovered that he's scheduled a mysql-dump job for the said time!He was supposed to have done an incremental backup but didn't.This would fix this for sure coz yesterday i did increase mysql's table_open_cache and table_definition_cache from 256 to 1024, and instead of having five java cron jobs giving the error, only 2 did.
Thanks guys.
I'll mark it as solved now.