4

I ran into a problem with our replication server. Essentially, we have 2 databases (database1 and database2). Master server has both. Slave has only database1. There is a

Replicate_Do_DB: database1

set in CHANGE MASTER TO configuration.

Now what happened is - we are using code igniter, and one of the programers created database2 and started inserting info into it. Code igniter sets a default database to database1. Now the result is for every query he produced - I get an error on SHOW SLAVE STATUS\G:

Error 'Table 'database2.tbl40' doesn't exist' on query. Default database: 'database1'. Query: 'INSERT INTO `database2`.`tbl40` (`date`, `day`) VALUES ('2011-04-26', '2011-04-26')'

So essentially, I he fixed the problem afterwards, but the replication doesn't work as there is around 1000 queries that will produce that error for replication server.

My question is - is there some way to clear queries like that from the binlog? Or I need to write a script that will do a 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

for every query that produces and error ?

Improve this question
2
  • what are you doing..are you directly inserting records to slave in database2.?
    – Abdul Manaf
    Commented Feb 15, 2012 at 12:53
  • yes, inserting stuff to database2, but, as far as I understand, code igniter mysql connection, selects database1 to begin with. It is all fine while it is happening on the master server, as it has both database1 and database2. But Slave server has only database1. So slave takes those queries from binlog, because database1 is selected, and tries to execute them on the slave, but on the slave server the database2 does not exist.
    – Katafalkas
    Commented Feb 15, 2012 at 13:41

2 Answers 2

Reset to default
5

If you really don't care about that table, you can use pt-slave-restart on the slave and have it skip those problems. I would be conservative about running it and make sure that you are only skipping queries for the table/database that you don't care about or at least for only a specific error.

You didn't post what the error code was in the output from SHOW SLAVE STATUS, but I suspect it is error 1146.

For example, this will skip all errors for 1146:

pt-slave-restart -u root -p pass --error-numbers 1146

Or, you could try skipping all errors that reference that table

pt-slave-restart -u root -p pass --error-text 'database2'

Another way to do this would be to set replicate-ignore-db=database2 and restart MySQL on the slave, but there are some caveats to how that works that you should read about in the documentation

Improve this answer
3
  • Hi. Yes the error code is 1146. I dont think that the replicate-ignore-db=database2 will work, as code igniter selects the database1 to start with. But now when I look at it, you give me an idea: there is also a replicate_ignore_table, I could set that to database2.tbl40. and the replicate_wild_ignore_table is something that could be usefull.
    – Katafalkas
    Commented Feb 15, 2012 at 13:49
  • Its me again, I think I solved it, I added a replicate-ignore-table=database2.tbl%, so it seems to be working so far ... Cheers for the idea :D
    – Katafalkas
    Commented Feb 15, 2012 at 14:13
  • Looks like Houston can stop worrying now. +1 !!!
    – RolandoMySQLDBA
    Commented Feb 15, 2012 at 16:22
4

I think the bigger problem here is your default database context was database1. Thats's why your slave tried to execute the update on database2 since it was specified in database2.table format.

Basically it's not safe to user db.table syntax with wildcards or you find yourself in the situation you did. If you're wanting to use the wildcard do or ignores it's generally safer to always specify your default db using "use" and execute the query in that context.

Improve this answer
3
  • totally agree. After this incident, I ll try making all the programers to show me, and test all their "awesome" queries before putting the production. Cheer for info! much appreciated.
    – Katafalkas
    Commented Feb 15, 2012 at 16:44
  • Instead of being so draconian w/ the devs, give them guidance an pointers as needed. Hopefully they're smart or they wouldn't have been hired as devs to begin with. To audit the finer points of their queries you might just turn on general query logging in your qa/dev environment. Look through that for new and potentially suspect queries. Raise true issues you might find from that rather than making them hate you for wanting to review ever select column from table where primary_key=N; query
    – atxdba
    Commented Feb 16, 2012 at 1:38
  • Cheers for a reply, I'll check out the query logging.
    – Katafalkas
    Commented Feb 17, 2012 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.