Solved: MySQL replication problem (sort of)
On Wed, 2007-10-17 at 21:16 +0200, Oded Arbel wrote: The problem is that on the second server everything looks peachy - information is coming in and its always up to date, but on the third server on the renamed database one of the log tables is not getting any updates - currently it is stuck with records from yesterday at noon and nothing new is coming in (I currently think its only one that has problems, but I haven't checked all of them). Other tables are getting updates just fine. The problem was - as usual - RTFM. The MySQL replicate-rewrite-db option, its important to know, is not resolving names using the actual data being changed and not even by parsing the SQL query that did the update - but only based on the current database select using the 'use' statement. The problem was that the table I had problem with (unlike all other tables) has data inserted using something like this: use my_db; insert into my_db.my_table (columns...) values (values...) This was enough to brake replicate-rewrite-db. Except for fixing the offending application, I don't know what else can be done. Funny though, if the rewrite command is replicate-rewrite-db=my_db-other_db and then I add replicate_do_table=my_db.my_table then updates to my_db.my_table on the origin server cause my_db.my_table on the replicating server to update, and not other_db.my_table. -- Oded = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
MySQL replication problem (sort of)
Hi list. I'm (possibly) having a problem with a MySQL replication setup, where replication works well except for one table that doesn't get new rows at all. I'm using a moderately complex replication setup where a remote server is a master to a server running on the local lan and replicates the entire server over an SSH connection (which sometimes go down, but then I restore it and the server just picks up the replication). A third server on the local lan replicates just one schema from the second server and renames it - the reason is that we want a local copy of the log tables that are generated on the production server, w/o overriding the development schema. The problem is that on the second server everything looks peachy - information is coming in and its always up to date, but on the third server on the renamed database one of the log tables is not getting any updates - currently it is stuck with records from yesterday at noon and nothing new is coming in (I currently think its only one that has problems, but I haven't checked all of them). Other tables are getting updates just fine. I don't see any errors in the mysqld.log file and the slave status shows both IO and SQL replication threads running w/o an error. mysqlcheck thinks that all the tables are ok. All the tables use the InnoDB engine and while there is a lot of foreign keying going on, the said problematic table has no foreign keys at all. If I resync the database (using mysqldump --master-data=1) it starts ok, and after a while (about a day) the same problematic table stops getting any new records. I rather not do a resync again because the last dump was already at 3GB of size and it takes several hours to load it into the database - and the next dump will be larger. Has anyone ever encountered a problem like this when replication stops for one table but not for the others ? -- Oded = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
