Clearly I am missing something, but here is the setup:
subclass of thread (call it mThread) takes sql parameters and a query to run as arguments.
It creates its own connection using MySQLCommunityServer object, then runs the query. It stores the result in a property, when the query is done the isFinished flag of the mThread class is set to true.
A window has a an mThread property and a timer checking the isFinished flag.
As a point of testing I never actually access the results of the mThread property or do any UI updating.
The window creates a new mThread with a long running sql query, and starts the timer. Now the UI locks up and I get the beach ball (using MAC). Changing the timer period does nothing (as expected). Changing the query to a faster query is the only thing stops the UI from locking.
What is going on? This seems like the opposite behavior of a thread. Now to be clear, the thread has its own connection the the database, it does not share that with anything else.
the database queries are NOT threaded.
you must use the limit and offset keywords to reduce the length of your query
or
use MBS database plugins that are thread friendly.
dim rs as RecordSet = connection.SQLSelect(searchQuery)
dim resultString as string
if rs <> NIl then
while not rs.EOF
for ii as integer = 1 to rs.FieldCount
if ii <> 1 then
resultString = resultString + "," + rs.IdxField(ii).StringValue
else
resultString = rs.IdxField(ii).StringValue
end if
next
results.Append(resultString)
rs.MoveNext()
wend
else
errorMessage = connection.ErrorMessage
end if
connection.Close()
isfinished = true
Since the threading is cooperative, its up to the threads to yield time to other threads. In the case of normal Xojo code the threads yield every time there is a loop boundary and in many other places too I think. Once you call out to some library or other function, such as the running of the query, unless those functions are specifically written to yield time back to the other threads then it will block the entire app just as if you hadnt bothered to put it into a thread at all. Unfortunately the database stuff is mostly like this. If using the MBS plugins is not an option then you should break your query up into blocks as Jean-Yves Pochez suggested. Then run it in a loop to get through all the data and the very act of returning control to some Xojo code and looping will make sure the other threads get serviced, though you can specifically perform a yieldToNextThread in your loops if you want to experiment.
And I think you should definitely consider putting in a feature request for the built in Xojo plugins to add some proper support for threads as they make such things very difficult right now. There might even already be one…
For the record I’ve been using Christians plugins since forever and can whole heartedly recommend them as a solution to this problem and many others. Well worth the money if its a viable option. That doesnt mean you shouldnt bug Xojo with an official feature request to properly support their own threading model in the database plugins though
hm - IIRC years ago the built in Database Plugins all had a MultiThreaded property to control if they shall yield. As this costs performance, they then were refactored so that this behaviour is automatically detected. Just PostgreSQLDatabase has kept it’s MultiThreaded property. The documentation for MySQLCommunityServer even states [quote]“SQLSelect and SQLExecute statements do not block when called from within Threads.”[/quote] If they do, I’d count this as an Bug that should be reported. I did so with PostgreSQLPreparedStatement which did block despite MultiThreaded in <https://xojo.com/issue/37806> and got it fixed within a comparable short period of time.
yes there is a MultiThreaded property, but it is not used it seems.
I already made a feedback some times ago, then as it was not corrected at all I programmed differently …
it is definitely a bug, and one that will certainly never be corrected …
are we talking about MySQLCommunityServer here? According to the Docs the property was in use from 2011r1 to 2013r3 only.
[quote]I already made a feedback some times ago, then as it was not corrected at all I programmed differently …
it is definitely a bug, and one that will certainly never be corrected …[/quote]
That sounds quite disappointed. To my experience, if a bug is really a problem for you, Xojo will fix them in the end, even if relatively specific, but lobbying for it takes some time and effort. If you need a quick solution, you can head over to MBS, but this possibility should not be an excuse for not fixing upstream bugs in the end
I thought the issue is with Connecting to the Server only, because i tend to prefer short/quick querrys. And i created a FR for the connect issue in January 2019: <https://xojo.com/issue/54692>
the code posted by the OP did not contain the db.Connect within the Thread.Run so I assumed this is done earlier. But yes - as of current there is no promise about db.Connect being thread friendly.
