I have a desktop app that I run 24/7 that queries a MySql database on the same mac weber 5 seconds or so.
About twice a week I come back into the office to find the app throwing a database error: The database he’s gone away.
Im at a total loss to even know what questions to ask so ill ask: How do I make it stop doing this?
You need to implement reconnect handling, not a fun thing. This happens to almost anyone who is running a long term connection. Wish I had an answer why, but it’s not limited to MySQL or Xojo. Any long term DB connection needs reconnect logic.
No matter how closed a system, you have, soft connections between clients and servers can get disconnected. You need to detect disconnects and reconnect. You might be able to mitigate the problem by periodically forcing a disconnect and reconnect. But connections just aren’t guaranteed to stay connected.
One approach that I have used is when I get an error, I check the connection and attempt to reconnect, then resubmit the request. If it fails to connect or errors again, I bail out.
Of course, doing so only makes sense in a subclass. I would be hell to try and code that at every query.
I assume there is no event that fires when the connection is dropped: which would mean I need to check for connection every time I query or write to the database ? That seems like a lot of overhead.
Perhaps I should go with an ugly hack that just drops and reconnects every hour or so…
And what happens if your connection drops? Are you going to wait up to an hour for it to reconnect?
It is not a lot of overhead… you should have error checking on every DB transaction anyway…
and if the error happens to be that the database is closed/disconnected,… then and only then re-establish the connection
I once had this same problem. In my case it was related to max_allowed_packet which is set to 1Mb by default.
You can change it by SET GLOBAL max_allowed_packet=12 * 1024 * 1024; for example.
And then you can verify if you still have the same problem.