Buy Percona ServicesBuy Now!

[Emergency] pt-online-schema-change makes column lose data

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • windy.jian
    replied
    Originally posted by windy.jian View Post
    percona server 5.7.25-28
    pt-osc: 3.0.13
    I don't think it has any thing to do with MySQL version, creation of INSERT trigger is not correct as I desscribe in the first post

    Leave a comment:


  • windy.jian
    replied
    percona server 5.7.25-28
    pt-osc: 3.0.13

    Leave a comment:


  • carlos.salguero
    replied
    Hello, one more question: could you tell me which MySQL version are you using?

    Leave a comment:


  • windy.jian
    replied
    Please run the sqls to reproduce the issue. Thanks for your time and effort so much.

    Leave a comment:


  • windy.jian
    replied
    DROP TABLE IF EXISTS data4jian;
    CREATE TABLE `data4jian` (
    `c_id` char(32) NOT NULL,
    `c_apply_stage_id` char(32) NOT NULL,
    `c_reason` varchar(500) DEFAULT NULL,
    `c_remark` varchar(2000) DEFAULT NULL,
    `c_crt_user` varchar(40) NOT NULL,
    `c_crt_name` varchar(40) NOT NULL,
    `c_crt_time` bigint(13) NOT NULL,
    `c_upd_user` varchar(40) NOT NULL,
    `c_upd_name` varchar(40) NOT NULL,
    `c_upd_time` bigint(13) NOT NULL,
    `c_is_del` tinyint(1) NOT NULL,
    `c_notify_tpl_id` char(32) DEFAULT NULL,
    `c_notify_time` bigint(13) DEFAULT NULL,
    `c_source` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    drop procedure if exists generatedata4jian;
    delimiter //
    create procedure generatedata4jian()
    begin
    declare i int;
    set i=0;
    while i<100000 do
    insert into data4jian(c_id,c_apply_stage_id,c_reason,c_remark, c_crt_user,c_crt_name,c_crt_time,c_upd_user,c_upd_ name,c_upd_time,c_is_del,c_notify_tpl_id,c_notify_ time,c_source) values (replace(uuid(),'-',''),'apply-stage-id','reason','remark','crt_user','crt_name',i,'upd _user','upd_name', i,i,'tpl_id',i,'source');
    set i=i+1;
    end while;
    end;//
    delimiter ;

    call generatedata4jian();

    perl /usr/bin/pt-online-schema-change --alter "ADD COLUMN c_apply_id varchar( 40) NOT NULL COMMENT '应聘ID' DEFAULT '', CHANGE c_apply_stage_id c_stage_id CHAR( 32) NOT NULL COMMENT '阶段ID' DEFAULT ''" --charset=utf8 --chunk-time 1 --critical-load Threads_connected:1300,Threads_running:120 --max-load Threads_connected:1100,Threads_running:100 --recurse=1 --check-interval 5 --no-check-replication-filters --no-check-alter --alter-foreign-keys-method=rebuild_constraints --execute --statistics --max-lag=3 --no-version-check --recursion-method=none --progress percentage,1 --user=root --password=root1234 --host=10.21.17.73 --port=23306 D=goinception,t=data4jian

    Leave a comment:


  • carlos.salguero
    replied
    I cannot reproduce the issue.
    Code:
    bin/pt-online-schema-change h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=tb_apply_eliminate --alter 'ADD COLUMN `c_apply_id` varchar( 40) NOT NULL DEFAULT "", CHANGE `c_apply_stage_id` `c_stage_id` CHAR( 32) NOT NULL DEFAULT ""' --execute --no-check-alter
    Found 2 slaves:
    HP-Omen -> 127.0.0.1:12346
    HP-Omen -> 127.0.0.1:12347
    Will check slave lag on:
    HP-Omen -> 127.0.0.1:12346
    HP-Omen -> 127.0.0.1:12347
    Operation, tries, wait:
    analyze_table, 10, 1
    copy_rows, 10, 0.25
    create_triggers, 10, 1
    drop_triggers, 10, 1
    swap_tables, 10, 1
    update_foreign_keys, 10, 1
    Altering `test`.`tb_apply_eliminate`...
    Renaming columns:
    c_apply_stage_id to c_stage_id
    Creating new table...
    Created new table test._tb_apply_eliminate_new OK.
    Altering new table...
    Altered `test`.`_tb_apply_eliminate_new` OK.
    2019-07-25T21:26:45 Creating triggers...
    2019-07-25T21:26:45 Created triggers OK.
    2019-07-25T21:26:45 Copying approximately 964 rows...
    2019-07-25T21:26:45 Copied rows OK.
    2019-07-25T21:26:45 Analyzing new table...
    2019-07-25T21:26:45 Swapping tables...
    2019-07-25T21:26:45 Swapped original and new tables OK.
    2019-07-25T21:26:45 Dropping old table...
    2019-07-25T21:26:45 Dropped old table `test`.`_tb_apply_eliminate_old` OK.
    2019-07-25T21:26:45 Dropping triggers...
    2019-07-25T21:26:45 Dropped triggers OK.
    Successfully altered `test`.`tb_apply_eliminate`.
    And after running the command I still see all the rows. The field (renamed) c_stage_id has data in all rows.

    Leave a comment:


  • windy.jian
    replied
    I just add a new column `c_apply_id`​​​​​​, and change the old column `c_apply_stage_id` to `c_stage_id`, there are three columns as described in the issue:
    c_apply_id: new added column
    c_apply_stage_id: to be renamed column
    c_stage_id: after renamed column.
    I think you mistake the columns' name in the ALTER SQL.

    Leave a comment:


  • carlos.salguero
    replied
    You are adding a column and at the same time renaming it?
    I am getting:

    Code:
    --alter appears to rename these columns:
    c_apply_stage_id to c_stage_id
    The tool should handle this correctly, but you should test it first because if it fails the renamed columns' data will be lost!  Specify --no-check-alter to disable this check and perform the --alter.
    `test`.`tb_apply_eliminate` was not altered.

    Leave a comment:


  • windy.jian
    replied
    DDL:
    CREATE TABLE `tb_apply_eliminate` (
    `c_id` char(32) NOT NULL,
    `c_apply_stage_id` char(32) NOT NULL,
    `c_reason` varchar(500) DEFAULT NULL,
    `c_remark` varchar(2000) DEFAULT NULL,
    `c_crt_user` varchar(40) NOT NULL,
    `c_crt_name` varchar(40) NOT NULL,
    `c_crt_time` bigint(13) NOT NULL,
    `c_upd_user` varchar(40) NOT NULL,
    `c_upd_name` varchar(40) NOT NULL,
    `c_upd_time` bigint(13) NOT NULL,
    `c_is_del` tinyint(1) NOT NULL,
    `c_notify_tpl_id` char(32) DEFAULT NULL,
    `c_notify_time` bigint(13) DEFAULT NULL,
    `c_source` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    ALTER TABLE tb_apply_eliminate
    ADD COLUMN `c_apply_id` varchar( 40) NOT NULL COMMENT '应聘ID' DEFAULT '',
    CHANGE `c_apply_stage_id` `c_stage_id` CHAR( 32) NOT NULL COMMENT '阶段ID' DEFAULT '';

    Leave a comment:


  • carlos.salguero
    replied
    Could you share your table structure?
    SHOW CREATE TABLE table_name.

    Leave a comment:


  • [Emergency] pt-online-schema-change makes column lose data

    1. ALTER TABLE xxx
    ADD COLUMN `c_apply_id` varchar( 40) NOT NULL DEFAULT '',
    CHANGE `c_apply_stage_id` `c_stage_id` CHAR( 32) NOT NULL DEFAULT '';
    2. pt_osc_xxx_ins doesn't select c_apply_stage_id column, so in the _xxx_new lose the data

    pt-tool: 3.0.13
    Last edited by windy.jian; 07-24-2019, 08:16 AM.
Working...
X