Crazy replication problem

Here is steps take to get to the problem. Version is 5.1.35

To be on the safe side, I did this prior to executing the scripts. I delet=
ed the mysql directory and the mysql-log directory, recreated them, chowned=
them, ran mysql_install_db, chowned them again. So, I know at least I'm w=
orking with a clean setup. I did this on both servers.

So, I have a script, which is actually a combination about 200 other script=
s, cat'ed into a single script. The first half of the script creates a dat=
abase and several tables. After all of the tables are created, a trigger i=
s created on one of the tables. Following the trigger, about 150 stored pr=
ocedures.

This loads fine on the master server. All looks well. I then set the slav=
e to slave against the master and it chokes on the creation of the trigger =
saying it can't find the table. quickly glancing at the tables, there are =
none. The database creation was replicated but the tables were not.

Here is what I have in the my.cnf file for replication (we had this setup f=
or master/master earlier, thus the replicate-do-db, but we will not be doin=
g that in this round):

log-slave-updates
sync_binlog =3D 1
replicate-same-server-id =3D 0
log_bin_trust_function_creators =3D 1
log-bin =3D /exports/mysql-log/log-repl
binlog-do-db =3D weblog
binlog-do-db =3D webarchive
replicate-do-db =3D weblog
replicate-do-db =3D webarchive

relay-log =3D relay-bin
relay-log-index =3D relay-index
relay-log-info-file =3D relay-info
relay-log-purge =3D 1

Everything is INNODB.

Any ideas as to why the tables didn't replicate? Also, I know that trigger=
s shouldn't be replicated. Is there any was to prevent trigger replication=
?=

--
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: Crazy replication problem

One quick follow up note:

In the top of the big script, I'm doing:

CREATE DATABASE x;
CREATE TABLE x.whatever (...);

Reading into the various bug reports, do I need to issue a USE x; ? I'm as=
suming that MySql isn't picking up the replica because there is no current =
database specified, even though the tables are qualified.

Can anyone confirm this?

Gary

________________________________________
From: Gary Smith [Gary@primeexalia.com]
Sent: Friday, July 10, 2009 5:12 PM
To: mysql@lists.mysql.com
Subject: Crazy replication problem

Here is steps take to get to the problem. Version is 5.1.35

To be on the safe side, I did this prior to executing the scripts. I delet=
ed the mysql directory and the mysql-log directory, recreated them, chowned=
them, ran mysql_install_db, chowned them again. So, I know at least I'm w=
orking with a clean setup. I did this on both servers.

So, I have a script, which is actually a combination about 200 other script=
s, cat'ed into a single script. The first half of the script creates a dat=
abase and several tables. After all of the tables are created, a trigger i=
s created on one of the tables. Following the trigger, about 150 stored pr=
ocedures.

This loads fine on the master server. All looks well. I then set the slav=
e to slave against the master and it chokes on the creation of the trigger =
saying it can't find the table. quickly glancing at the tables, there are =
none. The database creation was replicated but the tables were not.

Here is what I have in the my.cnf file for replication (we had this setup f=
or master/master earlier, thus the replicate-do-db, but we will not be doin=
g that in this round):

log-slave-updates
sync_binlog =3D 1
replicate-same-server-id =3D 0
log_bin_trust_function_creators =3D 1
log-bin =3D /exports/mysql-log/log-repl
binlog-do-db =3D weblog
binlog-do-db =3D webarchive
replicate-do-db =3D weblog
replicate-do-db =3D webarchive

relay-log =3D relay-bin
relay-log-index =3D relay-index
relay-log-info-file =3D relay-info
relay-log-purge =3D 1

Everything is INNODB.

Any ideas as to why the tables didn't replicate? Also, I know that trigger=
s shouldn't be replicated. Is there any was to prevent trigger replication=
?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgary@primeexalia.co=
m=

--
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: Crazy replication problem

> -----Original Message-----
> From: Gary Smith
> Sent: Friday, July 10, 2009 5:26 PM
> To: Gary Smith; mysql@lists.mysql.com
> Subject: RE: Crazy replication problem
>=20
> One quick follow up note:
>=20
> In the top of the big script, I'm doing:
>=20
> CREATE DATABASE x;
> CREATE TABLE x.whatever (...);
>=20
> Reading into the various bug reports, do I need to issue a USE x; ?
> I'm assuming that MySql isn't picking up the replica because there is
> no current database specified, even though the tables are qualified.
>=20
> Can anyone confirm this?
>=20
> Gary
>=20

So I found that adding a USE database to the tables allowed it to replicate=
.. I vaguely remember something like that a couple years back. =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