0

How do I find out which statement is out of sync when I get error 2014 with Multi-Statements?

I am calling libmysql.mysql_query with a SQL Connection with SupportsMultiStatements, passing a string of commands separated by ;. This code runs all day without errors. But every few days (with no regularity) my application throws:

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

Retry always works.

The error message does not specify which of the multiple commands is "out of sync". Is there any way to know?

This question is not how to fix my specific problem, so I haven't included the list of commands. (I suspect the intermittent problem is caused by a timeout waiting for table lock.) This question is about how error 2014 applies to multi-statements.

Improve this question

2 Answers 2

Reset to default
1

Usually that refers to Stored routines with PREPARE + EXECUTE + DEALLOCATE_PREPARE. Have you done all three of those, in that order? Optionally, EXECUTE can be repeated.

If you are referring to stringing together statements with ;, don't do that; it is a security hole waiting for some hacker to jump into.

What client language are you using?

If you need more help, let's see your code.

Improve this answer
6
  • Not a stored routine. Multiple statements strung together with ;s. This is not a web-enabled app. It's a desktop app written in Delphi 7.
    – Guy Gordon
    Commented Jan 11, 2017 at 4:20
  • Then I suggest breaking into separate statements and forget the 2014.
    – Rick James
    Commented Jan 11, 2017 at 4:56
  • Yes, I'll try that. But the commands will still be in the same order. It doesn't seem passing them one at a time would make a difference. Surely the server is simply splitting the string and executing them one at a time. By 'forget the 2014' do you mean you don't expect it to reappear, or that the code should automatically retry?
    – Guy Gordon
    Commented Jan 11, 2017 at 17:08
  • If it reappears, please show us the actual statements that are causing the 2014. I expect it to be something about the particular statements that causes the error.
    – Rick James
    Commented Jan 11, 2017 at 17:36
  • You cannot use IF, OPEN, DECLARE, and other Stored-Routine-only statements outside.
    – Rick James
    Commented Jan 11, 2017 at 17:36
0

The answer is you can't; so don't do that. Also, as Rick James pointed out, multi-statement SQL is a security hole that can allow a hacker to terminate your SQL and execute his own.

In my case, I solved the immediate problem by having my exception dialog display the entire multi-statement SQL that caused the error, formatted to be readable in the dialog box. Turned out that under certain conditions (backup locking a table) the statements were not in the order expected.

I'm using multi-statement to 'batch' my commands to a MyIsam table. I was planning to simply split the string on ';' and execute each cmd separately, and disable multi-statement on the connection. Then I realized that will still leave the same security hole. It doesn't matter if my code or the connector implements multi-statement.

So my plan now is to store each cmd in a TStringList, then submit them in order when the process is done. If one of the cmds is hacked to contain ';new cmd' the connection will throw an exception.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.