MySQL CharSet cast support

[flyway]

Original author: garich...@handshake.de (March 14, 2013 14:39:26)

Hi,

I get an error (see below) if I try to insert a char-row (mysql) with flyway (V2.1) migrate …
but if I do the same insert with flyway V1.6.1 it works perfectly…

command line output of flyway 2.1:

INSERT INTO bonlayout (vertriebslinie, lang, position, layout) VALUES ('CH01RE', 'en', 'EC_BLZ_1_0', '<RIGHT>Bank code: ___________________________</RIGHT>');
INSERT INTO bonlayout (vertriebslinie, lang, position, layout) VALUES ('CH01RE', 'en', 'EC_KNR_1_0', '<RIGHT>Account #: ___________________________</RIGHT>')
ERROR: Caused by 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 'INSERT INTO Farbpalette (Farbcode, Farbe) VALUES (0, '_undefiniert');
INSE' at line 38
ERROR: FlywayException: Migration of schema kasse to version 4.0.7 failed! Please restore backups and roll back database and code!
ERROR: Occured in com.googlecode.flyway.core.command.DbMigrate.migrate() at line 194

Original issue: http://code.google.com/p/flyway/issues/detail?id=451

[flyway]

From axel.fontaine.business@gmail.com on March 14, 2013 18:55:24
Thanks for reporting this!

Could you try again with 2.1.1 (should be available shortly on Maven Central) and let me know if it is fixed or not?

Thanks
Axel

[flyway]

From garich...@handshake.de on March 15, 2013 09:00:23
I tried the new Version 2.1.1 …but same error here

INSERT INTO bonlayout (vertriebslinie, lang, position, layout) VALUES ('AT01RE', 'en', 'EC_BLZ_1_0', 'Bank code: ___________________________');
INSERT INTO bonlayout (vertriebslinie, lang, position, layout) VALUES ('AT01RE', 'en', 'EC_KNR_1_0', 'Account #: ___________________________')
ERROR: Caused by com.mysql.jdbc.exceptions.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 'INSERT INTO Farbpalette (Farbcode, Farbe) VALUES (0, '_undefiniert');
INSE' at line 38
ERROR: FlywayException: Migration of schema kasse to version 4.0.7 failed! Please restore backups and roll back database and code!
ERROR: Occured in com.googlecode.flyway.core.command.DbMigrate.migrate() at line 201

[flyway]

From axel.fontaine.business@gmail.com on March 15, 2013 21:38:30
I tried to reproduce this, but couldn't get it to fail. :-/

Could you post a small script that triggers the error?

Thanks
Axel

[flyway]

From garich...@handshake.de on March 19, 2013 16:12:36
you are right…

perhaps it's the size of the file …

can I send my error file to your email address?

378K V4_0_7__opullenter_ochse.sql.bz2

Gregor

[flyway]

From axel.fontaine.business@gmail.com on March 20, 2013 09:25:43
Hi Gregor,

Please try to isolate the couple of statements that trigger the issue. I doubt it is the file size, as Flyway is being used by some people with files larger than 1 GB.

Cheers
Axel

[flyway]

From garich...@handshake.de on March 21, 2013 15:12:44
Hi Alex,

I reduced the file to the above mentioned (see attachment) SQL statements but no error…
so…
it has to be something other than '#' but I keep searching

…could take some time

Gregor

[flyway]

From axel.fontaine.business@gmail.com on March 21, 2013 20:16:16
OK, thanks! Let me know when you managed to isolate it.

Cheers
Axel

P.S.: Please post your original file as well, just in case.

[flyway]

From axel.fontaine.business@gmail.com on April 10, 2013 18:16:27
Hi Gregor,

any news?

Cheers
Axel

[flyway]

From garich...@handshake.de on April 11, 2013 07:49:29
Hi Axel,

I didn't have time so far…

…but I really do it on this Friday

Gregor

[flyway]

From garich...@handshake.de on April 12, 2013 15:33:00
Hi Axel,

I couldn't pinpoint the error spending line(s)…

…so I upload the whole thing

Gregor

[garichner]

+1

[bigslack]

I believe the problem with this file is the following statement:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `ean_artikelstamm_view`
AS 
    select `artikelstamm`.`Artikelnummer` AS `Artikelnummer`,`ean`.`EAN` AS `EAN`,`artikelstamm`.`Artikel` AS `Artikel`,`artikelstamm`.`Farbe` AS `Farbe`,`artikelstamm`.`Groesse` AS `Groesse`,`Warengruppe`.`Warengruppe` AS `Warengruppe`,`Warengruppe`.`Artikelgruppe` AS `Artikelgruppe`,`artikelstamm`.`Marke` AS `Marke`,`artikelstamm`.`Saison` AS `Saison`,`artikelstamm`.`Echtgroesse` AS `Echtgroesse`,`artikelstamm`.`Preis` AS `Preis`,`artikelstamm`.`Wert` AS `Wert`,`artikelstamm`.`Erstellungsdatum` AS `Erstellungsdatum`,`artikelstamm`.`Aktiv` AS `Aktiv`,`artikelstamm`.`date_time` AS `date_time`,`artikelstamm`.`Artikelkennzeichen` AS `Artikelkennzeichen`
    from ((`artikelstamm` left join `Warengruppe` on((`artikelstamm`.`Warengruppe` = `Warengruppe`.`Warengruppe`))) join `ean` on((`artikelstamm`.`Artikelnummer` = `ean`.`Artikelnummer`)))
    where ((`Warengruppe`.`lang_id` = _utf8'de') or isnull(`Warengruppe`.`lang_id`));

In particular, this segment: Warengruppe.lang_id = _utf8'de'. What is happening is the token _utf8'de'is creating only oneQUOTE` delimiter. This is because the starting quote is prefixed with the char encoding _utf8. Flyway doesn't realize this and can't properly handle it.

Here is a simple script that produces the same issue:

DROP TABLE IF EXISTS bug_test;
CREATE TABLE bug_test (
  id varchar(36) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



insert into bug_test (id) values (_latin1'hello');

--comment here is incorporated into above statement because it isn't terminated

[axelfontaine]

Fixed thanks to Adam's pull request.