Sorry, I should've been more clear above. What I meant to say is that the lack of explicit rollback support was not a mark against Flyway since we'd have to do similar work to accomplish whether we did it w/ Flyway or whether we rolled our own.
I'm currently experimenting w/ Mybatis migrations which has native support for downgrades as well as upgrades. The main caveat there is that we'd have to use a snapshot version of the jar since the one published to Maven Central relies on an incompatible version of the core mybatis jar. On Thu, Mar 24, 2016 at 10:53 AM, Maxim Khutornenko <ma...@apache.org> wrote: > > > > I think in the event that we have to rollback after applying a change > like > > you describe we'd either have to resort to a manual fix or restore from > > backup. > > I am afraid restoring from backup will not be an acceptable solution > if/when a new version has to be rolled back after running for awhile. > > The other thing to keep in mind is that this problem is not solved > > by rolling our own migration solution either. > > I don't see why we can't move data back the same way we moved it forward. > It takes another script to drop new objects, re-create old and migrate > data. What this may not help with is restoring data that has been dropped > completely from the DB but I'd argue that should be a non-issue as nothing > in the scheduler should rely on that data by the time it's dropped. > > How about having two flyway instances to support forward and reverse > migrations? The flyway seems to support it: > https://flywaydb.org/documentation/faq#multiple-schemas. We could have a > SQL or a Java 'afterMigrate' callback (both supported by flyway) to > establish a new baseline upon forward migration that may be used by a > reverse flyway instance to apply rollback scripts in an event of a version > downgrade. > > I feel this is an important problem that we need to have full clarity > around before moving forward with any schema mods. Happy to partake in any > related changes. > > On Thu, Mar 24, 2016 at 7:25 AM, Joshua Cohen <jco...@apache.org> wrote: > > > Flyway has a fairly well reasoned response to the downgrades question: > > https://flywaydb.org/documentation/faq#downgrade > > > > I think in the event that we have to rollback after applying a change > like > > you describe we'd either have to resort to a manual fix or restore from > > backup. The other thing to keep in mind is that this problem is not > solved > > by rolling our own migration solution either. > > > > Another option that may be worth considering is mybatis migrations: > > http://www.mybatis.org/migrations/runtime-migration.html. > > > > On Wed, Mar 23, 2016 at 11:34 PM, Maxim Khutornenko <ma...@apache.org> > > wrote: > > > > > +1 to giving flyway a try. I have used it before and it *mostly* > > > worked. There were a few hiccups occasionally when it couldn’t > > > properly recognize the DB drift and failed to apply the update but > > > that was entirely incremental build-related problem (flyway was > > > integrated into the build process). > > > > > > What flyway does not support is version rollbacks. It's not applicable > > > to this particular change but I am sure there will be changes where > > > we'd need to move data as part of DB upgrade. If we then decide to > > > rollback due to an unforeseen problem we would have to have reverse > > > migration script(s). Not impossible but something to keep in mind. > > > > > > > > > > > > On Wed, Mar 23, 2016 at 4:41 PM, Florian Pfeiffer > > > <florian.pfeif...@gutefrage.net> wrote: > > > > We're using flyway for our services, and from an ops perspective I'm > > > quite > > > > happy with it. On test it's allowed to apply the migrations > > > > automatically... on prod we're doing it manually after reviewing. No > > > > problems so far. > > > > I can't really tell anything from the dev side, beside that I > haven't > > > > heard them complaining about it (and normally they are complaining > > quite > > > > fast ;) )... > > > > > > > > Another thing that's similar to flyway is http://www.liquibase.org/ > > but > > > for > > > > that I have zero experience. > > > > > > > > Flo > > > > > > > > > > > > On 23 March 2016 at 22:41, Joshua Cohen <jco...@apache.org> wrote: > > > > > > > >> If we go with option 2 (which I'm leaning towards as well), does > > anyone > > > >> have thoughts on using (or experience with) something like Flyway: > > > >> https://flywaydb.org/ rather than implementing from scratch? > > > >> > > > >> On Wed, Mar 23, 2016 at 3:29 PM, John Sirois <jsir...@apache.org> > > > wrote: > > > >> > > > >> > On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen <jco...@apache.org> > > > wrote: > > > >> > > > > >> > > Hi Aurorans, > > > >> > > > > > >> > > As you may have seen ( > > > >> https://issues.apache.org/jira/browse/AURORA-1648 > > > >> > ), > > > >> > > we ran into an issue when upgrading a cluster that uses > dbScripts > > in > > > >> > > snapshots. To restate the problem from the ticket, when the > > > scheduler > > > >> > > starts up it creates the H2 database from schema.sql which > > contains > > > >> newly > > > >> > > added (or changed) tables. If the dbScript exists in the > snapshot, > > > it > > > >> > then > > > >> > > drops all tables and runs the script which re-creates the > database > > > in > > > >> the > > > >> > > form it was previously in and repopulates all the data. At this > > > point, > > > >> > if a > > > >> > > table was added that was not in the snapshot, that table is > lost. > > > >> > > > > > >> > > The solution here is to be run a migration after restoring the > > > >> database, > > > >> > my > > > >> > > question is what level of complexity do we want for migrations. > I > > > see > > > >> two > > > >> > > possible options: > > > >> > > > > > >> > > > > > >> > > 1. The relatively simple approach where we have a single > > > >> migration.sql > > > >> > > alongside schema.sql which contains any changes to run > against > > > the > > > >> > > database. After restoring the db from a dbScript in the > > > snapshot, we > > > >> > > would > > > >> > > run this script. On release boundaries(?), we clear the > script > > > out. > > > >> > The > > > >> > > main caveat here is that the ddl statements in this script > must > > > be > > > >> > > idempotent since they would potentially be run multiple > times. > > > >> Another > > > >> > > issue is this would make it more complicated to jump over > > > versions > > > >> > > (upgrading from v0.12.0 to v0.14.0 means you might miss a > > > migration > > > >> > that > > > >> > > only existed in the 0.13.0 release). > > > >> > > 2. A slightly more involved approach would be to track the > > schema > > > >> > > version in the database itself. Instead of having a single > > > >> > migration.sql > > > >> > > script that contains all migrations, we could instead have > > > >> > > migrations/001.sql, migrations/002.sql, etc. We'd add a new > > > table to > > > >> > the > > > >> > > database to track which migrations have been run. After > > restoring > > > >> via > > > >> > > dbScript, we'd check the table and run any migrations > necessary > > > (or > > > >> > all > > > >> > > of > > > >> > > the table does not exist). > > > >> > > > > > >> > > Thoughts on these options? > > > >> > > > > > >> > > > > >> > I have used 2 a few times in the past and my take is it only more > > > complex > > > >> > in 1 fixed piece of (java) code that can be well unit tested. The > > sql > > > >> > scripts are easier to read and write and reason about for devs and > > > >> > operators alike. > > > >> > > > > >> > > > > >> > > > > > >> > > Anyone have any alternatives? > > > >> > > > > > >> > > Cheers, > > > >> > > > > > >> > > Joshua > > > >> > > > > > >> > > > > >> > > > > > > > > > > > > > > > > -- > > > > Head of Data & Infrastructure > > > > florian.pfeif...@gutefrage.net > > > > +49-89-515146-173 > > > > > > > > gutefrage.net GmbH > > > > Ein Unternehmen der Verlagsgruppe Georg von Holtzbrinck > > > > Erika-Mann-Str 23 > > > > 80636 München > > > > > >
