Issues with (64bit?) Windows MySQL driver

[Bob Sneidar]

I had an issue with my mySQL server some time ago that revolved around making multiple connections in a short period of time. I would hit the connection limit of the SQL server and it would stop accepting connections. My issue was that I didn’t realize at the time that I needed to disconnect after every session, because the SQL server will keep a connection alive for a period of time specified in the server settings. If you are already connected and you create a new connection it will create a new thread.

If you have MySQLWorkbench (or some other server manager) you should be able to see the current connections. The MySQLWorkbench will have it’s own connections but if you see a lot of other connections that you can’t account for, then you need to ensure that you are disconnecting after every session.

Bob S


On Aug 16, 2024, at 1:14 AM, Mark Waddingham via use-livecode <use-livecode at lists.runrev.com> wrote:

On 2024-08-16 08:32, Ben Rubinstein via use-livecode wrote:
I have a tool (a LiveCode standalone) running on Windows, which every night drops and recreates a database on a remote MySQL server, (about 350MB, 50 tables). Running for many years.
About a year ago, we started to see a problem where sometimes the nightly build would fail, part-way through the process. The routine involves creating and populating tables, then creating indexes. Depending when the problem hits, the initial error is either
Connection was killed
or
Lost connection to MySQL server during query
All subsequent calls to revdb_execute get the error
MySQL server has gone away
I tried splitting the build into sections, so that the code opens the connection to a database builds some of the tables, then closes the connection, and opens a new connection to add more tables. There was no evidence that this made the issue occur less frequently; and once it hit, subsequent attempts to open a connection would get the error
Can't connect to MySQL server on '<address>' (0)
When this was happening maybe a couple of times per month (on average) it didn't matter too much (the system is designed to be resilient, if the data wasn't refreshed one day, it would be the next). I thought it might be network glitches.
Recently IT tightened security on the machine where the tool runs; and since then we get this problem nine times of out ten. They say the only change made was to remove the admin privileges of the user account, and have now reversed that change; however, this problem has remained since. Another problem that arrived at the same time, reported here as "a windows weirdness", seems (per Paul Dupuis and Mark Waddingham) to be related to UNC paths, and possibly to security policies.
Does anyone have a suggestion for how conditions could affect this? Is there any way to get more detailed information out of the rev database driver about what's happening?

The dbmysql is just a thin wrapper around the mysqlclient library which is basically just implementing a protocol over a socket.

The fact that you get 'Can't connect to MySQL on '<address>'' after it happens sounds very much like there's some sort of blocking going on at the system level. (A bit like most servers have 'portsentry' or similar on it which blocks requests which look dodgy) - given the 'tigtening of security' this is quite possible...

I think there are some low-level network tools on windows you could use to look at what's happening with sockets/ports (e.g. https://learn.microsoft.com/en-us/sysinternals/downloads/tcpview).

I was going to suggest tweaking the timeouts/auto-reconnect parameters on your revOpenDatabase call - but I'm not sure its a timeout due to the 'Can't connect' error happening subsequently.

Warmest Regards

Mark.

--
Mark Waddingham ~ mark at livecode.com<mailto:mark at livecode.com> ~ http://www.livecode.com/
LiveCode: Build Amazing Things

_______________________________________________
use-livecode mailing list
use-livecode at lists.runrev.com<mailto:use-livecode at lists.runrev.com>
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode