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

Question

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.

Brebner

did this work? I added ?autoReconnect=true to the jdbc url but when I wait too long it stil gets the same error, it doesn't reconnect automatically – Hydrogenate

Answer 1

obviously something is appending '?useUnicode=yes' to your URI, so you end up with

mysql://db_user:db_user@localhost/mydb?autoReconnect=true&useUnicode=yes

parsing this would give you that the value for autoReconnect is true?useUnicode=yes

dig in a bit, maybe your connection pool or data abstraction layers are doing that.

Closeknit

Thanks, I had encoding problem ad jdbc:mysql://127.0.0.1:3306/Mysql?relaxAutoCommit="true" in my file. Just removed it and it works. – Supertax

Answer 2

You have 2 connection parameters, concatenate them with & instead of ? :

mysql://db_user:db_user@localhost/mydb?autoReconnect=true&useUnicode=yes

Disembogue