Problem running migration scripts that contain multiple 'CREATE FUNCTION' statements

[GoogleCodeExporter]

Hi,

I have a problem when running a migration script that contains multiple 'CREATE 
FUNCTION' statements. For a detailed description on what happens and on how to 
reproduce the problem please see below.

Best regards,
Thomas

What steps will reproduce the problem?
1. Create a migration script that contains multiple 'CREATE FUNCTION' 
statements. E.g:

DROP FUNCTION IF EXISTS function1;

DELIMITER |

CREATE FUNCTION function1(param1 VARCHAR(30)) RETURNS VARCHAR(30)
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE result VARCHAR(30);
    SET result = param1;
    RETURN result;
END;

|

DELIMITER ;

DROP FUNCTION IF EXISTS function2;

DELIMITER |

CREATE FUNCTION function2(param1 VARCHAR(30)) RETURNS VARCHAR(30)
    DETERMINISTIC    
    SQL SECURITY INVOKER
BEGIN
    DECLARE result VARCHAR(30);
    SET result = param1;
    RETURN result;
END;

|

DELIMITER ;

2. Execute the script via the mysql command line and verify that it can be 
executed without any problem.
3. Execute the script via the c5-db-migration framework.

What is the expected output? What do you see instead?
The expected output is that the script can be executed without any problem and 
that both functions 'function1' and 'function2' are created.
The output instead is:

10:50:51,230        DEBUG ScriptRunner:143 - CREATE FUNCTION function1(param1 
VARCHAR(30)) RETURNS VARCHAR(30) DETERMINISTIC SQL SECURITY INVOKER BEGIN 
DECLARE result VARCHAR(30); SET result = param1; RETURN result; END; DROP 
FUNCTION IF EXISTS function2
10:50:51,240        ERROR ScriptRunner:128 - Error executing: CREATE FUNCTION 
function1(param1 VARCHAR(30)) RETURNS VARCHAR(30) DETERMINISTIC SQL SECURITY 
INVOKER BEGIN DECLARE result VARCHAR(30); SET result = param1; RETURN result; 
END; DROP FUNCTION IF EXISTS function2
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in 
your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'DROP FUNCTION IF EXISTS function2' at line 1

What version of the product are you using? On what operating system?

I am using version 0.9.9-m4 of the migration framework running on Ubuntu 9.10

Please provide any additional information below.

As far as I have seen from debugging the problem is that in the class 
'ScriptRunner' in line 100 the delimiter is reset to the MySQL default 
delimiter. In line 103 it is checked whether the current line ends with the 
delimiter character and thus the command should be executed. But since the 
delimiter has been reset to the default the if statement in line 103 does not 
evaluate to true and thus the command is not executed but the next line is read 
resulting in a command that does not contain one statement but two which 
results in a MySQL Syntax Exception.

Original issue reported on code.google.com by thomas.w...@googlemail.com on 2 Sep 2010 at 8:59