github / github/gh-ost

gh-ost tries to update generated columns

Open
#138 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

If the old table has a generated column then `gh-ost` tries to insert on the generated column on the new table.
It does not detect the error and tries to continue.

The solution is:
1. detect the failure and stop
2. skip generated columns on insert to avoid the error
3. Get Oracle to add an option to MySQL to blackhole inserts on generated columns (This is wrong..)

Example table:

``` SQL
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`yearly_per_year` int(11) GENERATED ALWAYS AS ((`salary` * 12)) VIRTUAL,
PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

Example output

```
$ sudo rm -rf /tmp/gh-ost.employees.salaries.sock; /usr/lib/gh-ost/gh-ost/gh-ost -table salaries -throttle-control-replicas replica1,replica2 -alter "ADD COLUMN test1 tinyint unsigned DEFAULT NULL" -conf /home/dvaneeden/.my_gh-ost.cnf -database employees -host $(hostname -f) -initially-drop-old-table -initially-drop-ghost-table -execute -test-on-replica
# Migrating `employees`.`salaries`; Ghost table is `employees`.`_salaries_gho`
# Migrating dvaneeden-test-2002.example.com:3306; inspecting dvaneeden-test-2002.example.com:3306; executing on dvaneeden-test-2002.example.com
# Migration started at Fri Aug 05 13:32:35 +0000 2016
# chunk-size: 1000; max-lag-millis: 1500ms; max-load: ; critical-load: ; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# Serving on unix socket: /tmp/gh-ost.employees.salaries.sock
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 0/100; Time: 0s(total), 0s(copy); streamer: binlog.000017:100461102; ETA: N/A
2016-08-05 13:32:36 ERROR Error 3105: The value specified for generated column 'yearly_per_year' in table '_salaries_gho' is not allowed.
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: binlog.000017:100463211; ETA: N/A
2016-08-05 13:32:36 ERROR Error 3105: The value specified for generated column 'yearly_per_year' in table '_salaries_gho' is not allowed.
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 0/100; Time: 1s(total), 1s(copy); streamer: binlog.000017:100463211; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 3s(total), 1s(copy); streamer: binlog.000017:100465895; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 4s(total), 1s(copy); streamer: binlog.000017:100466739; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 5s(total), 1s(copy); streamer: binlog.000017:100467583; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 6s(total), 1s(copy); streamer: binlog.000017:100468427; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 7s(total), 1s(copy); streamer: binlog.000017:100469271; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 8s(total), 1s(copy); streamer: binlog.000017:100470115; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 9s(total), 1s(copy); streamer: binlog.000017:100470959; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 10s(total), 1s(copy); streamer: binlog.000017:100471803; ETA: N/A
^CCopy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 11s(total), 1s(copy); streamer: binlog.000017:100472648; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 12s(total), 1s(copy); streamer: binlog.000017:100473493; ETA: N/A
Copy: 0/2838426 0.0%; Applied: 0; Backlog: 1/100; Time: 13s(total), 1s(copy); streamer: binlog.000017:100474338; ETA: N/A
^C
```

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.