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

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).

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 :)

To sum it up:

DELIMITER is implemented client-side, not serverside.

If you have a good driver or API, it may take care of this. PHP mysql / mysqli, as of now, do not.

If you need to use a different delimiter (e.g. because the default delimiter appears inside scripts), you have to encode/escape your SQL yourself or break it up so you don't need to change the delimiter. No help from PHP here.

DELIMITER is something that the mysql client application actually uses. I believe that the client application is responsible for breaking up the queries that it sends to Mysql. That is what PHPMyAdmin does, for example.

Instead of spending a whole night writing a script to parse MySQL into queries, use the code I wrote. You will find it in the scriptToQueries function, here:

http://wush.net/svn/luckyapps/pie/trunk/framework/classes/Db/Mysql.php

ENJOY

EDIT: Since writing this answer I have ported the code into the free Q platform in which you can study the code: https://github.com/EGreg/Platform/blob/master/platform/classes/Db/Mysql.php#L824