Replication Problem?

We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
queries of the form "INSERT INTO test.test_table VALUES('test','1')" no
longer replicate. If you connect to or change to the test database and
then execute "INSERT INTO test_table VALUES('test','1')" the query
replicates. Is this normal behavior? Is there a configuration setting
that I can change to make replication accept explicit database naming in
the query?

Any help would be greatly appreciated!

--
Ed Pauley II
ed@horseracinginfo.com

Ed, this is unfortunately "by design". Personally I don't get why this
choice was made...

Reference:
http://lists.mysql.com/mysql/190869


Regards,

Atle
FCI, Inc. - Unix Systems Administrator

On Tue, 12 Dec 2006, Ed Pauley II wrote:

> We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
> queries of the form "INSERT INTO test.test_table VALUES('test','1')" no
> longer replicate. If you connect to or change to the test database and
> then execute "INSERT INTO test_table VALUES('test','1')" the query
> replicates. Is this normal behavior? Is there a configuration setting
> that I can change to make replication accept explicit database naming in
> the query?
>
> Any help would be greatly appreciated!
>
> --
> Ed Pauley II
> ed@horseracinginfo.com
>
>
>
>
>

Hi Ed,

You may like to look at this, especially the last statement. If you are
not using these options however, I would suspect a bug.

Regards

--replicate-do-db=db_name

Tell the slave to restrict replication to statements where the default
database (that is, the one selected by USE) is db_name. To specify more
than one database, use this option multiple times, once for each
database. Note that this does not replicate cross-database statements
such as UPDATE some_db.some_table SET foo='bar' while having selected a
different database or no database.

An example of what does not work as you might expect: If the slave is
started with --replicate-do-db=sales and you issue the following
statements on the master, the UPDATE statement is not replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;

The main reason for this "just check the default database" behavior is
that it is difficult from the statement alone to know whether it should
be replicated (for example, if you are using multiple-table DELETE
statements or multiple-table UPDATE statements that act across multiple
databases). It is also faster to check only the default database rather
than all databases if there is no need.

If you need cross-database updates to work, use
--replicate-wild-do-table=db_name.% instead. See Section 6.9, "How
Servers Evaluate Replication Rules".


---------------------------------------------------------------
********** _/ ********** David Logan
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
**** _/ _/ _/ _/ **** Desk: +61 8 8408 4273
**** _/ _/ _/_/_/ **** Mobile: +61 417 268 665
***** _/ ******
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia
i n v e n t
---------------------------------------------------------------

-----Original Message-----
From: Atle Veka [mailto:atlev@flyingcroc.net]
Sent: Wednesday, 13 December 2006 10:55 AM
To: Ed Pauley II
Cc: mysql@lists.mysql.com
Subject: Re: Replication Problem?

Ed, this is unfortunately "by design". Personally I don't get why this
choice was made...

Reference:
http://lists.mysql.com/mysql/190869


Regards,

Atle
FCI, Inc. - Unix Systems Administrator

On Tue, 12 Dec 2006, Ed Pauley II wrote:

> We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
> queries of the form "INSERT INTO test.test_table VALUES('test','1')"
no
> longer replicate. If you connect to or change to the test database and
> then execute "INSERT INTO test_table VALUES('test','1')" the query
> replicates. Is this normal behavior? Is there a configuration setting
> that I can change to make replication accept explicit database naming
in
> the query?
>
> Any help would be greatly appreciated!
>
> --
> Ed Pauley II
> ed@horseracinginfo.com
>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=david.logan@hp.com

It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

-Ed



Logan, David (SST - Adelaide) wrote:
> Hi Ed,
>
> You may like to look at this, especially the last statement. If you are
> not using these options however, I would suspect a bug.
>
> Regards
>
> --replicate-do-db=db_name
>
> Tell the slave to restrict replication to statements where the default
> database (that is, the one selected by USE) is db_name. To specify more
> than one database, use this option multiple times, once for each
> database. Note that this does not replicate cross-database statements
> such as UPDATE some_db.some_table SET foo='bar' while having selected a
> different database or no database.
>
> An example of what does not work as you might expect: If the slave is
> started with --replicate-do-db=sales and you issue the following
> statements on the master, the UPDATE statement is not replicated:
> USE prices;
> UPDATE sales.january SET amount=amount+1000;
>
> The main reason for this "just check the default database" behavior is
> that it is difficult from the statement alone to know whether it should
> be replicated (for example, if you are using multiple-table DELETE
> statements or multiple-table UPDATE statements that act across multiple
> databases). It is also faster to check only the default database rather
> than all databases if there is no need.
>
> If you need cross-database updates to work, use
> --replicate-wild-do-table=db_name.% instead. See Section 6.9, "How
> Servers Evaluate Replication Rules".
>
>
> ---------------------------------------------------------------
> ********** _/ ********** David Logan
> ******* _/ ******* ITO Delivery Specialist - Database
> ***** _/ ***** Hewlett-Packard Australia Ltd
> **** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
> **** _/ _/ _/ _/ **** Desk: +61 8 8408 4273
> **** _/ _/ _/_/_/ **** Mobile: +61 417 268 665
> ***** _/ ******
> ****** _/ ******** Postal: 148 Frome Street,
> ******** _/ ********** Adelaide SA 5001
> Australia
> i n v e n t
> ---------------------------------------------------------------
>
> -----Original Message-----
> From: Atle Veka [mailto:atlev@flyingcroc.net]
> Sent: Wednesday, 13 December 2006 10:55 AM
> To: Ed Pauley II
> Cc: mysql@lists.mysql.com
> Subject: Re: Replication Problem?
>
> Ed, this is unfortunately "by design". Personally I don't get why this
> choice was made...
>
> Reference:
> http://lists.mysql.com/mysql/190869
>
>
> Regards,
>
> Atle
> FCI, Inc. - Unix Systems Administrator
>
> On Tue, 12 Dec 2006, Ed Pauley II wrote:
>
>
>> We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
>> queries of the form "INSERT INTO test.test_table VALUES('test','1')"
>>
> no
>
>> longer replicate. If you connect to or change to the test database and
>> then execute "INSERT INTO test_table VALUES('test','1')" the query
>> replicates. Is this normal behavior? Is there a configuration setting
>> that I can change to make replication accept explicit database naming
>>
> in
>
>> the query?
>>
>> Any help would be greatly appreciated!
>>
>> --
>> Ed Pauley II
>> ed@horseracinginfo.com
>>
>>
>>
>>
>>
>>
>
>


--
Ed Pauley II
ed@horseracinginfo.com
http://www.brisnet.com
http://www.brisbet.com

It turns out that I have a binlog-ignore-db option configured for
another database. If you have even one of these you must make all
updates in the default database if you want them to be replicated. This
is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to
5.0 the same day which explains why we did not see this behavior sooner.
I removed the option from the config file and just used
replicate-ignore-db instead and all is right in the world again. Hope
this helps someone else down the road. Thanks for all of your helpful
information and suggestions.

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

-Ed


Logan, David (SST - Adelaide) wrote:
> Hi Ed,
>
> You may like to look at this, especially the last statement. If you are
> not using these options however, I would suspect a bug.
>
> Regards
>
> --replicate-do-db=db_name
>
> Tell the slave to restrict replication to statements where the default
> database (that is, the one selected by USE) is db_name. To specify more
> than one database, use this option multiple times, once for each
> database. Note that this does not replicate cross-database statements
> such as UPDATE some_db.some_table SET foo='bar' while having selected a
> different database or no database.
>
> An example of what does not work as you might expect: If the slave is
> started with --replicate-do-db=sales and you issue the following
> statements on the master, the UPDATE statement is not replicated:
> USE prices;
> UPDATE sales.january SET amount=amount+1000;
>
> The main reason for this "just check the default database" behavior is
> that it is difficult from the statement alone to know whether it should
> be replicated (for example, if you are using multiple-table DELETE
> statements or multiple-table UPDATE statements that act across multiple
> databases). It is also faster to check only the default database rather
> than all databases if there is no need.
>
> If you need cross-database updates to work, use
> --replicate-wild-do-table=db_name.% instead. See Section 6.9, "How
> Servers Evaluate Replication Rules".
>
>
> ---------------------------------------------------------------
> ********** _/ ********** David Logan
> ******* _/ ******* ITO Delivery Specialist - Database
> ***** _/ ***** Hewlett-Packard Australia Ltd
> **** _/_/_/ _/_/_/ **** E-Mail: david.logan@hp.com
> **** _/ _/ _/ _/ **** Desk: +61 8 8408 4273
> **** _/ _/ _/_/_/ **** Mobile: +61 417 268 665
> ***** _/ ******
> ****** _/ ******** Postal: 148 Frome Street,
> ******** _/ ********** Adelaide SA 5001
> Australia
> i n v e n t
> ---------------------------------------------------------------
>
> -----Original Message-----
> From: Atle Veka [mailto:atlev@flyingcroc.net]
> Sent: Wednesday, 13 December 2006 10:55 AM
> To: Ed Pauley II
> Cc: mysql@lists.mysql.com
> Subject: Re: Replication Problem?
>
> Ed, this is unfortunately "by design". Personally I don't get why this
> choice was made...
>
> Reference:
> http://lists.mysql.com/mysql/190869
>
>
> Regards,
>
> Atle
> FCI, Inc. - Unix Systems Administrator
>
> On Tue, 12 Dec 2006, Ed Pauley II wrote:
>
>
>> We recently upgraded to MySQL 5.0. Since upgrading I have noticed that
>> queries of the form "INSERT INTO test.test_table VALUES('test','1')"
>>
> no
>
>> longer replicate. If you connect to or change to the test database and
>> then execute "INSERT INTO test_table VALUES('test','1')" the query
>> replicates. Is this normal behavior? Is there a configuration setting
>> that I can change to make replication accept explicit database naming
>>
> in
>
>> the query?
>>
>> Any help would be greatly appreciated!
>>
>> --
>> Ed Pauley II
>> ed@horseracinginfo.com
>>
>>
>>
>>
>>
>>
>
>


--
Ed Pauley II
Internet Software Developer
Bloodstock Research Information Services
859.223.4444
800.354.9206 ext. 297
ed@horseracinginfo.com
http://www.brisnet.com
http://www.brisbet.com