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

(4 posts) (2 voices)


No tags yet.

  1. labber, Member

    When running the following in the Query builder I am getting the Error "Commands out of sync; you can't run this command now" Do you see an issues with my code?

    DROP PROCEDURE IF EXISTS sp_DBR_empty_categories; DELIMITER $$ CREATE PROCEDURE sp_DBR_empty_categories() BEGIN -- make a holder table DROP TEMPORARY TABLE IF EXISTS report_category_output; -- CREATE TEMPORARY TABLE report_category_output SELECT,, category.externalId AS internal_id, updated_at FROM category;

    ALTER TABLE report_category_output ADD COLUMN count_child_cat INT(11) UNSIGNED DEFAULT 0, ADD COLUMN count_product INT(11) UNSIGNED DEFAULT 0, ADD INDEX(id) ;

    -- product counts DROP TABLE IF EXISTS product_count; CREATE TEMPORARY TABLE product_count SELECT, count(*) AS product_count FROM report_category_output INNER JOIN productCategory ON GROUP BY;

    ALTER TABLE product_count ADD INDEX(id);

    -- back update UPDATE report_category_output, product_count SET report_category_output.count_product=product_count.product_count WHERE ;

    -- category counts DROP TEMPORARY TABLE IF EXISTS cat_count ; CREATE TEMPORARY TABLE cat_count SELECT, count( AS cat_count FROM report_category_output INNER JOIN category ON INNER JOIN category c2 ON c2.parentExternalid=category.externalId GROUP BY ;

    ALTER TABLE cat_count ADD INDEX(id);

    -- back update UPDATE report_category_output, cat_count SET report_category_output.count_child_cat=cat_count.cat_count WHERE ;

    -- output SELECT id AS category_id, name, internal_id, updated_at, count_child_cat, count_product FROM report_category_output WHERE (count_child_cat=0 AND count_product=0);

    END $$ DELIMITER ; -- EOF sp_DBR_empty_categories.sql

  2. myDBR Team, Key Master

    You should never get "Commands out of sync", so that is bit weird. You could try to shorten the procedure to see where the problem comes from.

    Couple of notes about the procedure:

    - Instead of using ALTER TABLE inside the procdure, you could add the missing columns into the query or create the temporary table first and populate the table using "INSERT INTO ... SELECT".

    - Adding indexes to temporary table does not really help the performance unless you have a lot of data. The optimizer likely does a table scan even with the index if the number of rows is not huge.

    You could shorten the procedure to single query:

    SELECT AS c.category_id,, c.externalId as 'internal_id', c.updated_at, 0 as 'count_child_cat', 0 as 'count_product'
    FROM category c
    SELECT pg.categoryId
    FROM productCategory pg
    ) AND c.externalId NOT IN (
    SELECT parentExternalid
    FROM category

    myDBR Team

  3. labber, Member

    When I remove DELIMITER from DELIMITER $$ at the top and get rid of DELIMITER ; at the bottom it works fine. What I don;t understand is that's valid SQL.

  4. myDBR Team, Key Master

    OK. that explains it.

    The SQL Editor uses delimiter $$ by default with MySQL, so you do not need to set it yourself. Now that you do, it throws the MySQL off sync when commands are sent to MySQL.

    Just use:

    DROP PROCEDURE IF EXISTS sp_DBR_empty_categories;
    CREATE PROCEDURE sp_DBR_empty_categories()

    myDBR Team


You must log in to post.