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

Polyglot Projects Percona Toolkit
1
  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

2

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

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 ‘’;

4

You are adding a column and at the same time renaming it?
I am getting:

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

6

I cannot reproduce the issue.

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) has data in all rows.

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

8

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

9

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

10

percona server 5.7.25-28
pt-osc: 3.0.13

11

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