Bug #68130 alter table rename statement cause slave broken on ERROR 1050

• Submitted: 21 Jan 2013 5:55
• Modified: 22 Jan 2013 0:46
• Reporter: h h
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.5.21
• OS: Linux (centos5.6)
• CPU Architecture: Any

[21 Jan 2013 5:55] h h

Description:
program do rename A->temp ,B->A,temp->B once everyday

               self::$DBM->Execute($conn, $sql);
               $sql="alter table $table_tmp rename $table_temp";
               self::$DBM->Execute($conn, $sql);
               sleep(10);
               $sql="alter table $table rename $table_tmp";
               self::$DBM->Execute($conn, $sql);
               sleep(10);
               $sql="alter table $table_temp rename $table";
               self::$DBM->Execute($conn, $sql);

slave broken due to ERROR 1050,same problem happened half years ago,and today happened again ,so i can not find why,and here is the slave relay log content  ,and i use

# at 737463766
#130121  2:40:58 server id 5151  end_log_pos 737463766  Query   thread_id=134298908     exec_time=0     error_code=0
use toocle_suppliers_show_p/*!*/;
SET TIMESTAMP=1358707258/*!*/;
SET TIMESTAMP=1358707258/*!*/;
alter table product_show_total_tmp rename product_show_total_temp
/*!*/;
# at 737463913
#130121  2:41:08 server id 5151  end_log_pos 737463908  Query   thread_id=134298908     exec_time=0     error_code=0
SET TIMESTAMP=1358707268/*!*/;
alter table product_show_total rename product_show_total_tmp
/*!*/;
# at 737464055
#130121  2:41:12 server id 5151  end_log_pos 737464000  Query   thread_id=134320861     exec_time=0     error_code=0
SET TIMESTAMP=1358707272/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 737464147
#130121  2:41:12 server id 5151  end_log_pos 737464609  Query   thread_id=134320861     exec_time=0     error_code=0
use toocle_suppliers_tmp/*!*/;
SET TIMESTAMP=1358707272/*!*/;
UPDATE toocle_suppliers_base.company_s SET `poster` = 'amysimon80',`cname` = '海湾化工',`business_model` = '11',`regional` = 

'291113',`contact` = '潘风浪',`tel` = '020-—14è¡—2å·',`po -81789527',`email` = '[e-mail]',`mobile` = 

'13145716277',`url` = 'http://amysimon80.b2b.hc360.com/',`address` = '广东çœå¹¿å·žå¸‚东风西路和平å
stcode` = '510160',`pic_name1` = '',`poster_id` = 'amysimon80',`post_ip` = '119.129.114.58', post_time = now()  WHERE 1  and poster = 

'amysimon80'
/*!*/;
# at 737464756
#130121  2:41:12 server id 5151  end_log_pos 737464702  Query   thread_id=134320861     exec_time=0     error_code=0
SET TIMESTAMP=1358707272/*!*/;
COMMIT
/*!*/;
# at 737464849
#130121  2:41:18 server id 5151  end_log_pos 737464845  Query   thread_id=134298908     exec_time=0     error_code=0
use toocle_suppliers_show_p/*!*/;
SET TIMESTAMP=1358707278/*!*/;
alter table product_show_total_temp rename product_show_total

slave :
binlog_format            | STATEMENT 
master :
binlog_format            | MIXED             |

show slave status output:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.51.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000132
          Read_Master_Log_Pos: 759336569
               Relay_Log_File: mysqld-relay-bin.002834
                Relay_Log_Pos: 737464849
        Relay_Master_Log_File: mysqld-bin.000132
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'product_show_total' already exists'                                                         

                                         on query. Default database: 'toocle_suppliers_show_p'. Query: 'alter table prod                 

                                                                                uct_show_total_temp rename product_show_total'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 737464702
              Relay_Log_Space: 759336917
              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: 1050
               Last_SQL_Error: Error 'Table 'product_show_total' already exists'                                                         

                                         on query. Default database: 'toocle_suppliers_show_p'. Query: 'alter table prod                 

                                                                                uct_show_total_temp rename product_show_total'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 5151

How to repeat:
repeat is very difficult i think

[21 Jan 2013 19:51] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Error message in the SHOW SLAVE STATUS output clearly says what happened:

 Last_Error: Error 'Table 'product_show_total' already exists'                                                         

                                         on query. Default database: 'toocle_suppliers_show_p'. Query: 'alter table prod                 

                                                                                uct_show_total_temp rename product_show_total'

You have to find out why you have product_show_total table on the slave already.

[22 Jan 2013 0:46] h h

yes it's a bug, because i can not any table named  product_show_total ,second sql is already renamed the table,this is a replication bug