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
Original issue reported on code.google.com by
thomas.w...@googlemail.com
on 2 Sep 2010 at 8:59