How to set autoReconnect property for mysql db url in Play Framework configuration file?

[Shishir Prasad]

Hi,

I am trying to set autoReconnect=true mysql connection property in application.conf file of my Play Framework 2.0 application. But it is giving me the following error :

Caused by: java.sql.SQLException: The connection property 'autoReconnect' only accepts values of the form: 'true', 'false', 'yes' or 'no'. The value 'true?useUnicode=yes' is not in this set.

This is my connection string in application.conf file:

db.default.url="mysql://db_user:db_user@localhost/mydb?autoReconnect=true"

I am trying to set this connection parameter because I am getting this error in my application after it is idle for a long time :

[error] c.j.b.ConnectionHandle - Database access problem. Killing off all remaining connections in the connection pool. SQL State = 08S01
[error] application - Failed to login the user : guest
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully  
received from the server was 153,398,761 milliseconds ago.  
The last packet sent successfully to the server was 153,398,762 milliseconds ago. is longer than the server configured value of 'wait_timeout'. 
You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

I also tried setting these play db parameters to fix this connection issue as mentioned here (https://groups.google.com/forum/#!topic/play-framework/KzvbZ61j9Eo) but it didn't solve the problem.

idleConnectionTestPeriod=10
testConnectionOnCheckin=true

Any guidance towards solving this problem would be appreciated.

Thanks.

[jss]

try this

autoReconnect=true&autoReconnectForPools=true&interactiveClient=true&characterEncoding=UTF-8

[jss]

try this

autoReconnect=true&autoReconnectForPools=true&interactiveClient=true&characterEncoding=UTF-8

On Saturday, July 27, 2013 2:28:26 PM UTC-4, Shishir Prasad wrote:

[Shishir Prasad]

Hey,

Thanks for the reply. I tried your suggestion and although the play server is starting now without throwing any error messages but the intended purpose of these additional arguments is still not being served. I am still getting the same error of timeout when my connection is idle for a long time.

I think Play automatically appends some configuration parameters in the connection URL and that is messing up the final connection URL. For example, this is the connection URL I have specified in application.conf. As you can see, apart from these new connection arguments, some default arguments are again being appended to the URL. Is that fine ? Please advise.

db.default.url="mysql://db_user:db_user@localhost/mydb?autoReconnect=true&autoReconnectForPools=true&interactiveClient=true&characterEncoding=UTF-8"

[info] play - database [default] connected at jdbc:mysql://localhost/mydb?autoReconnect=true&autoReconnectForPools=true&interactiveClient=true&characterEncoding=UTF-8?useUnicode=yes&characterEncoding=UTF-8&connectionCollation=utf8_general_ci 

[Adalbert Wysocki]

Hey Shishir,

Were you able to solve your issue?

I have the same probelm and the added autoReconnect param is working as I get the error below:

Error: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 41,607,166 milliseconds ago.  The last packet sent successfully to the server was 41,607,166 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

Thanks

Aldo

[Shishir Prasad]

Hey Aldo,

I was not able to get this issue fixed with the solution suggested here or elsewhere :( I ended up writing a cron job that bounces my play server every couple of hours to avoid this issue. Hacky but it worked for me :)

- Shishir

--
You received this message because you are subscribed to a topic in the Google Groups "play-framework" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/play-framework/FVaoMnwHl2g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to play-framewor...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
http://shishirprasad.com/