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.id, category.name, 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 report_category_output.id, count(*) AS product_count FROM report_category_output INNER JOIN productCategory ON report_category_output.id=productCategory.categoryId GROUP BY report_category_output.id;

   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 report_category_output.id=product_count.id ;

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

   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 report_category_output.id=cat_count.id ;

   -- 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 c.id AS c.category_id, c.name, c.externalId as 'internal_id', c.updated_at, 0 as 'count_child_cat', 0 as 'count_product'
   FROM category c
   WHERE c.id NOT IN (
   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()
   BEGIN
   ...
   END
   $$

   --
   myDBR Team

Reply

You must log in to post.