Errors when PHP creates MySQL stored procedure on new server

[sKunKbad]

I've got a script that allows us to setup our database, add some testing data, and install some stored procedures. This script worked on the last couple of servers we were on. It's kind of important to not force a real human to install the stored procedures, because experience shows that confusion regarding the install can happen.

 

Details of new database server:

• Server: Localhost via UNIX socket
• Server type: MySQL
• Server version: 5.1.52-cll - MySQL Community Server (GPL)
• Protocol version: 10

Details of new server:

• cpsrvd 11.40.1.7
• Database client version: libmysql - 5.0.96
• PHP extension: mysqli
• PHP version 5.4.22

<?php
// Fake stored procedure
$sql = '
  DROP PROCEDURE IF EXISTS fake_stored_procedure;
  CREATE PROCEDURE `fake_stored_procedure`(x float,y float, z int)
  BEGIN
  // ... bunch of sql ...
  END
';

$this->mysqli->multi_query( $sql );
do
{
    $this->mysqli->next_result();
}
while( $this->mysqli->more_results() );

if( $this->mysqli->errno )
{
    die( $this->mysqli->error );
}

When going to phpMyAdmin after an install, the stored procedures have been created, but the definition and parameters are missing, as well as the definer.

 

If I install the stored procedures manually through phpMyAdmin, then everything appears normal. It seems like a PHP issue, but I get no errors during the installation.

 

Any advice or help is appreciated.

Edited December 21, 2013 by sKunKbad

[jazzman1]

You should use a CALL SQL statement to execute and create a stored procedure with php. I haven't see it in the script above.

Check out for more details at php.net.

EDIT:: found it - http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php

Edited December 21, 2013 by jazzman1

[sKunKbad]

You should use a CALL SQL statement to execute and create a stored procedure with php. I haven't see it in the script above.

Check out for more details at php.net.

EDIT:: found it - http://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php

 

I'm not trying to CALL the procedure, only CREATE it. There is no such statement when CALL and CREATE would be used at the same time, according to the link you provided. I've got some ideas of other things to try, but it is strange that this code has been working until we moved to the new server.

[jazzman1]

This works to me and I was able to create 2 stored procedures. Make sure you have a semicolon after END!

<?php

$mysqli = new mysqli("localhost", "lxc", "password", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$sql = "DROP PROCEDURE IF EXISTS fake_stored_procedure;
  CREATE PROCEDURE `fake_stored_procedure`(x float,y float, z int)
  BEGIN
  END;";

$sql .= "CREATE PROCEDURE greeting()
	BEGIN
	select current_time() AS time;
	END;";

/* execute multi query */
if ($mysqli->multi_query($sql)) {
    do
{
    $mysqli->next_result();
}
while( $mysqli->more_results() );
}

/* close connection */
$mysqli->close();

[sKunKbad]

It would appear that this is a bug in phpMyAdmin, which is what I've been using to check the stored procedure after it is installed. After running your code, the CALL to greeting() does display the time, however the stored procedure appears blank inside phpMyAdmin. I appreciate your time. I guess I'm off to the phpMyAdmin forum to figure out what is going on. Thanks again.

[jazzman1]

What do you mean by saying it appears blank? Are you able to execute a CALL statement in phpMyAdmin SQL tab?

CALL greeting

[sKunKbad]

What do you mean by saying it appears blank? Are you able to execute a CALL statement in phpMyAdmin SQL tab?

 

CALL greeting

Yes, I can execute the CALL statement through the SQL tab, but when I go to view/edit the stored procedure, even through phpMyAdmin's ROUTINES table, the definition and params are empty. If I CREATE the stored procedure in the SQL tab, then I can view/edit it, but that sort of defeats the purpose of the PHP installer. Another thing that isn't very nice is that the stored procedures don't show up in an SQL export. Something is definitely wrong. Edited December 22, 2013 by sKunKbad

[jazzman1]

To view the content of the stored procedure I'm using next sql command: SHOW CREATE PROCEDURE greeting.

I don't see a button to show the content in my mysql workbench too. Inside ROUTINES table I can create/alter/drop procedures without any problems.

Unfortunately, I'm not using phpMyAdmin to test it out.

[sKunKbad]

I learned the real reason for the odd behavior. It has to do with cpanel, the MySQL user used by PHP, and the MySQL user used by phpMyAdmin (which is the cpanel account user). If you search for "MySQL Definer", and how this effects stored procedures, then you will see that stored procedures have special privileges. The privileges are based on the Definer, which is a combination of the user name and the domain or IP that the user created the procedure with. Since the cpanel user name isn't the same as the user name used by PHP to create the procedure, the cpanel user will have no access to the procedure, which includes viewing, altering, or exporting. The reason why there is this restriction is that the procedures are stored in the MySQL `proc` table, and MySQL creates a special privilege on-the-fly when the procedure is created. The MySQL user only have access to this procedure because of the Definer. It seems to me that this is kind of a pain in the ass. I don't really understand why the stored procedure can't be attached to the actual database, but perhaps procedures need to be able to query on multiple databases. Anyways, that's the real answer.

 

To get around this issue, I have to tunnel in as the original user via SSH. So I bought SQLyog, which makes this super simple.

[jazzman1]

 

To get around this issue, I have to tunnel in as the original user via SSH. So I bought SQLyog, which makes this super simple.

 

Did you try to install and setup the free version of mysql workbench before to buy SQLyog? It works very well to me dealing with MySQL via SSH tunnel. 

[sKunKbad]

Did you try to install and setup the free version of mysql workbench before to buy SQLyog? It works very well to me dealing with MySQL via SSH tunnel.

 

No, but the business paid for it, and I liked it after checking out the trial version. I don't know about MySQL workbench, but SQLyog's schema sync is pretty awesome. That alone was worth the price. Oh, and I got 50% off because they are running a special until the end of the year. I got the Ultimate version plus an extra year of upgrades/updates for $134 USD.