4

I have a query which was working fine a few days back:

SELECT
    id, title, details, filetype, filepath, size, details, location, datetime, 
    IF(hid=1, 'Anonymous',
        (
            SELECT name 
            FROM users 
            WHERE did = userid
        ))
    AS username 
FROM infotable 
WHERE ext2!='0' 
ORDER BY id DESC 
LIMIT 50

but a couple of days back, it started giving an error:

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

I am calling it from a PHP page but I have also tried it from MySQL's GUI to run it as SQL query. It gave the the same error:

enter image description here

When I googled, I found that it may be due to MySQL to MySQLi update. I tried a lot to write an alternate query using MySQLi, but every time I got an error (Some times the same error and some times other errors).

My questions are:

1. What this can be called as: Sub Query or Multi Query

2. What is the exact error and what is its solution?

4
  • 1
    Can you duplicate the using the mysql CLI? That's kind of the gold standard for "correct" behavior and is going to be immune from any mysqli issues, which phpmyadmin might not be. Feb 22, 2016 at 11:28
  • You have a "subquery".
    – Rick James
    Feb 22, 2016 at 15:54
  • What version of phpmyadmin are you running?
    – Rick James
    Feb 22, 2016 at 15:55
  • phpMyAdmin - Version information: 4.0.10.7
    – SJSSoft
    Feb 22, 2016 at 16:15

3 Answers 3

2

I think this Stack Overflow question is related to your query:

PHP Commands Out of Sync error

It's because results are not completely fetched before another call. PHP docs has a solution read this too:

http://www.php.net/manual/en/mysqli.query.php#102904

3
  • This is usually the cause... but for this relatively straightforward query to be broken when run in pma seems a little unexpected. Feb 22, 2016 at 11:25
  • Can you please help me to write down the right query, I am messed up with it for 2 days.
    – SJSSoft
    Feb 22, 2016 at 16:23
  • Putting $conn->next_result(); before update query solved the problem.
    – aiternal
    Jul 3, 2020 at 10:26
1

For anyone who might still be interested, I ran into this problem myself where a table started giving this error out of the blue. Running CHECK on the table showed it had errors and had crashed. Running REPAIR on the table fix the errors and I stopped getting the "Commands out of Sync" error on my own query.

1

Another possibility is some failure of mysql server to deliver the result, but not severing the connection before the next query is run.

Came across this with mysql server 8.0. The error log showed an out of memory error for the sort buffer size. After increasing that, these errors in the application went away.

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.