MySQL Bugs: #68104: Slave isn't writing any changes to its database

Bug #68104	Slave isn't writing any changes to its database
Submitted:	17 Jan 2013 2:26	Modified:	18 Jan 2013 4:38
Reporter:	Brodey Dover	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.66-0ubuntu0.10.04.3-log (Ubuntu)	OS:	Linux (Ubuntu 10.04.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	LTS, no errors, Not Replicating, replication, slave, Ubuntu LTS

Description:
I have two servers running 10.04.04 LTS Ubuntu in a MASTER SLAVE configuration. Before I started with this company, the servers were replicating just fine and we decided to sever replication during an application upgrade. Once we determined data integrity and functionality, I tried to bring replication back up. 

1. Dump from master, restore on slave, stop slave, change master command, start slave and I got an error about "being unable to read slave-relay". 
2. No problem. I cleared binary logs on both servers, reset the slave and started it up. At this point the SLAVE was able to successfully connect to the MASTER so I decided to start doing simple testing. I added a document to the application and this should increase the number of documents in document table. It didn't. The INSERT statement wasn't even attempted on the SLAVE.
3. At this point I check the status, I see logs being generated but nothing is coming through. NO ERRORS are reported in the error logs.

How to repeat:
The above....

Suggested fix:
None at this time.

I see you use filtering on slave:

Replicate_Do_DB: oscar_mcmaster,appointment_manager

So, are you sure that INSERTs you want to see replicated are made to these databases and they are set as default databases (use appointment_manager;) in session that is inserting on master?

See http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replic... for details.

The application has fully qualified statements. So, the master would INSERT INTO oscar_mcmaster.document (....) and for the other application, it would INSERT INTO appointment_manager.appointments(...) for example. The configuration on the master is this:

***** TOP OF FILE ****
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /prod_data/mysql/mysql/
tmpdir          = /tmp
skip-external-locking
key_buffer              = 32M
max_allowed_packet      = 512M
thread_stack            = 512K
thread_cache_size       = 50
myisam-recover         = BACKUP
max_connections        = 600
table_cache            = 64
query_cache_limit       = 1M
query_cache_size        = 16M
log_error                = /var/log/mysql/error.log

server-id                       = 1
log_bin                         = /var/log/mysql/mysql-bin.log
binlog-do-db                    = oscar_mcmaster,appointment_manager
expire_logs_days                = 10
max_binlog_size                 = 100M

innodb_buffer_pool_size=2G
query_cache_type=1
innodb_flush_method=O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet      = 512M

[mysql]

[isamchk]
key_buffer              = 32M

***** END OF FILE ****

I am checking oscar_mcmaster.document and this is the only database with this table. 

Thank you very much, I'm really at a loss on this one. Let me know if I can provide anything else. I'm doing my best to avoid having to re-install an OS.

So you should make sure you are in the the default database, e.g. do   "USE oscar_mcmaster;" before doing inserts, even if you use fully qualified table names.

Or just replicate everything by removing the replicate-do-db from my.cnf?

"Or just replicate everything by removing the replicate-do-db from my.cnf?"

Thank you Shane. I was looking for "just replicate everything" in the manual. I re-read it again after your comment and there is a weak implication about this mode of functionality. I will make this change and test it tonight after-hours.

Thank you,

Also, it isn't me making the INSERT statements. It is the application.

Thank you for the feedback.

Closed as "Not a bug" since this is not MySQL bug.

You can conclude that? How?

Tried the new configuration from Shane (negated logic) and replication works just fine. Shane gets a free Beer.

Status -> Closed.