Commands out of sync; you can't run this command now

Question

User1434241939 posted

I am running a .Net WebForms site that queries a MySQL database repeatedly in order to fill drop-downs on a form.  The queries are almost all stored procedures if that makes a difference.  This has worked consistently for over a year. 

Sometime during the last week, with no code changes one of my pages started throwing the "Commands out of sync" error.  But for some reason, it does this inconsistently.  If I refresh the page several times, the page eventually loads successfully.  But if I re-load again, it may succeed but may fail again.

After reading up on the error, it seems that my code isn't consuming all the data returned from the stored procedures.  Maybe I need to close my ODBCDataAdapter or ODBCDataReader before opening the next one, or maybe I have to send some special command to clear out the query.  

Here is the code that seems problematic -- myda is only created successfully sometimes:

public DataSet FillDatasets<T, T2, T3, T4>(T con, ref T4 reader, string sql, string tablename)
            where T : IDbConnection, new()
            where T2 : IDbDataAdapter, new()
            where T3 : IDbCommand, new()
            where T4 : IDataReader
        {
            // Declare a DataAdapter, could be OleDB or ODBC or something elsee
            T2 myda;
            T3 mycommand;

            // Local dataset
            DataSet ds = new DataSet();

            // This is the StackOverflow way to instantiate a Data Adapter without arguments (which throws an error)
            mycommand = (T3)Activator.CreateInstance(typeof(T3), sql, con);
            reader = (T4) mycommand.ExecuteReader();
            //mycommand.Dispose();

            myda = (T2)Activator.CreateInstance(typeof(T2), sql, con);
            myda.Fill(ds);
            ds.Tables[0].TableName = tablename;

            return (ds);
        }

The code is called like this:

            DataSet ds = rs.FillDatasets<OdbcConnection, OdbcDataAdapter, OdbcCommand, OdbcDataReader>
                (MySQLConnection, ref MySQLReader, sqlString, "ActiveTrips");

I would attempt to narrow down the problem more (or offer more code) if I knew which direction to go with this.  Any help appreciated.</div> <div>Thanks</div> <div></div>

Answer 1

User475983607 posted

The MySQL docs explain this error.

https://dev.mysql.com/doc/refman/8.0/en/commands-out-of-sync.html

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

My best guess is the code is not closing connections or cleaning up resources properly.  The code probably works properly when each Filldatasets call completes in order.  You're passing the reader which is a reference type by reference so that's most likely the culprit.  IMHO, a very fragile design. 

Answer 2

User1434241939 posted

mgebhard

My best guess is the code is not closing connections or cleaning up resources properly. 

Thanks for the direction, I'm pretty sure that's what was happening.  I was largely able to fix the issue with these 2 lines of code after ExecuteReader() was called: 

                mycommand.Cancel();
                mycommand.Dispose();

I needed both Cancel() and Dispose() for it to work.

There were some further issues with returning the reader by reference (it seems that you can't dispose of mycommand until you are done with consuming the reader) so you were right about that too.  I put in a hack to get around that which I'll come back to fix someday.

Regarding the PHP calls, I would have issued them if knew how to do so from C#.  Who can say what the corresponding ODBC commands are that perform the same function as mysql_free_result() ?