gh-ost is stuck forever & data loss for BIT column in primary key

[Dasiu]

$ gh-ost --version
1.1.6

Scenario 1 -- gh-ost is stuck forever

Consider:

CREATE DATABASE  IF NOT EXISTS `ghost_test`;
USE `ghost_test`;
CREATE TABLE `ghost_bit_test` (
  `id` bigint NOT NULL,
  `bit` bit(1) NOT NULL,
  PRIMARY KEY (`id`,`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `ghost_bit_test` VALUES (1, b'1'),(2, b'1');

and

gh-ost --alter " alter table ghost_test.ghost_bit_test engine innodb " --allow-on-master --initially-drop-socket-file --initially-drop-ghost-table --execute --debug --verbose

gh-ost outputs:

2024-12-18 08:46:21 DEBUG Issued INSERT on range: [1,]..[1,]; iteration: 1605; chunk-size: 1000
2024-12-18 08:46:21 DEBUG Issued INSERT on range: [1,]..[1,]; iteration: 1606; chunk-size: 1000
2024-12-18 08:46:21 DEBUG Issued INSERT on range: [1,]..[1,]; iteration: 1607; chunk-size: 1000
2024-12-18 08:46:21 DEBUG Issued INSERT on range: [1,]..[1,]; iteration: 1608; chunk-size: 1000
2024-12-18 08:46:21 DEBUG Issued INSERT on range: [1,]..[1,]; iteration: 1609; chunk-size: 1000
[...]

Scenario 2 -- gh-ost loses a row

Consider:

CREATE DATABASE  IF NOT EXISTS `ghost_test`;
USE `ghost_test`;
CREATE TABLE `ghost_bit_test` (
  `id` bigint NOT NULL,
  `bit` bit(1) NOT NULL,
  PRIMARY KEY (`id`,`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `ghost_bit_test` VALUES (1, b'1');

and

gh-ost --alter " alter table ghost_test.ghost_bit_test engine innodb " --allow-on-master --initially-drop-socket-file --initially-drop-ghost-table --execute --debug --verbose

gh-ost outputs:

Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 42s(total), 1s(copy); streamer: binlog.000002:5976849; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2024-12-18 08:49:07 INFO Copy: 0/0 100.0%!;(MISSING) Applied: 0; Backlog: 0/1000; Time: 42s(total), 1s(copy); streamer: binlog.000002:5976849; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
Copy: 0/0 100.0%; Applied: 0; Backlog: 0/1000; Time: 42s(total), 1s(copy); streamer: binlog.000002:5976849; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2024-12-18 08:49:07 INFO Copy: 0/0 100.0%!;(MISSING) Applied: 0; Backlog: 0/1000; Time: 42s(total), 1s(copy); streamer: binlog.000002:5976849; Lag: 0.01s, HeartbeatLag: 0.01s, State: migrating; ETA: due
2024-12-18 08:49:07 INFO Setting RENAME timeout as 3 seconds
2024-12-18 08:49:07 INFO Session renaming tables is 11301
2024-12-18 08:49:07 INFO Issuing and expecting this to block: rename /* gh-ost */ table `ghost_test`.`ghost_bit_test` to `ghost_test`.`_ghost_bit_test_del`, `ghost_test`.`_ghost_bit_test_gho` to `ghost_test`.`ghost_bit_test`
2024-12-18 08:49:07 INFO Found atomic RENAME to be blocking, as expected. Double checking the lock is still in place (though I don't strictly have to)
2024-12-18 08:49:07 INFO Checking session lock: gh-ost.11289.lock
2024-12-18 08:49:07 INFO Connection holding lock on original table still exists
2024-12-18 08:49:07 INFO Will now proceed to drop magic table and unlock tables
2024-12-18 08:49:07 INFO Dropping magic cut-over table
2024-12-18 08:49:07 INFO Releasing lock from `ghost_test`.`ghost_bit_test`, `ghost_test`.`_ghost_bit_test_del`
2024-12-18 08:49:07 INFO Tables unlocked
2024-12-18 08:49:07 INFO Looking for magic cut-over table
2024-12-18 08:49:07 INFO Tables renamed
2024-12-18 08:49:07 INFO Lock & rename duration: 1.014217921s. During this time, queries on `ghost_bit_test` were blocked
2024-12-18 08:49:07 INFO Writing changelog state: Migrated
2024-12-18 08:49:07 DEBUG done streaming events
2024-12-18 08:49:07 DEBUG Done streaming
[2024/12/18 08:49:07] [info] binlogsyncer.go:180 syncer is closing...
[2024/12/18 08:49:07] [info] binlogsyncer.go:864 kill last connection id 11298
[2024/12/18 08:49:07] [info] binlogsyncer.go:210 syncer is closed
2024-12-18 08:49:07 INFO Closed streamer connection. err=<nil>
2024-12-18 08:49:07 INFO Dropping table `ghost_test`.`_ghost_bit_test_ghc`
2024-12-18 08:49:07 INFO Table dropped
2024-12-18 08:49:07 INFO Am not dropping old table because I want this operation to be as live as possible. If you insist I should do it, please add `--ok-to-drop-table` next time. But I prefer you do not. To drop the old table, issue:
2024-12-18 08:49:07 INFO -- drop table `ghost_test`.`_ghost_bit_test_del`
2024-12-18 08:49:07 INFO Done migrating `ghost_test`.`ghost_bit_test`
2024-12-18 08:49:07 INFO Removing socket file: /tmp/gh-ost.ghost_test.ghost_bit_test.sock
2024-12-18 08:49:07 INFO Tearing down inspector
2024-12-18 08:49:07 INFO Tearing down applier
2024-12-18 08:49:07 DEBUG Tearing down...
2024-12-18 08:49:07 INFO Tearing down streamer
2024-12-18 08:49:07 INFO Tearing down throttler
2024-12-18 08:49:07 DEBUG Tearing down...
# Done

Row is missing in migrated table (it is present in the old table).

---

I suspect the problem stems from the fact how BIT column is handled in the query gh-ost executes:

insert /* gh-ost `ghost_test`.`ghost_bit_test` */ ignore into `ghost_test`.`_ghost_bit_test_gho` (`id`, `bit`)
      (select `id`, `bit` from `ghost_test`.`ghost_bit_test` force index (`PRIMARY`)
        where (((`id` > _binary'1') or (((`id` = _binary'1')) AND (`bit` > _binary'�'))) and ((`id` < _binary'1') or (((`id` = _binary'1')) AND (`bit` < _binary'�')) or ((`id` = _binary'1') and (`bit` = _binary'�')))) lock in share mode
      ) /* statement may be truncated */

To my understanding _binary returns binary string whereas MySQL expects numeric context and comparison fails. ?

[meiji163]

Thanks for the bug report, I was able to reproduce the error but got a slightly different execution statement. Which MySQL version are you testing on? This is 8.0.39:

insert /* gh-ost `test`.`gh_ost_test` */ ignore
                into
                        `test`.`_gh_ost_test_gho`
                        (`id`, `is_good`)
                (
                        select `id`, `is_good`
                        from
                                `test`.`gh_ost_test`
                        force index (`PRIMARY`)
                        where
                                (((`id` > 2) or (((`id` = 2)) AND (`bit` > _binary''))) and ((`id` < 2) or (((`id` = 2)) AND (`bit` < _binary'')) or ((`id` = 2) and (`bit` = _binary''))))
                                for share nowait
                )                                                                                                                                                

Inspecting the query argument values in ApplyIterationInsertQuery, the `bit` argument is []uint8{ 1 } which seems correct. So I'm thinking there's something wrong with how go-mysql is serializing the value