Mysql – thesql master slave “table already exists” but table not exists

MySQLmysql-replicationreplication

I have 1 master mysql process, and 2 slave.

Today on both slaves i see :

Error 'Table 'bgbilling.contract_status_balance_dump' already exists' on query. Default database: 'bgbilling'. Query: 'CREATE TABLE contract_status_balance_dump( UNIQUE(cid) ) SELECT cid, MAX(yy*12+(mm-1))%12 + 1 AS mm,FLOOR(MAX(yy*12+(mm-1)) / 12) AS yy FROM contract_balance GROUP BY cid'

"show tables" does not show this table.

I tryed stop slave , and do "drop table contract_status_balance_dump"
but:

ERROR 1051 (42S02): Unknown table 'contract_status_balance_dump'

How its possible? And how fix that?

Best Answer

Here is how to fix it

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
use bgbilling
DROP TABLE IF EXISTS contract_status_balance_dump;
CREATE TABLE contract_status_balance_dump( UNIQUE(cid) )
SELECT cid, MAX(yy*12+(mm-1))%12 + 1 AS mm,FLOOR(MAX(yy*12+(mm-1)) / 12) AS yy
FROM contract_balance GROUP BY cid;
START SLAVE;
SHOW SLAVE STATUS\G

Related Solutions

Mysql – What prevents loops in thesql master-master setups

As far as your specific question, it's a combination of two things:

By default, if a server receives a statement via replication, it will not send that same statement out to its slaves, preventing any kind of loop. This setting however, can be changed (by adding 'log-slave-updates' to the my.cnf), leading to:
In replication, it sends the server-id of the origination server along with the statement. If a server receives a statement via replication with the same server id, it will not execute it, thus preventing it from executing the same statement twice.

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

Best Answer

Related Solutions

Mysql – What prevents loops in thesql master-master setups

Mysql – Sporadic crash of master-slave MySQL replication process

Related Topic