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

[BarShauli555]

Hi Guys, Im Bar From Wix DBA Team.
We had an old issue, that Shlomi created a fix for us and send us the binaries only, and not the code.
For now, we would like to use newer versions on gh-ost, and the change are not implemented at the new code.
Our Entire Mysql DB is working with generated unique key \ primary key, and this is a big issue for us.
Please review this ISSUE : [(https://github.com//issues/1000)]
It is still relevant.

I tested the newest version (master version) and also this version : #1461
and still the error persist.

ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery

Kindly please add this feature.

[meiji163]

Thanks for the report. Can you provide an example table/alter where you run into this error?

[BarShauli555]

Hi @meiji163 ,

Thank you for your quick response! We are experiencing the same issue as discussed in .Github Issue

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)

.Code Ref

[meiji163]

Thanks for the details @BarShauli555 . I could not reproduce the error with the example you gave.
Are you sure you are testing v1.1.7 or the master branch? The code you referenced is an old version. The unique key column check is now performed in NewDMLUpdateQueryBuilder, which is called during initialization .

[BarShauli555]

Hi @meiji163 Yes, Still the same error. From master branch. git clone, build and running the gh-ost file.

an example :

create database bar_ghost_test;
use bar_ghost_test; 
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"}');

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

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

ghost running command :

gh-ost --max-load=Threads_running=500 --critical-load=Threads_running=1000 --initially-drop-ghost-table --initially-drop-old-table --chunk-size=2 --max-lag-millis=3000 --user=test --password=test --assume-master-host=db-test.x.x.net --throttle-control-replicas=db-mysql-yosi-test0a.uw2-edt-1.wixprod.net --host=db-test2.x.x.net --database=bar_ghost_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

The result :

2025-03-09 08:19:16 INFO First throttle metrics collected
# Migrating `bar_ghost_test`.`test`; Ghost table is `bar_ghost_test`.`_test_gho`
# Migrating db-test.x.x.net:3306; inspecting db-test2.x.x.net:3306; executing on db-test.x.x.net
# Migration started at Sun Mar 09 08:19:12 +0000 2025
# chunk-size: 100; max-lag-millis: 3000ms; dml-batch-size: 10; max-load: Threads_running=500; critical-load: Threads_running=1000; nice-ratio: 0.000000
# throttle-additional-flag-file: /tmp/gh-ost.throttle
# throttle-control-replicas count: 1
# postpone-cut-over-flag-file: /tmp/ghost.postpone.flag [set]
# panic-flag-file: /tmp/ghost.panic.flag
# Serving on unix socket: /tmp/gh-ost.bar_ghost_test.test.sock
Copy: 0/3 0.0%; Applied: 0; Backlog: 0/1000; Time: 3s(total), 0s(copy); streamer: mysql-bin.000339:913479794; Lag: 0.15s, HeartbeatLag: 0.15s, State: migrating; ETA: N/A
2025-03-09 08:19:17 INFO Row copy complete
Copy: 0/3 0.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 1s(copy); streamer: mysql-bin.000339:913484501; Lag: 0.12s, HeartbeatLag: 0.16s, State: migrating; ETA: N/A
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 4s(total), 1s(copy); streamer: mysql-bin.000339:913484501; Lag: 0.12s, HeartbeatLag: 0.16s, State: migrating; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 5s(total), 1s(copy); streamer: mysql-bin.000339:913490424; Lag: 0.12s, HeartbeatLag: 0.16s, State: postponing cut-over; ETA: due
2025-03-09 08:19:19 ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery
Copy: 3/3 100.0%; Applied: 0; Backlog: 1/1000; Time: 6s(total), 1s(copy); streamer: mysql-bin.000339:913495705; Lag: 0.12s, HeartbeatLag: 0.16s, State: postponing cut-over; ETA: due
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 7s(total), 1s(copy); streamer: mysql-bin.000339:913500430; Lag: 0.12s, HeartbeatLag: 0.16s, State: postponing cut-over; ETA: due
2025-03-09 08:19:20 ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 8s(total), 1s(copy); streamer: mysql-bin.000339:913505154; Lag: 0.12s, HeartbeatLag: 0.16s, State: postponing cut-over; ETA: due
2025-03-09 08:19:21 ERROR unique key columns is not a subset of shared columns in BuildDMLUpdateQuery
Copy: 3/3 100.0%; Applied: 0; Backlog: 0/1000; Time: 9s(total), 1s(copy); streamer: mysql-bin.000339:913509876; Lag: 0.12s, HeartbeatLag: 0.16s, State: postponing cut-over; ETA: due