Re: Running Migrations Simulataneously

2016-04-27 Thread Amit Saha 
On Wed, Apr 27, 2016 at 11:31 PM, Mike Bayer  wrote:
> Yes, you can implement that inside your env.py if you wanted.All the
> other processes would wait and once inside the lock they all need to
> re-check the version.Not something alembic would include, though.

Have you thought about doing it in alembic and not done it because
that may not be the best idea? Just trying to understand.





>
>
> On Tuesday, April 26, 2016, Amit Saha  wrote:
>>
>> On Wed, Apr 27, 2016 at 12:37 AM, Mike Bayer 
>> wrote:
>> >
>> >
>> > On 04/26/2016 01:22 AM, Amit Saha wrote:
>> >>
>> >> On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer 
>> >> wrote:
>> >>>
>> >>>
>> >>>
>> >>> On 04/25/2016 08:30 PM, Amit Saha wrote:
>> 
>> 
>>  Hi all,
>> 
>>  In my scenario, DB migrations (via alembic) will be run as part of
>>  the
>>  app deployment and multiple app deployments will happen near
>>  simultaneously (multiple EC2 instances talking to the same MySQL DB).
>>  Let's, for simplicity's sake assume two instances:
>> 
>>  Instance #1: Alembic sees alembic_version table and finds that we do
>>  need the migrations to happen, and starts the migration.
>> 
>>  Instance #2: Alembic sees alembic_version table and finds that we do
>>  need the migrations to happen, and starts the migration.  This
>>  doesn't
>>  obviously happen since MySQL will lock the table during the
>>  migration.
>> >>>
>> >>>
>> >>>
>> >>> this scenario is entirely unsupported.   MySQL does not support
>> >>> transactional DDL and migration #1 will be hitting some tables and
>> >>> migration
>> >>> #2 another.The version table is not updated until after a
>> >>> particular
>> >>> migration takes place so concurrent processes will both be doing the
>> >>> same
>> >>> migration at the same time.
>> >>>
>> 
>>  Now, let's say instance #1 finishes the migration first and updates
>>  the
>>  alembic_version table. Then, the migration triggered by instance #2
>>  starts to run, and errors out because the schema changes are already
>>  in
>>  place.
>> 
>>  Is this what will happen or does alembic get a read/write lock on
>>  alembic_version table for the entire migration?
>> >>>
>> >>>
>> >>>
>> >>> there are no "read/write locks" on the alembic_version table.  What
>> >>> you're
>> >>> doing will not work at all.   Only one process should be running
>> >>> migrations
>> >>> against a single target database at a time.
>> >>
>> >>
>> >> Thanks for the reply, Mike. I am wondering what are my options -
>> >> should I just have to ensure that I don't run migrations in more than
>> >> one process? Which would mean, I cannot really use alembic for my DB
>> >> migrations (I wonder how people would usually do it).
>> >
>> >
>> > How this is usually done is that the decision to "migrate" is initiated
>> > manually.  I'm guessing this is a web application that wants to just
>> > automigrate when it starts.Usually what apps like that do is check
>> > that
>> > the DB is not up to date, and present a web form with a button to
>> > "upgrade"
>> > - so the user pushes the button only once.Or, if this is some kind
>> > of
>> > service that spawns multiple processes, the lead process does the check
>> > against the table and does the upgrade.
>> >
>> > The thing about schema upgrades is that they require code changes to
>> > have
>> > any upgrades to apply.  So therefore, you're starting *something* to
>> > first
>> > run that new code; that's where the migration step should happen, before
>> > everything else starts up.
>>
>> Thanks again Mike. So, I think the manual separate step to change the
>> schema has to be in there.
>>
>> For my curiosity's sake (and please bear with my noobishness), what if
>> alembic had a lock (provided the RDMS allows) for the entire time:
>>
>> 1. Get RW lock on alembic_version
>> 2. Check if we need a migration
>> 3. Yes, next step, no- step 5
>> 4. Run migrations
>> 5. Update alembic_version
>> 5. Release lock on alembic_version
>>
>> That would at least prevent multiple migrations stepping on each
>> other's feet? Whichever gets to alembic_version first performs the
>> entire migration and when the other process gets to alembic_version,
>> it finds no migrations are needed.
>>
>>
>>
>>
>> >
>> >
>> >
>> >
>> >
>> >>
>> >
>> > --
>> > You received this message because you are subscribed to a topic in the
>> > Google Groups "sqlalchemy-alembic" group.
>> > To unsubscribe from this topic, visit
>> >
>> > https://groups.google.com/d/topic/sqlalchemy-alembic/I2AAEUdF2dQ/unsubscribe.
>> > To unsubscribe from this group and all its topics, send an email to
>> > sqlalchemy-alembic+unsubscr...@googlegroups.com.
>> > For more options, visit https://groups.google.com/d/optout.
>>
>>
>>
>> --
>> http://echorand.me
>>
>> --
>> You

Re: Running Migrations Simulataneously

2016-04-27 Thread Mike Bayer 
Yes, you can implement that inside your env.py if you wanted.All the
other processes would wait and once inside the lock they all need to
re-check the version.Not something alembic would include, though.

On Tuesday, April 26, 2016, Amit Saha  wrote:

> On Wed, Apr 27, 2016 at 12:37 AM, Mike Bayer  > wrote:
> >
> >
> > On 04/26/2016 01:22 AM, Amit Saha wrote:
> >>
> >> On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer  >
> >> wrote:
> >>>
> >>>
> >>>
> >>> On 04/25/2016 08:30 PM, Amit Saha wrote:
> 
> 
>  Hi all,
> 
>  In my scenario, DB migrations (via alembic) will be run as part of the
>  app deployment and multiple app deployments will happen near
>  simultaneously (multiple EC2 instances talking to the same MySQL DB).
>  Let's, for simplicity's sake assume two instances:
> 
>  Instance #1: Alembic sees alembic_version table and finds that we do
>  need the migrations to happen, and starts the migration.
> 
>  Instance #2: Alembic sees alembic_version table and finds that we do
>  need the migrations to happen, and starts the migration.  This doesn't
>  obviously happen since MySQL will lock the table during the migration.
> >>>
> >>>
> >>>
> >>> this scenario is entirely unsupported.   MySQL does not support
> >>> transactional DDL and migration #1 will be hitting some tables and
> >>> migration
> >>> #2 another.The version table is not updated until after a
> particular
> >>> migration takes place so concurrent processes will both be doing the
> same
> >>> migration at the same time.
> >>>
> 
>  Now, let's say instance #1 finishes the migration first and updates
> the
>  alembic_version table. Then, the migration triggered by instance #2
>  starts to run, and errors out because the schema changes are already
> in
>  place.
> 
>  Is this what will happen or does alembic get a read/write lock on
>  alembic_version table for the entire migration?
> >>>
> >>>
> >>>
> >>> there are no "read/write locks" on the alembic_version table.  What
> >>> you're
> >>> doing will not work at all.   Only one process should be running
> >>> migrations
> >>> against a single target database at a time.
> >>
> >>
> >> Thanks for the reply, Mike. I am wondering what are my options -
> >> should I just have to ensure that I don't run migrations in more than
> >> one process? Which would mean, I cannot really use alembic for my DB
> >> migrations (I wonder how people would usually do it).
> >
> >
> > How this is usually done is that the decision to "migrate" is initiated
> > manually.  I'm guessing this is a web application that wants to just
> > automigrate when it starts.Usually what apps like that do is check
> that
> > the DB is not up to date, and present a web form with a button to
> "upgrade"
> > - so the user pushes the button only once.Or, if this is some kind of
> > service that spawns multiple processes, the lead process does the check
> > against the table and does the upgrade.
> >
> > The thing about schema upgrades is that they require code changes to have
> > any upgrades to apply.  So therefore, you're starting *something* to
> first
> > run that new code; that's where the migration step should happen, before
> > everything else starts up.
>
> Thanks again Mike. So, I think the manual separate step to change the
> schema has to be in there.
>
> For my curiosity's sake (and please bear with my noobishness), what if
> alembic had a lock (provided the RDMS allows) for the entire time:
>
> 1. Get RW lock on alembic_version
> 2. Check if we need a migration
> 3. Yes, next step, no- step 5
> 4. Run migrations
> 5. Update alembic_version
> 5. Release lock on alembic_version
>
> That would at least prevent multiple migrations stepping on each
> other's feet? Whichever gets to alembic_version first performs the
> entire migration and when the other process gets to alembic_version,
> it finds no migrations are needed.
>
>
>
>
> >
> >
> >
> >
> >
> >>
> >
> > --
> > You received this message because you are subscribed to a topic in the
> > Google Groups "sqlalchemy-alembic" group.
> > To unsubscribe from this topic, visit
> >
> https://groups.google.com/d/topic/sqlalchemy-alembic/I2AAEUdF2dQ/unsubscribe
> .
> > To unsubscribe from this group and all its topics, send an email to
> > sqlalchemy-alembic+unsubscr...@googlegroups.com .
> > For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> http://echorand.me
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are

Re: Running Migrations Simulataneously

2016-04-26 Thread Mike Bayer 



On 04/26/2016 01:22 AM, Amit Saha wrote:

On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer  wrote:



On 04/25/2016 08:30 PM, Amit Saha wrote:


Hi all,

In my scenario, DB migrations (via alembic) will be run as part of the
app deployment and multiple app deployments will happen near
simultaneously (multiple EC2 instances talking to the same MySQL DB).
Let's, for simplicity's sake assume two instances:

Instance #1: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.

Instance #2: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.  This doesn't
obviously happen since MySQL will lock the table during the migration.



this scenario is entirely unsupported.   MySQL does not support
transactional DDL and migration #1 will be hitting some tables and migration
#2 another.The version table is not updated until after a particular
migration takes place so concurrent processes will both be doing the same
migration at the same time.



Now, let's say instance #1 finishes the migration first and updates the
alembic_version table. Then, the migration triggered by instance #2
starts to run, and errors out because the schema changes are already in
place.

Is this what will happen or does alembic get a read/write lock on
alembic_version table for the entire migration?



there are no "read/write locks" on the alembic_version table.  What you're
doing will not work at all.   Only one process should be running migrations
against a single target database at a time.


Thanks for the reply, Mike. I am wondering what are my options -
should I just have to ensure that I don't run migrations in more than
one process? Which would mean, I cannot really use alembic for my DB
migrations (I wonder how people would usually do it).


How this is usually done is that the decision to "migrate" is initiated 
manually.  I'm guessing this is a web application that wants to just 
automigrate when it starts.Usually what apps like that do is check 
that the DB is not up to date, and present a web form with a button to 
"upgrade" - so the user pushes the button only once.Or, if this is 
some kind of service that spawns multiple processes, the lead process 
does the check against the table and does the upgrade.


The thing about schema upgrades is that they require code changes to 
have any upgrades to apply.  So therefore, you're starting *something* 
to first run that new code; that's where the migration step should 
happen, before everything else starts up.









--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Running Migrations Simulataneously

2016-04-25 Thread Amit Saha 
On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer  wrote:
>
>
> On 04/25/2016 08:30 PM, Amit Saha wrote:
>>
>> Hi all,
>>
>> In my scenario, DB migrations (via alembic) will be run as part of the
>> app deployment and multiple app deployments will happen near
>> simultaneously (multiple EC2 instances talking to the same MySQL DB).
>> Let's, for simplicity's sake assume two instances:
>>
>> Instance #1: Alembic sees alembic_version table and finds that we do
>> need the migrations to happen, and starts the migration.
>>
>> Instance #2: Alembic sees alembic_version table and finds that we do
>> need the migrations to happen, and starts the migration.  This doesn't
>> obviously happen since MySQL will lock the table during the migration.
>
>
> this scenario is entirely unsupported.   MySQL does not support
> transactional DDL and migration #1 will be hitting some tables and migration
> #2 another.The version table is not updated until after a particular
> migration takes place so concurrent processes will both be doing the same
> migration at the same time.
>
>>
>> Now, let's say instance #1 finishes the migration first and updates the
>> alembic_version table. Then, the migration triggered by instance #2
>> starts to run, and errors out because the schema changes are already in
>> place.
>>
>> Is this what will happen or does alembic get a read/write lock on
>> alembic_version table for the entire migration?
>
>
> there are no "read/write locks" on the alembic_version table.  What you're
> doing will not work at all.   Only one process should be running migrations
> against a single target database at a time.

Thanks for the reply, Mike. I am wondering what are my options -
should I just have to ensure that I don't run migrations in more than
one process? Which would mean, I cannot really use alembic for my DB
migrations (I wonder how people would usually do it).

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: Running Migrations Simulataneously

2016-04-25 Thread Mike Bayer 



On 04/25/2016 08:30 PM, Amit Saha wrote:

Hi all,

In my scenario, DB migrations (via alembic) will be run as part of the
app deployment and multiple app deployments will happen near
simultaneously (multiple EC2 instances talking to the same MySQL DB).
Let's, for simplicity's sake assume two instances:

Instance #1: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.

Instance #2: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.  This doesn't
obviously happen since MySQL will lock the table during the migration.


this scenario is entirely unsupported.   MySQL does not support 
transactional DDL and migration #1 will be hitting some tables and 
migration #2 another.The version table is not updated until after a 
particular migration takes place so concurrent processes will both be 
doing the same migration at the same time.




Now, let's say instance #1 finishes the migration first and updates the
alembic_version table. Then, the migration triggered by instance #2
starts to run, and errors out because the schema changes are already in
place.

Is this what will happen or does alembic get a read/write lock on
alembic_version table for the entire migration?


there are no "read/write locks" on the alembic_version table.  What 
you're doing will not work at all.   Only one process should be running 
migrations against a single target database at a time.






(And hence instance #2

never will need to run the migration since when it reads
alembic_version, the DB already has the migrated schema).

Thanks for your insights.

Best Wishes,
Amit.





--
You received this message because you are subscribed to the Google
Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy-alembic+unsubscr...@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.