mysql - FlywayDB: Migration fails with "Error executing statement", but manually running migration SQL code works -

i have play framework 2.2.2 scala application , use play-flyway 1.0.3.

the following migration should run flyway:

alter table  `user_visits` add  `firstsiterequesttime` datetime not null ; update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) ; 

so add column table , programatically fill using update statement.

the first line executes fine, update statemens fails with

flywaysqlscriptexception: error executing statement @ line 2: update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) com.googlecode.flyway.core.command.flywaysqlscriptexception: error executing statement @ line 2: update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) com.googlecode.flyway.core.dbsupport.sqlscript.execute(sqlscript.java:92) com.googlecode.flyway.core.resolver.sql.sqlmigrationexecutor.execute(sqlmigrationexecutor.java:72) com.googlecode.flyway.core.command.dbmigrate$2.dointransaction(dbmigrate.java:243) com.googlecode.flyway.core.command.dbmigrate$2.dointransaction(dbmigrate.java:241) com.googlecode.flyway.core.util.jdbc.transactiontemplate.execute(transactiontemplate.java:72) 

however, when take migration sql code , run using phpmyadmin, works fine (although takes 2-5 seconds run, tables quite big).

my mysql.log looks like

                  128 query set autocommit=0                   128 query alter table  `user_visits` add  `firstsiterequesttime` datetime not null 140412  4:40:43   128 query update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) 140412  4:40:44   128 query rollback                   128 query set autocommit=1 

is flyway having kind of timeout? log file shows sends query mysql server rolls back. why? stated before, query correct. works when run manually.

edit:

after running migration, alter table statement executed , committed (the table altered), although mysql.log says rolled back. second command rolled back.

i tried splitting 2 sql commands in 2 migrations. first 1 (alter table) runs fine , second 1 fails.

edit2:

it might kind of timeout problem. making execute faster (by updating of rows)

alter table  `user_visits` add  `firstsiterequesttime` datetime not null ; update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) user_visits.id<1000; 

runs correctly, in flyway.

mysql documentation says of mysql 5.5.3, "most statements" cause implicit commit before executing after executing. seems include alter table statement (in mysql 5.5.3 , later).

notice in log shown in question, autocommit disable. sure, either use explicit commit between statements this:

alter table  `user_visits` add  `firstsiterequesttime` datetime not null ; commit ; update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) ; 

or place them in separate , contiguous migration files, this:

file v1__alter_table.sql:

alter table  `user_visits` add  `firstsiterequesttime` datetime not null ; 

file v2__update_table.sql:

update `user_visits` set `firstsiterequesttime`=(select `time` `user_siterequests` `user_siterequests`.`visit`=`user_visits`.`id` order `time` asc limit 1) ;