gh-ost tries to update generated columns
- 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
Assessment
This issue has not been assessed yet.