github / github/gh-ost

data loss caused by upper case letter: migrating with gh-ost 1.0.30 and 1.0.36

Open
#441 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

#### Operation:
both 1.0.30 and 1.0.36

```
/usr/bin/gh-ost --max-load=Threads_running=32 --critical-load=Threads_running=32 --chunk-size=1000 --throttle-control-replicas=SLAVE_IP:PORT --max-lag-millis=1000 --initially-drop-old-table --initially-drop-ghost-table --ok-to-drop-table --conf=/home/sankuai/dbpass_center/superadmin.cnf --host=SLAVE_IP --port=PORT --database=db_name --table=TR_promo_record --debug --switch-to-rbr --allow-master-master --cut-over=default --default-retries=3 --critical-load-interval-millis=3000 --panic-flag-file=/tmp/ghost.panic.flag --postpone-cut-over-flag-file=/tmp/ghost.postpone.flag --execute --alter="modify orderID bigInt(20) unsigned DEFAULT '0' COMMENT '订单ID'"
```

#### got some data loss when migrating is finished

before:
+----------+
| count(*) |
+----------+
| 100 |
+----------+

after:
+----------+
| count(*) |
+----------+
| 1 |
+----------+

#### running log:
```
Copy: 1/1 100.0%; Applied: 0; Backlog: 1/100; Time: 2s(total), 1s(copy); streamer: mysql-bin.000199:794294927; State: migrating; ETA: due
2017-07-05 16:29:17 DEBUG Getting nothing in the write queue. Sleeping...
2017-07-05 16:29:17 INFO Waiting for events up to lock: got AllEventsUpToLockProcessed:1499243356362835071
2017-07-05 16:29:17 INFO Done waiting for events up to lock; duration=980.888669ms
# Migrating `sbtest`.`TR_promo_record`; Ghost table is `sbtest`.`_TR_promo_record_gho`
# Migration started at Wed Jul 05 16:29:15 +0800 2017
# chunk-size: 1000; max-lag-millis: 1000ms; max-load: Threads_running=32; critical-load: Threads_running=32; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# postpone-cut-over-flag-file: /tmp/ghost.postpone.flag
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.sbtest.TR_promo_record.sock
Copy: 1/1 100.0%; Applied: 0; Backlog: 0/100; Time: 2s(total), 1s(copy); streamer: mysql-bin.000199:794295756; State: migrating; ETA: due
2017-07-05 16:29:17 INFO Setting RENAME timeout as 3 seconds
2017-07-05 16:29:17 INFO Session renaming tables is 127381
2017-07-05 16:29:17 INFO Issuing and expecting this to block: rename /* gh-ost */ table `sbtest`.`TR_promo_record` to `sbtest`.`_TR_promo_record_del`, `sbtest`.`_TR_promo_record_gho` to `sbtest`.`TR_promo_record`
2017-07-05 16:29:17 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2017-07-05 16:29:17 INFO Checking session lock: gh-ost.127383.lock
2017-07-05 16:29:17 INFO Connection holding lock on original table still exists
2017-07-05 16:29:17 INFO Will now proceed to drop magic table and unlock tables
2017-07-05 16:29:17 INFO Dropping magic cut-over table
2017-07-05 16:29:17 INFO Releasing lock from `sbtest`.`TR_promo_record`, `sbtest`.`_TR_promo_record_del`
2017-07-05 16:29
```

#### table defination
```
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| orderID | bigint(20) unsigned | YES | MUL | 0 | |
| promoAmount | decimal(10,3) | YES | | 0.000 | |
| promoType | int(11) | YES | | 0 | |
| promoId | varchar(50) | YES | | 0 | |
| note | varchar(1000) | YES | | NULL | |
| addTime | datetime | YES | | NULL | |
| updateTime | datetime | YES | MUL | NULL | |
| promoName | varchar(128) | YES | | NULL | |
| bearType | tinyint(4) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
Create Table: CREATE TABLE `TR_promo_record` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`orderID` bigint(20) unsigned DEFAULT '0' COMMENT '订单ID',
`promoAmount` decimal(10,3) DEFAULT '0.000' COMMENT '优惠金额',
`promoType` int(11) DEFAULT '0' COMMENT '优惠类型,1表示宙斯立减,2表示抵用券',
`promoId` varchar(50) DEFAULT '0' COMMENT '优惠id,立减是discountId,抵用券是couponId',
`note` varchar(1000) DEFAULT NULL COMMENT '备注',
`addTime` datetime DEFAULT NULL COMMENT '记录添加时间',
`updateTime` datetime DEFAULT NULL COMMENT '更新时间',
`promoName` varchar(128) DEFAULT NULL COMMENT '活动名称',
`bearType` tinyint(4) DEFAULT NULL COMMENT '1 新美大承担;2是商家;',
PRIMARY KEY (`id`),
UNIQUE KEY `UK_OrderID_promoType` (`orderID`,`promoType`),
KEY `IX_updateTime` (`updateTime`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 COMMENT='旅游订单优惠记录表'
```

#### also got a mysqldump file test_data_loss_sql.txt like
check the attach
[test_data_loss_sql.txt](https://github.com/github/gh-ost/files/1124462/test_data_loss_sql.txt)

#### Would you please reivew the problem

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.