Buy Percona ServicesBuy Now!

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

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

  • [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.

  • #2
    Could you share your table structure?
    SHOW CREATE TABLE table_name.

    Comment


    • #3
      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 '';

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment


              • #8
                Please run the sqls to reproduce the issue. Thanks for your time and effort so much.

                Comment


                • #9
                  Hello, one more question: could you tell me which MySQL version are you using?

                  Comment


                  • #10
                    percona server 5.7.25-28
                    pt-osc: 3.0.13

                    Comment


                    • #11
                      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

                      Comment

                      Working...
                      X