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

**URL:** <https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130>\
**Category:** Percona Toolkit\
**Created:** [July 24, 2019, 7:35am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130 "2019-07-24T07:35:08Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 24, 2019, 7:35am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/1 "2019-07-24T07:35:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [July 24, 2019, 8:51am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/2 "2019-07-24T08:51:58Z")

</div>

Could you share your table structure?  
SHOW CREATE TABLE table\_name.

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 24, 2019, 9:28am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/3 "2019-07-24T09:28:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [July 24, 2019, 10:15am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/4 "2019-07-24T10:15:43Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 24, 2019, 6:47pm UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/5 "2019-07-24T18:47:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [July 25, 2019, 7:01pm UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/6 "2019-07-25T19:01:58Z")

</div>

I cannot reproduce the issue.

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

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 26, 2019, 8:09am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/7 "2019-07-26T08:09:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 26, 2019, 8:11am UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/8 "2019-07-26T08:11:35Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![carlos.salguero](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/carlos.salguero/32/20_2.png) [@carlos.salguero](https://forums.percona.com/u/carlos.salguero)\
**Post date:** [July 26, 2019, 4:19pm UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/9 "2019-07-26T16:19:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 26, 2019, 7:59pm UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/10 "2019-07-26T19:59:41Z")

</div>

percona server 5.7.25-28  
pt-osc: 3.0.13

---

<div class="post-metadata">

**Author:** ![windy.jian](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@windy.jian](https://forums.percona.com/u/windy.jian)\
**Post date:** [July 26, 2019, 9:24pm UTC](https://forums.percona.com/t/emergency-pt-online-schema-change-makes-column-lose-data/7130/11 "2019-07-26T21:24:11Z")

</div>

> [@windy.jian;54388](#):
>
> 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