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

MySQLperformancequery-performance

My table structure is … There is Counter which has many Box in it. Box has a one BoxType. and Collection stores Box datewise revenue and data. I need data of particular 1 COUNTER (Data of All the box belongs to it.) .

counter table

Counter Table Data structure

box table

Box atble data structure

box_type table
box_type table data structure

Collection table
collection table data structure

The query returns data on some data.
Query returns data on previous db with same structure less data.

Query not working on new inserted data. 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)'

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

Here is my query… was working before now with new data having problem

SELECT `b`.`box_id`,
       `bt`.`box_type_id`,
       `bt`.`price`,
       `bt`.`start_val`,
       `bt`.`end_val`,
       ((`bt`.`end_val` - `bt`.`start_val`) + 1) AS totalTickets,
       (CASE
           WHEN (SELECT SUM(`co1`.`sold_tickets`)
                   FROM `collections` `co1`
                        INNER JOIN `boxes` `b1`
                           ON     `b1`.`box_id` = `co1`.`box_id`
                              AND `b1`.`status` = "1"
                        INNER JOIN `counters` `c1`
                           ON     `c1`.`counter_id` = `b1`.`counter_id`
                              AND `c1`.`status` = "1"
                        INNER JOIN `box_types` `bt1`
                           ON     `bt1`.`box_type_id` = `b1`.`box_type_id`
                              AND `bt1`.`status` = "1"
                  WHERE `co1`.`status` = "1" AND `b1`.`box_id` = `b`.`box_id`)
                   IS NULL
           THEN
              0
           ELSE
              (SELECT SUM(`co1`.`sold_tickets`)
                 FROM `collections` `co1`
                      INNER JOIN `boxes` `b1`
                         ON     `b1`.`box_id` = `co1`.`box_id`
                            AND `b1`.`status` = "1"
                      INNER JOIN `counters` `c1`
                         ON     `c1`.`counter_id` = `b1`.`counter_id`
                            AND `c1`.`status` = "1"
                      INNER JOIN `box_types` `bt1`
                         ON     `bt1`.`box_type_id` = `b1`.`box_type_id`
                            AND `bt1`.`status` = "1"
                WHERE `co1`.`status` = "1" AND `b1`.`box_id` = `b`.`box_id`)
        END)
          AS soldTickets,
       (  ((`bt`.`end_val` - `bt`.`start_val`) + 1)
        - (CASE
              WHEN (SELECT SUM(`co1`.`sold_tickets`)
                      FROM `collections` `co1`
                           INNER JOIN `boxes` `b1`
                              ON     `b1`.`box_id` = `co1`.`box_id`
                                 AND `b1`.`status` = "1"
                           INNER JOIN `counters` `c1`
                              ON     `c1`.`counter_id` = `b1`.`counter_id`
                                 AND `c1`.`status` = "1"
                           INNER JOIN `box_types` `bt1`
                              ON     `bt1`.`box_type_id` = `b1`.`box_type_id`
                                 AND `bt1`.`status` = "1"
                     WHERE     `co1`.`status` = "1"
                           AND `b1`.`box_id` = `b`.`box_id`)
                      IS NULL
              THEN
                 0
              ELSE
                 (SELECT SUM(`co1`.`sold_tickets`)
                    FROM `collections` `co1`
                         INNER JOIN `boxes` `b1`
                            ON     `b1`.`box_id` = `co1`.`box_id`
                               AND `b1`.`status` = "1"
                         INNER JOIN `counters` `c1`
                            ON     `c1`.`counter_id` = `b1`.`counter_id`
                               AND `c1`.`status` = "1"
                         INNER JOIN `box_types` `bt1`
                            ON     `bt1`.`box_type_id` = `b1`.`box_type_id`
                               AND `bt1`.`status` = "1"
                   WHERE     `co1`.`status` = "1"
                         AND `b1`.`box_id` = `b`.`box_id`)
           END))
          AS remainingTickets
  FROM `collections` `co`
       INNER JOIN `boxes` `b`
          ON `b`.`box_id` = `co`.`box_id` AND `b`.`status` = "1"
       INNER JOIN `counters` `c`
          ON `c`.`counter_id` = `b`.`counter_id` AND `c`.`status` = "1"
       INNER JOIN `box_types` `bt`
          ON `bt`.`box_type_id` = `b`.`box_type_id` AND `bt`.`status` = "1"
 WHERE `co`.`status` = "1" AND `b`.`counter_id` = "1"
GROUP BY `b`.`box_id`
ORDER BY `b`.`box_id` ASC

How do i improve this query for consistent performance and is there a better way of doing this thing.

Best Answer

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.

Related Question