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:
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

[17 Jan 2013 2:26] Brodey Dover
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.
[17 Jan 2013 7:30] Valeriy Kravchuk
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.
[17 Jan 2013 14:31] Brodey Dover
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 ****
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

socket          = /var/run/mysqld/mysqld.sock
nice            = 0


user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /prod_data/mysql/mysql/
tmpdir          = /tmp
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


max_allowed_packet      = 512M


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.
[17 Jan 2013 14:43] MySQL Verification Team
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?
[17 Jan 2013 16:50] Brodey Dover
"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,
[17 Jan 2013 16:53] Brodey Dover
Also, it isn't me making the INSERT statements. It is the application.
[17 Jan 2013 19:05] Sveta Smirnova
Thank you for the feedback.

Closed as "Not a bug" since this is not MySQL bug.
[17 Jan 2013 21:03] Brodey Dover
You can conclude that? How?
[18 Jan 2013 4:38] Brodey Dover
Tried the new configuration from Shane (negated logic) and replication works just fine. Shane gets a free Beer.

Status -> Closed.