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: [EMAIL PROTECTED]
**** _/ _/ _/ _/ **** 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:[EMAIL PROTECTED]
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
[EMAIL PROTECTED]
--
Ed Pauley II
[EMAIL PROTECTED]
http://www.brisnet.com
http://www.brisbet.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
