I sent out a review for a prototype of my proposed migration system here: https://reviews.apache.org/r/45467/
Please take a look at the review and let me know if it seems reasonable at a high level (noting the caveats called out in the review description, this is not production quality code, etc.). The biggest thing I'd like to call out that makes me a bit wary is that it uses MyBatis Migrations and, as I mentioned right now that means we have to consume a -SNAPSHOT jar, as for some reason they have not published a new version to Maven Central since 2014 (even though the project is active on github.) I opened https://github.com/mybatis/migrations/issues/38 to see about addressing that. On Thu, Mar 24, 2016 at 11:00 AM, Joshua Cohen <jco...@apache.org> wrote: > 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 >> > > >> > >> > >
