Search code examples
mysqlstored-proceduresservermigration

Mysql stored procedure not working anymore after server change

Ok, I have an app, which was working. IT department moved it to a new server. It still works... except for ONE thing... which uses a mysql stored procedure.

From the "user click", to the mysql.log : everything is fine. I successfully find the procedure call in it, with appropriate parameters: 10462020 Query CALL deleteUpdateOrder(468, 1) But, on the database side, nothing happens. (And there are no error message in the log file)

I checked the procedure, it indeed exists too in the new server:

CREATE DEFINER=`tice`@`%` PROCEDURE `deleteUpdateOrder`(IN `s_id` INT, IN `deleted_q_order` INT)
BEGIN
    DELETE FROM questions WHERE session_id=s_id AND q_order=deleted_q_order;
    UPDATE questions
        SET q_order = q_order-1
    WHERE q_order > deleted_q_order
    AND session_id=s_id;
END

As there was some username changes between servers, just in case I added permission to my user 'qamphi' to have permission on it (don't think it was needed with the ALL PRIVILEGES line, but just in case) :

+--------------------------------------------------------------------------------------------------------+
| Grants for qamphi@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `qamphi`@`localhost` IDENTIFIED BY PASSWORD 'RANDOM_DATA_HERE' |
| GRANT ALL PRIVILEGES ON `qamphi`.* TO `qamphi`@`localhost`                                                    |
| GRANT ALL PRIVILEGES ON `qamphi`.`qamphi` TO `qamphi`@`localhost`                                             |
| GRANT EXECUTE ON PROCEDURE `qamphi`.`deleteupdateorder` TO `qamphi`@`localhost`                               |
| GRANT EXECUTE ON PROCEDURE `qamphi`.`proposaldelete` TO `qamphi`@`localhost`                                  |
+--------------------------------------------------------------------------------------------------------+

Everything seems in place, yet it's not updated in the DB... I'm out of idea, does anyone has any clue on this ?

Thx a lot in advance.

Solution

  • Ok, while I'm not sure WHY, I was able to fix my problem.

    Instead of recreating and granting rights to the missing user (which didn"t work, meh), I edited the stored procedure changing the definer in the mysql.proc table, and it worked instantly.

    I guess it's one ugly fix, but after weeks of disfunctionning live application, it's still a relief...