Trouble Creating a MySQL Function through PDO

Question

I've created a CLI PHP script designed to allow me to run update scripts against my database. The script works as expected, runs sequential update files, rolls back on errors, etc. However, the only thing I can't get it to do properly is create functions. What am I missing?

---

Script

Here's the basics of how the script works:

$sql = file_get_contents($file);
$sql = "USE `$database`;" . $sql;
$stmt = $pdo->prepare($sql);

try
{
    $stmt->execute();

    if($stmt->errorCode() !== '00000')
    {
        throw new Exception("Error: Unable to run update $file\n" . print_r($stmt->errorInfo(), true));
    }
}
catch(Exception $ex)
{
    $pdo->rollBack();
    echo "Error: Exception occured running update $file\n" . print_r($ex, true) . "\n";
    throw $ex;
}

---

SQL

And here's an example of the Function portions of the SQL code:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER FUNCTION `uuid_from_bin`(b BINARY(16)) RETURNS char(36) CHARSET latin1
    DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);
  SET hex = HEX(b);
  RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
RETURN 1;
END ;;

If I run this code inside MySQL Workbench it runs as expected. When I run it through my script using PDO everything except the functions are created and populated. I get no errors and no exceptions.

---

So Far...

I've tried removing the definer, thinking that the user that was "CURRENT_USER" might be 'user@randomip' and the user in the system is defined as 'user@%'.

I also just removed the definer from the SQL. Still works in MySQL Workbench, but not in this script.

I've also pulled the Functions out into their own SQL, same result.

---

Other Info

MySQL 5.5.37-0ubuntu0.14.04.1

PHP 5.5

When I connect with PDO I'm not setting the DB, since the script may need to create the database. But the scripts are appended with a USE statement. Again all the other DDL statements work.

The user has all but Grant permissions on schema based on a pattern, specifically ci_%

---

Solution, Update DELIMITER

Updated the code to remove the DELIMITER

#DELIMITER USED TO BE HERE
CREATE DEFINER=CURRENT_USER FUNCTION `uuid_from_bin`(b BINARY(16)) RETURNS char(36) CHARSET latin1
    DETERMINISTIC
BEGIN
  DECLARE hex CHAR(32);
  SET hex = HEX(b);
  RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
RETURN 1;
END;

Answer 1

You don't need to use DELIMITER when you submit a statement through an API like PDO.

In fact, you must not use DELIMITER, because it's not recognized by the server's SQL parser. It's only recognized in the mysql client.