Problems with mysql create procedure from doctrine raw sql

Question

I am working with an application project in Symfony2. By registration, per client, a database is created. Schemas are created by a validation service when the client logs on. The application needs some data to work, and so far, I used ORM fixtures. For a number of reasons, I now need to move from fixtures load to get more close to the database. I have created a stored procedure (mysql) which will replace all data loading. The procedure works, but I need to create this procedure in each database together with the schema. I use doctrine raw sql for this purpose but I cannot get pass a problem with the first lines of the create procedure statement. It seems it's got to do with the 'delimiter $$'.

While executing the service with the sql statement i get: "message":"An exception occurred while executing 'delimiter $$\r\n\r\nCREATE DEFINER=root@`localhost"

It seems to be because of line-breaks but I am not sure. Anyone knows of some way around this?

Answer 1

The problem comes from the fact that delimiter command can only be used when using the MySQL Command-Line Tool as discussed http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html. To fix this problem you need to remove the part where you are changing the delimiter and just use ; as your delimiter. Consider the following:

delimiter $$

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
   SET @x = 0;
   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END$$

The above will not work however the following will:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;