MySQL Flyway syntax error

Question

We use Flyway from 1 month without any problems.

But today I tried to add a new script migration, which is very long (over 1500 lines) and ran into an obscure MySQL syntax error.

I opened this script in MySQL Workbench and without syntax error, where it was reported, the script is executed without errors.

This script named 'V10012__insert-accept-testing-event-moment-pass.sql' contains the following instructions.

• INSERT 1 instruction
• INSERT 2 instruction
• ...
• INSERT LAST - 1 operator
• INSERT LAST statement

The error reported by MySQL is as follows:

[ERROR] caused by com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: you have an error in the SQL syntax; check the manual corresponding to MySQL Server Version for the correct syntax to use next to INSERT INTO video_feedback (id, youtube_video_id) VALUES (1102, / * id * / 'on line 232 [ERROR] com.googlecode.flyway.core.migration.MigrationException: transition to version 10012 failed! Restore the backups and roll back the database and code!

The error is reported in the "COMPLETE INSERT statement" statement.

But if I invert the INSERT LAST and INSERT LAST-1 statements in my script, then the error is reported in the INSERT LAST-1 statement (which is at the end of the file), so I don’t have an error in the INSERT LAST statement because Flyway successfully executed it .

In addition, if now I completely remove the "INSERT statement LAST" statement from the script 'V10012_insert-accept-testing-event-moment-pass.sql' and put this statement in a new file called 'V10013_test.sql' flyway successfully executes all my migration scenarios!

So, what could be the problem in my original "V10012__insert-accept-testing-event-moment-pass.sql" script?

Is script size limit possible?

Here is some helpful information about my environment:

• My scripts use a lot of / * * / comments
• Flyway Maven Plugin 1.7
• Maven 2.0.3
• MySQL: MySQL-socket-Java: 5.1.21
• MySQL 5.5.X
• Java JDK 1.7.0_09-b05
• Windows 7

Answer 1

I finally found the cause of the problem.

In my script, I had:

... ... /* INSERT statement LAST - 1 */ INSERT INTO `table_1` (`id`, `string_1`, `string_2`) VALUES ( 1, 'aaa', /* COMMENT 1*/ 'bbb' /* COMMENT 2*/ ); /* INSERT statement LAST */ INSERT INTO `table_2` (`id`, `string_3`) VALUES ( 1, /* COMMENT 3 */ 'cccc' /* COMMENT 4 */ ); 

The problems seem to be on COMMENT 2 and COMMENT 4. If I delete the flyway theme, all my migration scripts will succeed.

For example, this script will work:

 ... ... /* INSERT statement LAST - 1 */ INSERT INTO `table_1` (`id`, `string_1`, `string_2`) VALUES ( 1, 'aaa', /* COMMENT 1*/ 'bbb' ); /* INSERT statement LAST */ INSERT INTO `table_2` (`id`, `string_3`) VALUES ( 1, /* COMMENT 3 */ 'cccc' ); 

So, maybe this is a bug in the Flyway parser?

I don’t have time to test it today, but it seems like this error only occurs when:

• We have a comment after VARCHAR (or other column types that I assume)
• This comment is placed immediately before the ')' symbol

These problems seem reproducible only if several insert statements are present in the same SQL script.

In addition, I believe that comments that are not placed before the ')' character are correct, for example:

 /* INSERT statement LAST */ INSERT INTO `table_2` (`id`, `string_3`) VALUES ( 1, /* COMMENT 3 */ 'cccc', /* COMMENT 4 */ 'dddd' ); 

Here COMMENT 3 and COMMENT 4 pass in Flyway, because they are located after the character ',' and before the string value, but they do not fit before the character ')'.

Hope this can help; -)

Answer 2

This is very unlikely due to overflight. This is almost certainly due to the MySQL driver that uses flyway. With a native MySql client, you naturally assume that it can handle any legal syntax. Even “optional” comment blocks

One of the problems that I know for SURE in the jdbc driver for MySql is that it cannot handle summary delimiters on the fly. Thus, triggers and stored procedures can be a problem, especially if they were generated using mysqldump , but then you use the Java application for reuse.

if you look here and go to spring.datasource.separator , you will see that this is installed on the whole system (essentially) on ; .

Now imagine how it will behave when it comes to this line in the script:

 DELIMITER ;; CREATE FUNCTION `int2vancode`(id BIGINT(20)) RETURNS varchar(255) BEGIN DECLARE num VARCHAR(10); DECLARE length INT; SET num = conv(id, 10, 36); SET length = char_length(num); return CONCAT(conv(length -1 , 10, 36), num); END ;; DELIMITER ; 

The java driver is dumb and does not interpret any of these statements, although the DELIMITER statement is explicitly intended for the client

I ran into a problem right now when the native client runs the entire script without problems, but flyway says that I have a syntax error that even intellij does not see.

** Update **

I solved my problem and this more or less confirms the problem that I see: I had a create table:

 CREATE TABLE `Position` ( PositionID INT NOT NULL AUTO_INCREMENT, `Name` varchar(200) COLLATE utf8_unicode_ci NOT NULL ); 

I had to avoid Name and Position for the pass in order to accept it, although my bash script, which iteratively executes them, has no problem.

Perhaps I can tell the Java driver something to help him?