Has anyone ever had experience with this error before? I had several users using a Xojo Cloud app that connects to a MySQL database. They crashed and the application went offline and the only error logged around that same time was several database requests that issued that error.
I’m not sure if that caused the error or if it is a symptom of a different issue.
I was wondering if that error is specific to a specific SQL query or if MySQL will issue that message if it is having trouble handling multiple queries of any kind.
Google and MySQL docs show no real info about the error. Any insight would be appreciated!
“A query already in progress” means just that… the database is single threaded and can’t accept another request until it completes the pend/in progress request.
I used to be the webmaster for a huge international organization, and I wrote the website in PHP and mySQL, I would have 100’s of users online at one, and never had this problem. However, all the database queries were short and fast…
i know its not mySQL, but on MSSQL usually you cannot have two running queries on the same connection.
if you need to run a second query while you have an open recordset (cursor) you would need to create another connection.
there is a technology called MARS (Multiple Active recordSets) for MSSQL, but i find it easier to just use another connection.
I will be testing a new version in just a bit, but the tech support folks at Xojo helped identify the main problem was that the database connection was not declared in the Session–instead, I had declared it within an application module, which meant that all users were sharing the same connection and bumping into each other when traffic increased.
From what Christian says, I may need to create a new database connection for my executes as well!
I had this problem today and I am quite sure that my code was OK. So I expanded my error handler to handle MySQL errorCode “48879”, which is this specific error. It just waits for 1 second and then tries to execute the preparedStatement again, recursing through the error handler in a loop. I also logged this in my stdout and noticed that the code was now able to get out of the problem, and I was also able to see when exactly this happened. I do a lot of inserts and the MySQL schema has some rather heavy UNIQUE contrains. I think that MySQL is doing some housekeeping after the previous insert query and returns this error when it is NOT READY to process the next query. To me, MySQL seems to be threading the housekeeping, but does not take into account that the connection is really not ready for the next query. My 5 cents.
The most important Part here is the “the same SQL Connection”. If you need to do multiple queries at once on 1 Database, open a new Connection for each query.
But there’s also a Limit on the Server for concurrent open connections and Memory Buffers…
Wrong about my code being OK. It just works now. I know about different connections, I already use 3. I was just mixing them up in my error handler. Doh. Good remark about the memory buffers. I must keep that in mind for later.
Maybe you should try to put as many of them as possible, in 1 Transaction?
BTW: Another advantage of Transactions is that if there’s an issue while building/performing your Transaction, a Rollback will roll back all changes between the Begin and End of the Transaction.
I have data to send that could be corrupted. By commiting every insert, I am able to cope with invalid records. Performance is not really an issue as it is a background process that has all the time in the world. Thanks for the suggestion though.
