I built a couple small desktop utilities that access a remote MariaDB connection. A window stays open and after an hour or two, I try to do something and an exception is fired because the connection has been dropped. What’s the best practice here. Currently when I open the window I open the connection and a list of data is displayed. Should I explicitly open/close the conn on each CRUD operation or …? It’s not a smooth user experience to timeout and then manually have to reconnect each time that happens.
1 Like
I am new to Xojo but have been programming for years. It sounds like MariaDB is timing out your connections. Before you use a connection you should always test the state to make sure it is still connected and in the proper state to run a query. If not programmatically close the connection and open another one with the same credentials. This way you always have a good connection when you want to use it, and you don’t pay the high re-connection penalty of closing and opening a connection for each transaction.
Just my 2 cents.
Well that what I’m doing - if there is no connection the exception fires. But when I try to programmatically reopen the connection, the window freezes.
for my postgres databases, I send a "select 1’ each 5 minutes or so using a timer.
but I would prefer to reconnect each time a query has been made.
I use MySQL for desktop apps that may be left running for hours. The connection does drop sometimes. Might be a server setting, but when I first discovered this, I figured it could be server issues, network issues, or other things beyond my control. So I decided to make my software smart enough to detect and correct the problem in the background. I do all my database calls (sqlSelect, sqlExecute) in jacket functions that checks the error codes and reconnects if necessary. Users have no idea and never notice any problems or delays with this. I’ve included partial (and untested) sample code below. This uses the old API, release 2019R1 and earlier.
[code]
Public Property db as MySQLCommunityServer
Public Function DBSelect(ByRef rs As RecordSet, sql As String) as Boolean
rs = db.sqlSelect( sql )
If db.Error Then
If db.ErrorCode = 2006 Or db.ErrorCode = 2013 Then ’ server has gone away or connection lost
Call ConnectToData ’ re-open the connection
Call DBSelect(rs, sql) ’ and try again
Else
’ other error, issue error message or take other action
Return False
End
End
Return True
End Function
Public Function ConnectToData() as Boolean
db.Host = “hostname.mydomain.com”
db.Port = 3306
db.Username = “me”
db.Password = “takeaguess”
db.DatabaseName = “mydb”
If db.Connect Then
Return True
Else
’ issue error message: “Unable to connect to database” + db.ErrorMessage
Return False
End
End Function[/code]
Thanks for your code, Eric. It got me to thinking. I am actually running out of the IDE, not as a standalone exe (yet). I had “break on exception” checked. That’s what caused the freeze. When ever I timed out, it would return me to the debugger (fine) and I would step through the exception. When it got to App.dbABSConnect to reconnect, that’s when the app would freeze up. I suppose it’s some kind of bug. Anyway, when i unchecked it all is well.
@Eric_Bloom,
I was just working on similar MySQL connection lost problems as I work to upgrade a WE app and had been thinking about how to address. So I checked out XOJO forum and found this thread.
You confirmed what I was thinking. I implemented something similar as a global passing the MySQLCommunityServer var and all is good. I use this with many, many other functions throughout.
Interesting to see a rise in MySQL time-outs. The network responds within 4ms. MySQL Workbench remains connected after a day without use. Not certain the cause of these time-outs but using the recent XOJO starting in first release 2020 they occur very frequently.
I report in syslog when this fires and I see this working seamlessly and no one knows it and I continue the request.
Very nice
Carl
MySQL defaults to a 4 hour timeout. Workbench must be querying the state of the connection and reconnecting as needed. I do the same in my software. The cool thing is I can reboot the server and the users don’t even notice.