Re: Handling migrations if dbScript exists in snapshot?

2016-03-29 Thread Joshua Cohen 
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  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 
> 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  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 
>> > 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
>> > >  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

Re: Handling migrations if dbScript exists in snapshot?

2016-03-24 Thread Maxim Khutornenko 
>
> 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  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 
> 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
> >  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  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 
> > wrote:
> > >>
> > >> > On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen 
> > 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

Re: Handling migrations if dbScript exists in snapshot?

2016-03-24 Thread Joshua Cohen 
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 
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
>  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  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 
> wrote:
> >>
> >> > On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen 
> 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.
> >> >
> >> >
> >> >

Re: Handling migrations if dbScript exists in snapshot?

2016-03-23 Thread John Sirois 
On Wed, Mar 23, 2016 at 2:21 PM, Joshua Cohen  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
>