6

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

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
foo
  • 1,968
  • 1
  • 23
  • 35
  • Try remove the use 'dbname'. Also try running if using the mysqli_query command. – Knubo Jan 01 '11 at 21:38
  • I did remove that USE statement, no effect. mysqli_query fails, too - and in this case it is obvious why: the string contains multiple statements, so if it *were* performing correctly, it would return multiple results - which mysqli_query does not handle. - for the other scripts, mysqli_multi_query works perfectly fine, returning one result row for each statement. (empty rows on success, Note: 1051 lines as reaction to SHOW WARNINGS; - all as expected). – foo Jan 01 '11 at 21:51
  • I'm getting closer - no solution, but a diagnosis. DELIMITER seems to be implemented client-side (!). – foo Jan 01 '11 at 23:38
  • Are you specifying the queries in double-quoted strings? PHP could be eating the `$$` as a variable variable, perhaps. – Marc B Jan 02 '11 at 03:51
  • No, they are stored in strings which I read from files directly, and I checked for $$ replacement. – foo Jan 03 '11 at 09:36

3 Answers3

7

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

Xavier Barbosa
  • 3,919
  • 1
  • 20
  • 18
  • 2
    That works, sort of. As I wrote before, DELIMITER seems to be implemented client-side, and the PHP mysql(i) interface seems to be missing some parts the mySQL clients implements - like DELIMITER. It does not seem to implement DELIMITER at all. So sending the procedures separately and without DELIMITER works. However, I don't think hand-rewriting the routines again and again is a viable solution. It's quite a number of routines, and there will be more in the future. I need something that works *with* the tools I'm given, not agains. But thanks for the idea, this may help others. – foo Jan 19 '11 at 23:42
  • 1
    Thank you, I was having the same problem, trying to create, and execute a stored procedure from mysqli_multi_query and it worked from HeidiSQL however when I executed the same code from PHP it threw an error. I removed my DELIMETER statements, and any line breaks and it worked perfectly. THANKS! :D – Jared Feb 09 '14 at 19:26
  • Question: How to check if storedProcedure exists already, from PHP? – Mike Warren Nov 08 '14 at 07:46
  • 1
    @MikeWarren: SHOW PROCEDURE STATUS gives a list of stored procedures. – olekeh Nov 12 '17 at 15:57
1

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.

foo
  • 1,968
  • 1
  • 23
  • 35
-3

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

Gregory Magarshak
  • 1,883
  • 2
  • 25
  • 35