How to insert/create stored procedures in mySQL from PHP?

Question

I've got a number of stored procedures made with the MySQL Workbench. They work just fine when use MySQL workbench to put them into my test DB.

Now I am preparing the DB creation scripts for deployment, and this is the only one giving me trouble. When I use the command line / mysql shell, the script works perfectly well to. It's only when I use the PHP mysql(i) interface to execute the script - it fails. Without comment.

I use the procedure creation scripts as MySQL workbench generates for me; that is, it has this pattern:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

at the start of the script, then repeating for each procedure:

DELIMITER $$
USE `dbname`$$
CREATE PROCEDURE `procname`(IN inputparameters)
BEGIN

... procedure goes here

;
END$$
DELIMITER ;

This script works fine if run from MySQL Workbench. It also runs fine if I try the same from mysql commandline. But it fails to accomplish anything when I execute it through the PHP mysqli interface ( executing it with mysqli_multi_query, which works fine for the other scripts creating and populating the DB). There is no error returned on the interface, no results (!). All I get is "false", and that's it. error code is at 0, no error message.

It's a big WTF for me, and I hope you can point me in the right direction - how can I fix this and install the procedures from PHP?

PS: root/admin access is given and verified (after all, I just created the DB with the very same connection, created users, inserted tables and so on).

Answer 1

I haven't tested, but I won't be surprised by mysqli_multi_query() expecting to have the same delimiter of each queries. Try to pack the stored procedure creation in a single query, without using the DELIMITER modifier ?

So instead of

<?php
$results = mysqli_multi(
    'DELIMITER $$
    USE `dbname`$$
    CREATE PROCEDURE `procname`(IN inputparameters)
    BEGIN
    ... procedure goes here

    ;
    END$$
    DELIMITER ;
');
?>

Just do this

<?php
$result = mysqli_query('CREATE PROCEDURE `procname`(IN inputparameters) BEGIN ...; END');

And tell us if it works :)