unique key columns is not a subset of shared columns in BuildDMLUpdateQuery on JSON table

[ykalfon]

Hi

we run gh-ost on MySQL 8.0.22 and got the following error when an UPDATE statement is performed on the table while ghost is running:
ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery

This is the scenario:

CREATE TABLE `test` (
  `main_id` varbinary(16) NOT NULL,
  `sub_id` varchar(36) CHARACTER SET latin1 COLLATE latin1_swedish_ci GENERATED ALWAYS AS (jsonbody->>'$._id') STORED NOT NULL,
  `jsonbody` json NOT NULL,
  PRIMARY KEY (`main_id`,`sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED

INSERT INTO test (main_id, jsonbody) 
VALUES (x'F2C45C49175845FCB062D579DFB6CE1D','{"_id":"c1192a0c-b1b8-4b86-adb5-19f40a869f3f","name":"apple"}');

while gh-ost is running, run any update on the table, for example:

UPDATE test
  SET jsonbody=JSON_OBJECT('_id', 'c1192a0c-b1b8-4b86-adb5-19f40a869f3f', 'name', 'carrot')
WHERE main_id=x'F2C45C49175845FCB062D579DFB6CE1D'
  AND sub_id='c1192a0c-b1b8-4b86-adb5-19f40a869f3f';

and gh-ost throws:
ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery

The gh-st command that was run is:

/usr/bin/gh-ost  
--max-load=Threads_running=500  
--critical-load=Threads_running=1000  
--initially-drop-ghost-table  
--initially-drop-old-table  
--chunk-size=600  
--max-lag-millis=3000  
--user=XXXX --password=XXXXX 
--assume-master-host=rw_master_1 
--throttle-control-replicas=ro_co-master_2 
--host=ro_2 
--database=test --table=test 
--verbose  
--alter="modify sub_id VARCHAR(36) CHARACTER SET latin1 COLLATE latin1_general_ci GENERATED ALWAYS AS (jsonbody->>'$._id') STORED NOT NULL"  
--assume-rbr  
--allow-master-master  
--cut-over=default  
--concurrent-rowcount  
--default-retries=120  
--panic-flag-file=/tmp/ghost.panic.flag  
--exact-rowcount  
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag  
--hooks-path=/opt/mysql/ghostWebHooks   
--execute > gh-ost.test.test.log

It seems to be here:
if !uniqueKeyColumns.IsSubsetOf(sharedColumns)

gh-ost/go/sql/builder.go

Line 474 in 47d49c6

if !uniqueKeyColumns.IsSubsetOf(sharedColumns) {

[ykalfon]

In this case:
uniqueKeyColumns = [main_id, sub_id]
sharedColumns = [main_id, jsonbody]

[barucho]

Hi
@shlomi-noach -
we think this issue is related to
#916 and
openark#13

[shlomi-noach]

All, I'm gonna try looking into it, but this isn't on my immediate priorities as I have other tasks on my list, so please understand if this takes a while.

I created openark#26 to investigate. However, I'm blocked on an unexpected CI error described in #1001

If anyone wants to investigate either of the two issues, please feel free.

[shlomi-noach]

I think the solution is relatively straighforward. Still blocked on that CI failure though, #1001

[shlomi-noach]

Please see openark#26 and test artifact https://github.com/openark/gh-ost/suites/3189193666/artifacts/73602792

[ykalfon]

Hi Shlomi,

First, I appreciate your effort and dedication, Thank you!
Second, it works !!

Shabat Shalom

[shlomi-noach]

Fixed downstream by openark#26

Submitted fix upstream as #1004

[meiji163]

Fixed by #1461