Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

``pt-online-schema-change`` could break replication.

Description

**Reported in Launchpad by gu lei last update 19-03-2012 16:57:32

pt-online-schema-change h=127.0.0.1,P=3306,t=db.tiny_url --alter "drop key url","add key url(url(80))" --sleep 0.1

MySQL version:5.1.55-rel12.6-log Percona Server with XtraDB (GPL), Release 12.6, Revision 200

on slave:

show slave status\G

Master_Host: 192.168.1.1
Master_User: repuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: crtalk1-bin.009260
Read_Master_Log_Pos: 125939386
Relay_Log_File: tw_usr-relay-bin.029288
Relay_Log_Pos: 122264451
Relay_Master_Log_File: crtalk1-bin.009260
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: 1146
Last_Error: Error 'Table 'db.__tmp_tiny_url' doesn't exist' on opening tables
Skip_Counter: 0
Exec_Master_Log_Pos: 122264304
Relay_Log_Space: 125939734
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 'db.__tmp_tiny_url' doesn't exist' on opening tables

binlog on master:
mysqlbinlog -v -v --base64-output=DECODE-ROWS crtalk1-bin.009261 | grep -B 1 -A 10 __tmp|more
#120216 0:00:27 server id 152233306 end_log_pos 29260 Table_map: `db`.`tiny_url` mapp
ed to number 278651
#120216 0:00:27 server id 152233306 end_log_pos 29324 Table_map: `db`.`__tmp_tiny_url
` mapped to number 278645
#120216 0:00:27 server id 152233306 end_log_pos 29420 Write_rows: table id 278651
#120216 0:00:27 server id 152233306 end_log_pos 29516 Write_rows: table id 278645 flags: S
TMT_END_F

  1.  

    1.  

      1. INSERT INTO db.tiny_url

      2. SET

      3. @1=88801080 /* INT meta=0 nullable=0 is_null=0 */

      4. @2='http://15449413.blog.hexun.com/62026240_d.html' /* VARSTRING(400) meta=400 nullable=0 is_n
        ull=0 */

      5. @3='--' /* VARSTRING(18) meta=18 nullable=0 is_null=0 */

      6. @4=0 /* LONGINT meta=0 nullable=0 is_null=0 */

      7. @5='00:00:27' /* TIME meta=0 nullable=0 is_null=0 */

      8. INSERT INTO db.__tmp_tiny_url

      9. SET

      10. @1=88801080 /* INT meta=0 nullable=0 is_null=0 */

      11. @2='http://15449413.blog.hexun.com/62026240_d.html' /* VARSTRING(400) meta=400 nullable=0 is_n
        ull=0 */

      12. @3='--' /* VARSTRING(18) meta=18 nullable=0 is_null=0 */

      13. @4=0 /* LONGINT meta=0 nullable=0 is_null=0 */

      14. @5='00:00:27' /* TIME meta=0 nullable=0 is_null=0 */

  2. at 29516
    #120216 0:00:27 server id 152233306 end_log_pos 29543 Xid = 38657832443
    COMMIT/!/;

  3. at 29543

__tmp_tiny_url created only on master but triggers wrote binlog so error occured on slave.

Environment

None

Smart Checklist

Activity

Carlos Salguero September 20, 2019 at 7:51 PM

Merged into 3.0 branch

lpjirasync January 24, 2018 at 1:55 PM

**Comment from Launchpad by: Baron Schwartz on: 14-03-2012 22:12:43

Is this fix released?

lpjirasync January 24, 2018 at 1:55 PM

**Comment from Launchpad by: Daniel Nichter on: 02-03-2012 19:24:35

I was able to reproduce this by add SET SQL_LOG_BIN=0 to the sql file for alter_active_table.t. At present, the test file loads the table to be altered on the master which replicates to the slave. Therefore, the changes made by the triggers don't cause an error because the slave also has the table.

After talking with Baron, and in view of the fact that we plan to redesign this tool in the future, for the current version (2.0.x), we are going to add an --execute option that the user must specify before the tool will actually work. This is to encourage users to read the docs, which we'll also update to mention that altering a table on a master that doesn't exist on a slave will cause a problem, other issues with replication, etc.

lpjirasync January 24, 2018 at 1:55 PM

**Comment from Launchpad by: gu lei on: 02-03-2012 01:44:18

Create and alter __tmp table before set sql_log_bin=0.

set sql_log_bin=0

create triggers

insert into __tmp select from ....

rename __tmp table:

create __tmp table before set sql_log_bin=1

set sql_log_bin=1;

drop __tmp table

So slave would not be broken and __tmp table on slave would be dropped.

lpjirasync January 24, 2018 at 1:55 PM

**Comment from Launchpad by: gu lei on: 01-03-2012 05:41:51

Hi, Baron Schwartz:

A suggestion:

Create and alter __tmp table before set sql_log_bin=0.

After rename __tmp table:

set sql_log_bin=0;

create __tmp table

set sql_log_bin=1;

drop __tmp table

Done

Details

Assignee

Reporter

Priority

Fix versions

Labels

Time tracking

1d logged

Smart Checklist

Created January 24, 2018 at 1:55 PM
Updated March 4, 2024 at 5:25 PM
Resolved January 24, 2018 at 1:55 PM