Mysql using mym - "Commands out of sync" error

Hi All, I'm using mym to extract data from a mysql database. The first SELECT query I send works just fine, but subsequently I get an error:

Error using mym

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

If I re-open the database (i.e. run mym('open',...); mym('use',...')) then everything works again, but this costs significant extra time. Any advice would be greatly appreciated.

Eran Mukamel [e-mail]

[Sandy]

I had the same error, and I figured it out, but I don't really understand what I did. In the source file mym.cpp, find this: "if (!mysql_real_connect(conn, host, user, pass, NULL, port, NULL, CLIENT_MULTI_STATEMENTS))", and change CLIENT_MULTI_STATEMENTS to CLIENT_MULTI_RESULTS. This works for me, but I am not sure whether there will be other problems.

Answer 1

[arushi]

Hi Eran,

The "Commands out of sync" error is a known issue with MySQL client libraries, including mym, when the sequence of command execution is not properly managed. Here are some strategies to address and potentially resolve this issue:

Ensure Complete Result Fetching:

• After executing a SELECT query, make sure you retrieve all the result data. If you do not process all the rows returned by a query, the connection can become out of sync

Clear Results Explicitly:

• If you are not interested in the results of a query, you may need to explicitly clear them. Some libraries require this to maintain synchronization.
• If mym provides a way to clear results, use that function after processing or discarding the data.

Avoid Mixed Query Types Without Processing:

• Ensure that you do not mix SELECT queries with other queries (INSERT, UPDATE, DELETE) without processing the results in between.

Check for Pending Results:

• Before sending a new query, make sure there are no pending results from a previous query. This might involve checking the state of the connection or ensuring all results are fetched.

Update mym Library:

• Ensure you are using the latest version of mym. There might be bug fixes or improvements that address synchronization issues.

Hope this helps.