Prepared Statement needs to be reprepared problem

Question

dangari 0 Junior Poster in Training

13 Years Ago

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.

java

4 Contributors
7 Replies
3K Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by dangari

~s.o.s~ 2,560 Failure as a human

13 Years Ago

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

jwenting 1,889 duckman

13 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

masijade 1,351 Industrious Poster Team Colleague Featured Poster · Answer 1 · 2011-07-12T04:09:30+00:00

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 0 Junior Poster in Training · Answer 2 · 2011-07-12T14:26:25+00:00

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.

dangari 0 Junior Poster in Training · Answer 3 · 2011-07-12T18:00:42+00:00

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?

~s.o.s~ 2,560 Failure as a human Team Colleague Featured Poster · Answer 4 · 2011-07-13T12:27:28+00:00

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 0 Junior Poster in Training · Answer 5 · 2011-07-13T15:10:13+00:00

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.