mysql master slave "table already exists" but table not exists

Question

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?

Answer 1

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

Answer 2

make sure that when checking the Slave you are running the command ‘ls bgbilling/contract_status_balance_dump’ inside the ‘bgbilling’ database. Hope this is helpful, good luck :)