4

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 ?

1 Answer 1

2

I finally found my problem.

This line in the SHOW SLAVE STATUS\G

Replicate_Wild_Ignore_Table: mydb%.%\_tmp

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 from my.cnf and everything was back to normal.

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.