MySQL reconnection trouble

Xojo 2020R2.1, Desktop app using MySQL database

I think this problem appeared after converting the app to API2. If the network connection to the database drops, or the database server restarts, I used to get error 2006 or 2013 which I check for and reconnect the database.

Now when the connection drops, I get error -1 with a message like “Expected 0 parameters to be bound but received 1”. Attempts to reconnect seem OK, but further SelectSQL calls fail with the same -1 error. Only fix I’ve found is to restart the app.

I am taking advantage of the “built-in” prepared statements with API2, the old version of the app did not use prepared statements (except in a few areas where user input was being processed.)

Any thoughts or suggestions would be appreciated.

Hi Eric - I am about to embark on connecting a MySQL db to my own new 2020R2 app, so in the next day or two I may have something to offer. Will keep an eye on this ticket and contribute if/when I can. Best of luck!

Just a heads up for your adventure in API 2:

60656 - MySQLCommunityServer BeginTransaction raises DatabaseException with Syntax Error

I had a mysql disconnect issue that took me several days (weeks?) to figure out. … There are several timeout settings in mysql. In my case the connection was timing out around the six to eight hour point. I could have changed the timeout setting but that would introduce other issues later - i.e. connections that never close and eventually lead to a memory aka ‘connections’ leak. Instead I put an event table in the database and created a timer to insert a heartbeat record every five hours. That same routine, also just deletes records older than XX time old to keep the table size small. Now the program is working perfectly.

You can still get disconnects from network issues. Also if the user puts the computer to sleep the connection will usually be broken when the computer wakes up. So we still need to be able to detect and reconnect when anything goes wrong.

You don’t need to insert/delete records to do a “ping and keep alive” connection. A simple “SELECT 1” could do the job.

These are all true statements. In my program however, it’s mult-part server side and the client is via POSTs / GETs. In this particular case, since the server never sleeps the timer keeps the connection open. Plus by adding in events (the heartbeat is not the only entry), I can see the status of what is happening along the way and if someone has an issue, the event log might hold a clue as to what is wrong.