MySQL and Threads Warning

If you are using MySQL and you are using Threads you will run into issues. <https://xojo.com/issue/44904> This affects Shorts because we use a thread during schema operations to keep the UI responsive.

I have not checked to see if it affects other database servers or not.

I use MySQL in threads, but haven’t noticed this. I keep the connection variables around then connect to the MySQL db only as it’s needed, usually because my hosted databases time me out after 15 seconds of inactivity!

Is this macOS, Windows, Linux or all?
Is this desktop, WebApp or all?
Does this affect just the MySQL Community Plugin or SQLDatabaseMBS as well?
What is a ‘secondary thread’ from the Feedback? I usually connect to the db, retrieve the recordSet and do any updates within the one thread. Does the problem arise if you connect to the db then pass it to the thread?

In my case I’ve only tested on Mac OS X desktop app with MySQL running on a different computer. I establish the connection in the main thread and then the thread uses that connection. Any query that I run never comes back.

I will see if establishing a new connection in the thread solves the issue.

This is for Shorts and it is not yet compatible with SQL MBS.

I would love you make it compatible :slight_smile:

I think it is not recommended to create a connection on one thread and use it on another. At least not use them the same time.
I think in MBS plugin I have code to prevent that and raise an exception.

Well, it’s on the list and someone did the work of an initial port. However, it’s not bubbled it’s way to the top in priority yet.

So I did some more work on this yesterday. Adding a second connection from within the Thread did nothing to mitigate the problem. However, I moved the priority on the thread from 5 to 10 and poof reports started working again. Odd, I know.

so maybe a timing issue?

e.g.

Thread 1 starts a MySQL operation
Yields time
Thread 2 does something with MySQL
Yields time
Thread 1 finishes MySQL and got error?

No errors ever happen. No database errors or exceptions. The app is still responsive but the thread doing reading the recordset simply never finishes. As far as I can tell it loses control when it accesses the recordset.

The problem didn’t exist in Xojo 2016 r1.1 so I’ve gone back to that for existing apps which initiate and access MySQL within a thread. BTW as I’m sure you noticed the problem was much worse in 2016 r2.0 as it actually froze the thread (case 44608). In 2016 r2.1 the thread stays “active” but eventually stops service down-line code. Thanks for the tip on thread priority but I’ll stick with 2016 r1.1 until they fix the problem.

I know this won’t help, but…

Same here. I use up to 5 and more concurrent connections to 5 mySQL Databases on Servers within our Intranet and via SSL Connections outside our Intranet and a few others like MSSQL Databases. All within one App on Windows 7 (32 + 64 Bit), Windows 10 (64Bit) and OS X El Capitan. I am also making heavy use of various Threads at the same time with Priorities 1, 5 and 10. Sometimes a connection is created in a Thread and used among other Threads and sometimes it’s created in the Main Thread and used within other Threads.

I’ve never seen such an issues (afaik… ;))

I attached a small project file to Case 44904 which demonstrate the problem. Just watch the System.DebugLog output to see where the thread stops servicing code. BTW it uses a MySQL instance open to the internet which, of course, is only there to demonstrate this issue. :wink:

I believe that Xojo is aware that the Mysql plugin is not thread safe. If I recall, they planned to do some work on this a year ago.

This technique worked fine right up until Xojo 2016 r2.0. I’ve been doing it this way since RealStudio 2012. MySQL plug-in or not, this particular problem just surfaced recently.

Yeah, R2.0 was awful and not recommended. It’s better in R2.1 but obviously some issues still exist.

[quote=283623:@Sascha S]I know this won’t help, but…

Same here. I use up to 5 and more concurrent connections to 5 mySQL Databases on Servers within our Intranet and via SSL Connections outside our Intranet and a few others like MSSQL Databases. All within one App on Windows 7 (32 + 64 Bit), Windows 10 (64Bit) and OS X El Capitan. I am also making heavy use of various Threads at the same time with Priorities 1, 5 and 10. Sometimes a connection is created in a Thread and used among other Threads and sometimes it’s created in the Main Thread and used within other Threads.

I’ve never seen such an issues (afaik… ;))[/quote]

I’ve double checked my Threads and found numerous i’ve set to a Priority of < 5. Those Threads sometimes took ages to complete if mySQL Connections were involved. After raising the Priority to 5+ they are finishing blazing fast again.

Added it to my top 5 and it went from place 66 to 31. :wink:

Woohoo! Looks like it was marked fixed an hour ago!

That’s awesome. Hopefully it makes it into R3.

It should, but that’s obviously not a firm promise.