aurora-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joshua Cohen <jco...@apache.org>
Subject Re: Handling migrations if dbScript exists in snapshot?
Date Wed, 30 Mar 2016 03:45:08 GMT
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 <jcohen@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 <maxim@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 <jcohen@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 <maxim@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.pfeiffer@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 <jcohen@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 <jsirois@apache.org>
>> > > wrote:
>> > > >>
>> > > >> > On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen <jcohen@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.pfeiffer@gutefrage.net
>> > > > +49-89-515146-173
>> > > >
>> > > > gutefrage.net GmbH
>> > > > Ein Unternehmen der Verlagsgruppe Georg von Holtzbrinck
>> > > > Erika-Mann-Str 23
>> > > > 80636 München
>> > >
>> >
>>
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message