Mysql – Commands out of sync; you can’t run this command now

MySQLmysqlisubquery

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:

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?

Best Answer

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

Related Solutions

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

If iBTID is an auto_increment column in the export1 table, you may want to change the INSERT INTO command to exclude that column as follows:

INSERT INTO export1 (timeStampID ,ipSource ,macSource,portSource,totalBits, packetCount) VALUES ('2012-07-16 23:51:45','10.207.160.116','00:14:5E:95:B9:18','53',995,7)

Every time you INSERT and hand mysqld a row where you set an auto_increment column to NULL, mysqld tries to reconcile the INSERT's Primary Key by finding the next auto_increment value and handling it internally. That's could be a little taxing on mysqld because handing a PRIMARY KEY (which is all defined as NOT NULL) a NULL value forces this reconciliation exercise. That is really unnatural in terms of ANSI SQL.

MySQL Error #2014 – Commands Out of Sync, Troubleshooting Guide

I don't think the problem is with your query, but rather with your data. Check out the error message

It ssays : #2014 - Commands out of sync; you can't run this command now and 1690 BIGINT UNSIGNED value is out of range in '(bt.end_val-bt.start_val)'

I think for some reason, now (bad data entry?), your query is trying to insert a negative value into an UNSIGNED INT which is not allowed.

[EDIT]

I found it strange that you couldn't subract one integer from another so I tested with a sample data set.

mysql> CREATE TABLE `test1` (
    ->   `start_val` int(10) unsigned NOT NULL,
    ->   `end_val` int(10) unsigned NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.50 sec)

mysql> insert into test1 values(3, 4);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1 values(4, 3);
Query OK, 1 row affected (0.05 sec)

So, two simple records in a simple table.

Then I ran this very simple query:

mysql> select end_val - start_val from test1;

and INCREDIBLY I got the result:

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test1`.`end_val` - `test`.`test1`.`start_val`)

I investigated and found that there is a setting

NO_UNSIGNED_SUBTRACTION

By default, subtraction between integer operands produces an UNSIGNED result if any operand isUNSIGNED.

This has to be switched off if you with to subtract UNSIGNED INT values. I consider this to be a bug in MySQL - all the more egregious since there are no CHECK constraints in MySQL - but one could have plenty of reasons to wish to subtract two positve integers and obtain a negative result (debts, whatnot), but you can't do that in MySQL. Absolutely incredible! Unsigned INTs should not be used for anything but PRIMARY KEYs.

Best Answer

Related Solutions

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

MySQL Error #2014 – Commands Out of Sync, Troubleshooting Guide

Related Question