Issues with (64bit?) Windows MySQL driver

[Mark Waddingham]

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 ~ http://www.livecode.com/
LiveCode: Build Amazing Things