Here's the setup: I have a MySQL DB cluster in Rackspace that I'm already using for replication to multple slaves in multiple datacenters. I am trying to use AWS' Aurora with a MySQL front-end, which I've had working before, but now replication is breaking. This is the error that i'm seeing

Last_Errno: 1146

Last_Error: Error 'Table 'SOME_DB_NAME2.segment' doesn't exist' on query. Default database: 'SOME_DB_NAME2'. Query: 'DELETE FROM segment WHERE 1'

The problem is, is that I have only imported one database called SOME_DB_NAME1, NOT SOME_DB_NAME2. So the error that i'm getting for a table that doesn't exist in my database at all is really strange.

I exported 1 database from my Rackspace DB cluster to setup replication, and when I import that 1 database into Aurora and kick off replication, it almost immediately failes and gives me the Errno: 1146 for a table that doesn't even exist in the 1 database that I brought over. I've done a SHOW TABLES; using the 1 database that I brought over, and i've confirmed that the problematic table doesn't even exist. I don't know if maybe I have to grab ALL databases from Rackspace and bring them over to initiate replication, even though I just wanted 1 of the databases...

  • If the replication logs have statements for tables that don't exist on the destination what would you expect the behavior to be? This is exactly how I'd expect it to work. If it just ignored statements for tables that didn't exist how would you ever know if someone dropped a table on the slave on accident? – yoonix May 26 '16 at 18:38
  • There shouldn't be replication logs for tables that don't exist at all. Let's say on my main DB cluster I have DB's 1, 2, 3, 4, and 5. I -only- created a mysql dump for DB #2, and then imported that DB#2 into aurora. Now, Aurora is breaking replication because it's saying that a table that belongs on DB #4 doesn't exist. Why is that happening if I only want to replicate DB #2 from Rackspace to DB#2 on Aurora? – user182030 May 26 '16 at 19:13
  • Without seeing your configuration, I'd assume it's because the replication logs log for all the databases as is the default. If it's in the replication log, the server is going to try to apply it. If the table doesn't exist on the slave that it's trying to apply to, it fails. The replication logs are written by the master, not the slave. The master has no knowledge of what exists and doesn't on the slave. – yoonix May 26 '16 at 19:38
  • Ok great, than this totally makes sense. Thanks for making me aware of the default behavior! I'm going to do an all database dump and just work with it that way – user182030 May 26 '16 at 20:11

It sounds to me like you need a -replication-do-DB statement on your slave so that it will only attempt to run queries from the database that you are trying to replicate. Otherwise as noted in the comments it will attempt to run statements on all databases (even the ones that don't exist) and create the error identified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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