Schema change sequence

[osolo]

Hi Everyone,

I'd like your help opinion about the sequence I user (or rather, would
like to use) to perform schema changes. Logically, it seems that it
should work, but mmm will fail mid-way.

1. mmm_control set_offline db2
2. (on db1) stop slave
3. <perform changes on db2>
4. wait for db2 to catch up with db1
5. mmm_control set_online db2
6. mmm_control move_role writer db2
7. (on db1) start slave
8. wait for db1 to catch up
9. mmm_control move_role writer db1

Like I said, this feels like it should work, but in reality at step 5
I get this error:
ERROR: Some replication checks failed on peer 'db1'. We can't set
'db2' online now. Please, wait some time.

Any insights would be appreciated. I do apologize if this is a noob
question that's been discussed a million times.

[Arjen Lentz]

Hi

On 15/12/2009, at 7:10 AM, osolo wrote:
> I'd like your help opinion about the sequence I user (or rather, would
> like to use) to perform schema changes. Logically, it seems that it
> should work, but mmm will fail mid-way.
>
> 1. mmm_control set_offline db2
> 2. (on db1) stop slave
> 3. <perform changes on db2>
> 4. wait for db2 to catch up with db1
> 5. mmm_control set_online db2
> 6. mmm_control move_role writer db2
> 7. (on db1) start slave
> 8. wait for db1 to catch up
> 9. mmm_control move_role writer db1
>
> Like I said, this feels like it should work, but in reality at step 5
> I get this error:
> ERROR: Some replication checks failed on peer 'db1'. We can't set
> 'db2' online now. Please, wait some time.

Perhaps your schema change makes subsequent events fail? Check with
SHOW SLAVE STATUS \G


But, stopping the slave thread does not help at all.
What you *want* is prevent your schema change from being replicated,
because otherwise it'll execute on the active master as well as all
slaves too.
So instead you do this on the non-active master:

SET SESSION SQL_LOG_BIN=0;
ALTER TABLE ...
SET SESSION SQL_LOG_BIN=1; -- or just disconnect

Then you move the active master so you can do the same alter table on
the now passive one.
And then same on each of the slaves in turn (if you have decent
loadbalancing in place)

That way you can do a schema change without affecting the system as a
whole being online and responsive.


Cheers,
Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Exceptional Services for MySQL at a fixed budget.

Follow our blog at http://openquery.com/blog/
OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org

[Oz Solomon]

Hi Arjen,

I'm trying to stay away from SET SESSION SQL_LOG_BIN=0. The sequence was
designed so that it would only need to be executed once, and all the slaves
running off of DB1 will automatically be updated, which greatly reduced time
as well as the chance of error.

Stopping the slave on DB1 does help because while I'm upgrading the schema
on DB2, DB1 is not affected. Only in step 7 does it "see" the schema
changes, but at that point it's not the active writer.

> Perhaps your schema change makes subsequent events fail

There are/were no failures. I think the problem I'm running into is that
mmm doesn't "like" you to set any master online if the other master's slave
isn't working.

Maybe all I'm really looking for is a --force switch.

-Oz

--

You received this message because you are subscribed to the Google Groups
"MySQL Multi Master Manager Development" group.
To post to this group, send email to mmm-...@googlegroups.com.
To unsubscribe from this group, send email to
mmm-devel+...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/mmm-devel?hl=en.

[Paul Graydon]

Have you tried looking at show slave status manually on db2? See what
errors it's throwing up that might cause MySQL-MMM to decide it's
unreliable?

[Arjen Lentz]

Hi Oz

On 15/12/2009, at 10:36 AM, Oz Solomon wrote:
> I'm trying to stay away from SET SESSION SQL_LOG_BIN=0.

Why?

[Oz Solomon]

>Why?

With SQL_LOG_BIN=0, you have to manually perform the changes on every slave
and master. Is less error prone if you don't have to do that, especially if
you have a lot of changes and a lot of slaves.

Make sense?

Oz


-----Original Message-----
From: mmm-...@googlegroups.com [mailto:mmm-...@googlegroups.com] On
Behalf Of Arjen Lentz
Sent: Monday, December 14, 2009 7:55 PM
To: mmm-...@googlegroups.com
Subject: Re: [mmm-devel] Schema change sequence

[Oz Solomon]

Hi Paul,

I verified with SHOW SLAVE STATUS on DB2 that everything was OK. If you
notice, the error mmm is reporting is for DB1, which makes sense since the
slave is stopped there.

-Oz

-----Original Message-----
From: mmm-...@googlegroups.com [mailto:mmm-...@googlegroups.com] On
Behalf Of Paul Graydon
Sent: Monday, December 14, 2009 7:42 PM
To: mmm-...@googlegroups.com
Subject: Re: [mmm-devel] Schema change sequence

[Arjen Lentz]

Hi Oz

On 15/12/2009, at 11:08 AM, Oz Solomon wrote:
>> Why?
>
> With SQL_LOG_BIN=0, you have to manually perform the changes on
> every slave
> and master. Is less error prone if you don't have to do that,
> especially if
> you have a lot of changes and a lot of slaves.
>
> Make sense?

Yes, but I disagree on the logic. Just think about the slaves. With
your method, you'd need to stop replication to prevent all slaves from
executing the schema change at the same time. This means that the
application will be either crippled or have to be offline, as updates
in the active master won't go to those slaves.

As I described in my original post, with SQL_LOG_BIN=0, the infra as a
whole is fully operational and the users won't notice a thing.
It requires some back-end magic, but that's all managable - and in
fact it can be automated!

Cheers,
Arjen.

[Istvan Podor]

Hey Guys,

I have to agree with Arjen.

Oz you afraid to use sql_log_bin =0. Did you recognized you are trying to use a HA application what is managing mysql over the client's possibilities? I mean its connect as a client each time its want to do something and you are worry about binlog? :))

Basically every time I did this its always happened in the same order:
- alter on slaves (not active/passive master)
- alter on passive master with sql_log_bin=0
- move role with mmm
- alter on the previously-active master with ALSO log_bin = 0

Tadaam! :) Works and as long as you can solve not to send traffic to those hosts which are currently under the schema change, you won't be offline, slow or whatever. (I didn't mention things like: when you executed sql_log_bin, verify your session is still active before executing the alter statement or always check out the status of replication etc)


Btw you as a reliable dba who installed mmm I suppose two things should be ok:
- you have to know how many slaves you have, where they are replicate from and how
- being sure of executing sql_log_bin, turning off turning back on when its needed

So don't worry, this is just the way of it :)

Istvan

PS: This "method" is also good if you want version or security upgrades. But beware moving to 5.1 from 5.0.