I posted about this awhile back in an older topic, but this has become worse with 2021r1:
The RowData function for a WebListBox 2.0 errors out when loading data from a MySQL DB with, “A Query is already in progress”. In my tracking this down, I can make the WebListBox example app do the same thing by changing the source database from the SQLite version to a MySQL version. You can see the same thing by importing the Eddies Electronics DB into a MySQL DB and changing the connection to it.
My theory is that the SQLite db doesn’t run into it because it’s a local file, in memory and not subject to network latency and the overhead of remote query. It’s not my database server… it has 20 physical cores, 384GB RAM, 10GB NIC and has no problem running huge queries extremely fast.
This issue along with the WebListBox.SelectedRowIndex always returning -1 when using filtering (FB 63801) makes the new WebListBox pretty unusable for me.
Before I file a bug report, has anyone found a workaround for using MySQL as a WebListBox datasource while avoiding this? Not being able to use the new WebListBox and it’s datasource features is extremely frustrating and I need to get this done.
I believe the issue on 63801 has been fixed … sort of. With 2021r1, the listindex works properly on my WebListBox’s with a datasource only when there is an exact match with the filter (eg. Lastname field has an value that matches the filter exactly). If there is only a partial match, it still returns -1. I’ve created case 64228, but there has been no status change to it.
I have to generally agree with you though, I’m finding the performance to be so sporadic which makes it incredibly difficult to work with (let alone consider it in a deployed project!). I’m using PostgreSQL as a back end, and although I’m not receiving any errors, it will occasionally just hang without loading any further records/rows. Very frustrating … almost to a point where I’m ready to reconsider using the WebListBox with a Datasource.
The problem is that you can’t have two actively running rowset/recordsets in MySQL on the same database connection at the same time. You’ll need to create a new database connection for each call to get rows if you are running into this.
If that is so, then Xojo is the only dev platform I’ve used where I had to ACTIVELY call open/close db connections to MySQL… ever. Per session (as this is), sometimes, but doing this actively would result in an insane number of connections and closures based on what I’m observing. I’ve never run into this error before even with Xojo until trying to use WebListBox.
I think the question is, why is the first query still running when the second one happens on the current connection? Shouldn’t the data have already been returned and the query ended before the next query is sent? This problem is in the Example included with Xojo and is the only thing loading, not just my own. Is the MySQL plugin asynchronous? Is the WebListBox firing multiple asynchronous requests?
I will try your suggestion as well, but I’m still wondering why this only happens with the new WebListBox…
First, you need to remember that all requests that come from a browser are run in a thread. Next, when more than one thread is running, threads yield to one another on loop boundaries.
So here’s what happens with a WebListBox:
Page loads or Listbox is reloaded
Command is sent to the listbox in the browser
JS listbox requests the number of rows
Datasource responds with, let’s say 200.
JS listbox requests the first three batches of rows, in 20 row increments, each in a separate request.
Here’s where the fun begins…
First request comes in, the query is created and a rowset is requested.
The rowset is iterated.
The first time the Wend boundary is hit, the thread manager switches contexts and the second request comes in and a rowset is requested…
The problem you are running into is that the first request hasn’t finished yet when the second comes in and starts running. You’ve got two possibilities:
You do what I suggested and have multiple database connections, or
You create a criticalsection and prevent the second query from starting until the first is done.
#1 will be faster and will require more system resources, #2 will be slower but will have much lower overhead.
Other dev platform use connection pools to make this connections and closures automagically. It is true that Xojo is more archaic in this matter and you have to do it manually, but in the end it is the same the DBMS is made for this kind of work. The week link is the single threaded Xojo app, if you see performance problems, you need to run multiple instances of the app with some load balancing.
Thanks Greg, appreciate the flow, quasi-asynchronous Thanks for the heads up on CriticalSection, didn’t know about that!
I saw the updates you made on the example with Andrew’s case (FB 64228), so I took that, fixed the RowCount statements as you suggested, implemented 1 off db connections for each SQL call, add a little code to get the search button and cancel buttons to work and it now works mostly as expected.
After selecting a row in a filtered set of data, if you removing/changing the filter, the row that was selected is still selected in the full list, but the SelectedRowIndex goes back to -1. Deselecting it and the reselecting it causes the SelectedRowIndex be correct.
I would expect that SelectedRowIndex would return the selected position in the returned data of the row that is still selected and the ListBox would be scrolled to that position, but this may not be how it’s expected to work?
I guess you could partially work around it by setting the SelectedRowIndex to -1 before refreshing the data to remove the selection, but not sure how to accomplish the other or if it’s even possible?
Thanks for the clarification, connection pools/pooling. I’m just very surprised the MySQL plugin doesn’t support connection pools/pooling at this point. I haven’t run across this issue before and assumed it did.
Thanks for the heads up on CriticalSection, didn’t know about that!