MySQLClientException: Can not connect: status is not fresh

[booper]

hello, you did a great job on this lib, thank you

From time to time I got this exception:

MySQLClientException: Can not connect: status is not fresh
package:mysql_client/src/mysql_client/connection.dart 116  MySQLConnection.connect

may you please suggest a good way to manage the connection? and is there any getter method to check _state of the connection ?

[zim32]

Hi booper. You can use connected property to check connection status.

Seems like connection is closed and you are trying to use the same connection object to initiate new connection. FIrst thing to note, you can not reuse connection object after it is closed. Second point, we need to understand why connection is closed..

[booper]

hey, yes, I check connected property to determine the state of the connection, and when the connection is closed I try to use the same connection to reconnect ( with the connect method ).

It happens every time when the application with an established connection doesn't communicate with the MySQL server ( version 8 ) for a long time, in my case it is about 3 - 4 hours.

[zim32]

Can you recreate fresh connection instance?

[zim32]

You can wrap it in some function which creates connection for you, and then reuse it.

[zim32]

I am not sure about what to do if connection is closed due to some timeout or other database specific things

[zim32]

Maybe we need to implement some kind of ping to keep connection alive

[booper]

Can you recreate fresh connection instance? You can wrap it in some function which creates connection for you, and then reuse it.

Sure, already handled it, and when catching such an exception I just recreate the connection instance.

Maybe we need to implement some kind of ping to keep the connection alive

Good idea, I am exploring MySQL docs to understand what is the reason MySQL closes the connection with the client on a long time idle.

check this out: https://community.pivotal.io/s/article/Idle-Database-Connections-Dropped-by-MySQL?language=en_US

[booper]

So, based on what I learned:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

MySQL has its [wait_timeout] variable default value set to 28800 seconds (8 hours).

So, not a big deal, what I have to do is just check the state of the connection before executing every query and in case of connection is down recreate it.

[zim32]

Mysql docs is not super informative)

[zim32]

I am trying to keep the library as slim as possible, meaning that if something can be implemented by user easily it should not be part of this library. I think someone can implement ping by periodically executing some dummy quey like SELECT 1 or similar

[zim32]

I just need to add this into README. Thank you for investigating this issue

[booper]

I just need to add this into README. Thank you for investigating this issue

Thank you for a great job on this lib. Eventually, you are right, keep the lib as slim as possible - the connection handle is the developer's responsibility, not the lib itself :)