gh-ost tries to update generated columns

[dveeden]

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:

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

[shlomi-noach]

Yes, this will be a bit lengthier to handle; we may eventually also need to consider what happens when the alter statement changes a generated column into a non generated column. I need to wrap my head around this, but this sounds non-trivial.

Any thoughts on possible scenarios is appreciated!

[druud62]

Anecdotal virtual-column trick:

Yesterday, and mainly for fun, I used (a tweaked) pt-osc to shrink and optimize a table in one run. (the tweak was: an extra grep to keep the virtual column out of @common_columns)

To prepare the pt-osc run, I first added a virtual column AS (IF(<condition>, NULL, id)), and then added a UNIQUE index on it. The condition is true for rows that were old and no longer wanted.

I had to do that in 2 steps, to be able to use standard online-alters (LOCK=NONE, ALGORITHM=INPLACE).

Adding the index takes a few seconds per 1M rows, and would pause single-threaded replication, so be careful there.

Then I used pt-osc to alter the column to AS (IF(<condition>, 0, id)), to make all no longer wanted rows collide on that zero (via the new restrictive UNIQUE index).

Because pt-osc uses INSERT IGNORE to copy rows, only one of the (millions of) unwanted rows stays in the table. Don't forget to manually delete that one old row.

To finish, it needs 2 standard online alters again: one to drop the index, and then one to drop the virtual column.