Replication, Stored Proceedures and Databases

After getting table replication to work by including the USE database on th=
e creation scripts, I have run into a rather large problem. We have 5 data=
bases on the server which get replicated to another server. We call them d=
atabases, A, B, C, D, and E. we have two other databases F and G which are=
not replicated. Inside of F and G we have stored procedures (F for readin=
g, G for writing) that are locked down pretty good. The stored procedures =
run as a specific user to do the task they need to do.

The problem is when a procedure in G modifies a table in database A, the co=
rresponding update isn't replicated to the slave database. We have some pr=
ocedures that modify tables across the board and access other user function=
s inside of database G. =20

In database G we have 150+ stored procedures.

What's the best approach to fix this problem? Is it as simple as adding th=
e appropriate USE statement inside of the stored procedure right before the=
insert/update/delete/whatever?

Any help would be greatly appreciated.=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Replication, Stored Proceedures and Databases

Gary@primeexalia.com (Gary Smith) writes:

....

> In database G we have 150+ stored procedures.

150k stored procedures? Sounds "rather large". Do you really need this?

> What's the best approach to fix this problem? Is it as simple as adding the appropriate USE statement inside of the stored procedure right before the insert/update/delete/whatever?

I'd suggest row based replication. In your previous post you mentioned
you were using 5.1.35 so you can do that. One of the reasons for
using RBR is precisely to make life clearer when replicating from one
server to another. The rows changed on the master will be changed on
the slave. You don't need to depend on the effect of the stored
procedure on master and slave being the same.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

R: Re: Replication, Stored Proceedures and Databases

--000e0cd2a0286d7716046e6aec9e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

You dont have changes coming from db G since it is ignored from replication.
Why dont You move all stored procs in a separate db and replicate it as
well? You will use it as a 'library' for all of your dbs. Of course prepose
your schema name, always. You dont have to change replication type in This
case. Cheers, Claudio

Il giorno 11 lug, 2009 10:03 m., "Simon J Mudd" ha
scritto:

Gary@primeexalia.com (Gary Smith) writes:

....

> In database G we have 150+ stored procedures.
150k stored procedures? Sounds "rather large". Do you really need this?

> What's the best approach to fix this problem? Is it as simple as adding
the appropriate USE stat...
I'd suggest row based replication. In your previous post you mentioned
you were using 5.1.35 so you can do that. One of the reasons for
using RBR is precisely to make life clearer when replicating from one
server to another. The rows changed on the master will be changed on
the slave. You don't need to depend on the effect of the stored
procedure on master and slave being the same.

Simon

-- MySQL General Mailing List For list archives:
http://lists.mysql.com/mysql To unsubscribe: h...

--000e0cd2a0286d7716046e6aec9e--

RE: Replication, Stored Proceedures and Databases

> -----Original Message-----
> From: sjmudd@pobox.com [mailto:sjmudd@pobox.com]
> Sent: Saturday, July 11, 2009 1:02 AM
> To: mysql@lists.mysql.com
> Subject: Re: Replication, Stored Proceedures and Databases
>=20
> Gary@primeexalia.com (Gary Smith) writes:
>=20
> ...
>=20
> > In database G we have 150+ stored procedures.
>=20
> 150k stored procedures? Sounds "rather large". Do you really need this?

150, not 150,000.

> > What's the best approach to fix this problem? Is it as simple as
> adding the appropriate USE statement inside of the stored procedure
> right before the insert/update/delete/whatever?
>=20
> I'd suggest row based replication. In your previous post you mentioned
> you were using 5.1.35 so you can do that. One of the reasons for
> using RBR is precisely to make life clearer when replicating from one
> server to another. The rows changed on the master will be changed on
> the slave. You don't need to depend on the effect of the stored
> procedure on master and slave being the same.

We are using row based replication. In the wee hours of the night last nig=
ht we changed the replication rules to replicate everything but information=
_schema and mysql. No changes that are initiated from the stored procedure=
s in database G that update tables in database A are seen. We had to do an=
entire DB reload last night after we discovered this. =20

> Simon
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dgary@primeexalia.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Re: Replication, Stored Proceedures and Databases

> -----Original Message-----
> From: Claudio Nanni [mailto:claudio.nanni@gmail.com]
> Sent: Saturday, July 11, 2009 2:44 AM
> To: Simon J Mudd
> Cc: mysql@lists.mysql.com
> Subject: R: Re: Replication, Stored Proceedures and Databases
>=20
> You dont have changes coming from db G since it is ignored from
> replication.
> Why dont You move all stored procs in a separate db and replicate it as
> well? You will use it as a 'library' for all of your dbs. Of course
> prepose
> your schema name, always. You dont have to change replication type in
> This
> case. Cheers, Claudio
>

Database G is just that, a library for the stored procedures. Anyway, as m=
entioned in the other email, replicating all of the tables solved the probl=
em. As for the schema name, I always include it on all queries. I found t=
hat it was much easier to always to it instead of only doing it when I need=
to and forgetting. I work with Oracle and I'm always doing stuff in anoth=
er schema so I do it out of habit.

Thanks guys for the follow up.=20

I think someone should add a clear statement to the doc's regarding the cro=
ss schema replication for stored procedures and tables, when the active dat=
abase is not the replicated database, even though the affected table is in =
a replicated database. The docs are currently vague on this.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg