Problems parsing multiline insert (MySQL)

[knes1]

Flyway 2.2.1 seems to have problem detecting the end of multiline insert statment.
This migration fails:

CREATE TABLE IF NOT EXISTS `cs_idc_code` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `longDescription` varchar(255) DEFAULT NULL,
  `shortDescription` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES 
(1,'0010','Cholera due to vibrio cholerae','Cholera d/t vib cholerae'),
(2,'0011','Cholera due to vibrio cholerae el tor','Cholera d/t vib el tor'),
(3,'0019','Cholera, unspecified','Cholera NOS'),
(4,'0020','Typhoid fever','Typhoid fever'),
(5,'0021','Paratyphoid fever A','Paratyphoid fever a'),
(6,'0022','Paratyphoid fever B','Paratyphoid fever b');

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES
(7,'0023','Paratyphoid fever C','Paratyphoid fever c'),
(8,'0029','Paratyphoid fever, unspecified','Paratyphoid fever NOS');

Migration seems to fail because FlyWay seems to merge two statements together (it didn't correctly detect the end of the first INSERT statement).

Here's the FlyWayException's error message:

Error executing statement at line 9: INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES 
(1,'0010','Cholera due to vibrio cholerae','Cholera d/t vib cholerae'),
(2,'0011','Cholera due to vibrio cholerae el tor','Cholera d/t vib el tor'),
(3,'0019','Cholera, unspecified','Cholera NOS'),
(4,'0020','Typhoid fever','Typhoid fever'),
(5,'0021','Paratyphoid fever A','Paratyphoid fever a'),
(6,'0022','Paratyphoid fever B','Paratyphoid fever b');

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES
(7,'0023','Paratyphoid fever C','Paratyphoid fever c'),
(8,'0029','Paratyphoid fever, unspecified','Paratyphoid fever NOS');

I noticed this after I upgraded from version 2.0.3 to version 2.2.1. It was not happening in 2.0.3.

[knes1]

Here's unit test that demonstrates the bug:
SqlScriptTest.java

package com.googlecode.flyway.core.dbsupport;

import com.googlecode.flyway.core.dbsupport.SqlScript;
import com.googlecode.flyway.core.dbsupport.mysql.MySQLDbSupport;
import org.junit.Assert;
import org.junit.Test;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class SqlScriptTest  {

    @Test
    public void testLinesToStatements() throws IOException {
        Connection conn = null;
        SqlScript sqlScript = new SqlScript(new MySQLDbSupport(conn));
        List<String> lines = readLinesFromResource("multiline_insert.sql");
        List< SqlStatement > statements = sqlScript.linesToStatements(lines);
        Assert.assertEquals(3, statements.size());
    }

    private List<String> readLinesFromResource(String resourceName) throws IOException {
        InputStream is = getClass().getResourceAsStream(resourceName);
        BufferedReader br = new BufferedReader(new InputStreamReader(is));
        List<String> result = new ArrayList<String>();
        String line = null;
        while((line = br.readLine()) != null) {
            result.add(line);
        }
        return result;

    }
}

multiline_insert.sql

CREATE TABLE IF NOT EXISTS `cs_idc_code` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(10) DEFAULT NULL,
  `longDescription` varchar(255) DEFAULT NULL,
  `shortDescription` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES
(1,'0010','Cholera due to vibrio cholerae','Cholera d/t vib cholerae'),
(2,'0011','Cholera due to vibrio cholerae el tor','Cholera d/t vib el tor'),
(3,'0019','Cholera, unspecified','Cholera NOS'),
(4,'0020','Typhoid fever','Typhoid fever'),
(5,'0021','Paratyphoid fever A','Paratyphoid fever a'),
(6,'0022','Paratyphoid fever B','Paratyphoid fever b');

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES
(7,'0023','Paratyphoid fever C','Paratyphoid fever c'),
(8,'0029','Paratyphoid fever, unspecified','Paratyphoid fever NOS');

[knes1]

Ok, tracked down the bug to the SqlStatmentBuilder.
Strange thing is that SqlStatmentBuilder will incorrectly detect that a statement is terminated only in second case (see test below) and difference between 2 statements is only in one letter.

This will work - statement end is detected correctly:

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES 
(6,'0022','Paratyphoid fever A','Paratyphoid fever a');

This will not work - sql bulider thinks that this statement is not terminated:

INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES 
(6,'0022','Paratyphoid fever A','Paratyphoid fever b');

Here's the unit test method:

    @Test
    public void testIsTerminated() {
        SqlStatementBuilder s = new MySQLDbSupport(null).createSqlStatementBuilder();
        s.addLine("INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES ");
        s.addLine("(6,'0022','Paratyphoid fever A','Paratyphoid fever a');");
        Assert.assertTrue(s.isTerminated()); //this passes ok

        s = new MySQLDbSupport(null).createSqlStatementBuilder();
        s.addLine("INSERT INTO `cs_idc_code` (`id`, `code`, `longDescription`, `shortDescription`) VALUES ");
        s.addLine("(6,'0022','Paratyphoid fever A','Paratyphoid fever b');");
        //this fails and the only thing that is different is 'b' instead of 'a' at the end of statement
        Assert.assertTrue(s.isTerminated());
    }

[knes1]

Ok. I think this happens because SqlStatementBuilder incorrectly recognizes b' as a start of Bit-Field literal in string 'Paratyphoid fever b'.

Method extractStringLiteralDelimitingTokens for line (6,'0022','Paratyphoid fever A','Paratyphoid fever b'); returns
[QUOTE, QUOTE, QUOTE, ALTERNATE_QUOTE] instead of [QUOTE, QUOTE, QUOTE, QUOTE].

[bigslack]

Knes1, you are correct that it is the b' that is causing the problem. Flyway is confused and thinks that you are trying to do a bitfield literal. I have already submitted a pull request for the fix which was accepted and is now on master. If you build from master HEAD you wont have the issue anymore.

[Patrick1701]

Hi bigslack,
we have the same problem with b, but also for single line inserts.

kind regards
Patrick

[bigslack]

Commit * SHA: eeadacb resolves the issue. Current flyway/master has merged the commit so the next point release will have the fix.

[axelfontaine]

Duplicate of #593