I have a MySQL Replication Master/Slave set up.
Multiple times a day, Replication breaks as follows
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ....
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000012
Read_Master_Log_Pos: 10458316
Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 1528552
Relay_Master_Log_File: bin-log.000012
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: dba
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mydb%.%\_tmp
Last_Errno: 1146
Last_Error: Error 'Table 'mydb.catalog_category_product_index_tmp' doesn't exist' on query. Default database: 'mydb'. Query: 'DELETE `catalog_category_product_index` FROM `catalog_category_product_index`
LEFT JOIN `catalog_category_product_index_tmp` AS `t` ON catalog_category_product_index.category_id = t.category_id AND catalog_category_product_index.store_id = t.store_id AND catalog_category_product_index.product_id = t.product_id WHERE (t.category_id IS NULL) AND (catalog_category_product_index.product_id IN ('69911'))'
Skip_Counter: 0
Exec_Master_Log_Pos: 9644906
Relay_Log_Space: 3944233
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table 'mydb.catalog_category_product_index_tmp' doesn't exist' on query. Default database: 'mydb'. Query: 'DELETE `catalog_category_product_index` FROM `catalog_category_product_index`
LEFT JOIN `catalog_category_product_index_tmp` AS `t` ON catalog_category_product_index.category_id = t.category_id AND catalog_category_product_index.store_id = t.store_id AND catalog_category_product_index.product_id = t.product_id WHERE (t.category_id IS NULL) AND (catalog_category_product_index.product_id IN ('69911'))'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1000
1 row in set (0.00 sec)
I simply run
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
and Replication comes back to normal
The binary logs on the Master have events that say
DROP TABLE catalog_category_product_index_tmp;
CREATE catalog_category_product_index_tmp (...);
- Inserts into catalog_category_product_index_tmp
My client uses binlog_format = MIXED on Master and Slave. When DROP TABLE
and CREATE TABLE
are executed, MySQL shifts to binlog_format = STATEMENT
to execute the DDL
.
This replication issue of the temp table not existing on the Slave happens to all tables that end in tmp
.
mysql> select table_name,engine from information_schema.tables where table_schema='mydb' and table_name like '%tmp';
+---------------------------------------------+--------+
| table_name | engine |
+---------------------------------------------+--------+
| catalog_category_anc_categs_index_tmp | MEMORY |
| catalog_category_anc_products_index_tmp | MEMORY |
| catalog_category_product_index_enbl_tmp | MEMORY |
| catalog_product_index_eav_decimal_tmp | MEMORY |
| catalog_product_index_eav_tmp | MEMORY |
| catalog_product_index_price_bundle_opt_tmp | MEMORY |
| catalog_product_index_price_bundle_sel_tmp | MEMORY |
| catalog_product_index_price_bundle_tmp | MEMORY |
| catalog_product_index_price_cfg_opt_agr_tmp | MEMORY |
| catalog_product_index_price_cfg_opt_tmp | MEMORY |
| catalog_product_index_price_downlod_tmp | MEMORY |
| catalog_product_index_price_final_tmp | MEMORY |
| catalog_product_index_price_opt_agr_tmp | MEMORY |
| catalog_product_index_price_opt_tmp | MEMORY |
| catalog_product_index_price_tmp | MEMORY |
| cataloginventory_stock_status_tmp | MEMORY |
+---------------------------------------------+--------+
QUESTIONS
- Is there any known issue with MySQL Replication and Magento ?
- Could this be an issue with Magento ?
- Any other ideas as to why ?
Best Answer
I finally found my problem.
This line in the
SHOW SLAVE STATUS\G
was actually preventing the DB user from dropping and creating the MEMORY tables. I made a change to puppet to remove
Replicate_Wild_Ignore_Table
frommy.cnf
and everything was back to normal.