CODEWITHSUNDEEP
mysqlsql
ranj1185
ranj1185

Reputation: 73

stored procedure executing but not updating the table as expected

I have this stored procedure :

DELIMITER $$
CREATE DEFINER=`old_dev_user`@`%` PROCEDURE `p_refresh_selling_table`(location_id VARCHAR(5))
BEGIN

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
ROLLBACK;
  END;

  SET sql_log_off = 1;

  START TRANSACTION;

  IF (location_id = '') THEN

    DELETE FROM  selling_table;

    INSERT INTO selling_table
                (column1,
                 column2,
                 column3,
..
..
.
)
       (SELECT DISTINCT original_table.id,
..
..
..
..

);       


ELSE

    DELETE FROM  selling_table where id=location_id;

INSERT INTO selling table
                (column1,
                 column2,
                 column3,
..
..
.
)
       (SELECT DISTINCT original_table.id,
..
..
..
..
);

 END IF;

  COMMIT;

END$$
DELIMITER ;

The issue I have is that the stored procedure executes without any errors but the results are not updated to the selling_table as expected:

mysql> CALL `site_database`.`p_refresh_selling_table`(81);
Query OK, 0 rows affected (0.01 sec)

I know the table should populate as expected because when I run the select part of the stored procedure ((SELECT DISTINCT original_table.id..) I get the output of results which then should go to the selling table.

I have also checked permissions and the user (although not the same as the definer) has ALL permissions to the database / schema which is used in the stored procedure.

Also I have duplicated this schema on a separate (local) environment and this stored procedure executes just fine, I can see the selling_table populated with the expected results.

Please point me to the correct direction as to why the selling_table is not being populated.

Thank You

Upvotes: 1

Views: 184

Answers (1)

Ike Walker
Ike Walker

Reputation: 65537

I recommend that you install common_schema and use rdebug to debug your stored procedure:

http://code.openark.org/blog/mysql/taking-common_schemas-rdebug-to-a-test-drive

Upvotes: 1

Related Questions