Skip to content

gh-ost lose data with UNIQUE KEY #1526

Open
@RankRao

Description

@RankRao

MySQL version: 5.7.30
gh-ost version: 1.1.7

CREATE TABLE `temp_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `ddate` datetime NOT NULL,
  `regUserCount` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`channel`,`ddate`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into temp_table(channel,ddate,regUserCount) values('google','2025-04-02',10),('Google','2025-04-02',100);

select * from temp_table;
+----+---------+---------------------+--------------+
| id | channel | ddate               | regUserCount |
+----+---------+---------------------+--------------+
|  1 | google  | 2025-04-02 00:00:00 |           10 |
|  2 | Google  | 2025-04-02 00:00:00 |          100 |
+----+---------+---------------------+--------------+

alter table temp_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1062 (23000): Duplicate entry 'Google-2025-04-02 00:00:00' for key 'unique'

if we use gh-ost to alter table, it works success, but lose data.
in row-copy process, it have use like: insert ignore into _temp_table_gho select * from temp_table lock in share mode;
then the UNIQUE KEY for channel column have been ignore, then lose data.

gh-ost --ok-to-drop-table --initially-drop-ghost-table --initially-drop-socket-file --host="127.0.0.1" --port=3306 --user="xx" --password="xx" --database="test" --table="temp_table" --verbose --alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" --panic-flag-file=/tmp/ghost.panic.flag --allow-on-master --throttle-flag-file /tmp/1.log --execute

mysql> select * from temp_table;
+----+---------+---------------------+--------------+
| id | channel | ddate | regUserCount |
+----+---------+---------------------+--------------+
| 1 | google | 2025-04-02 00:00:00 | 10 |
+----+---------+---------------------+--------------+

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions