Skip to content

gh-ost lose data with UNIQUE KEY #1526

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
RankRao opened this issue Apr 7, 2025 · 1 comment
Open

gh-ost lose data with UNIQUE KEY #1526

RankRao opened this issue Apr 7, 2025 · 1 comment

Comments

@RankRao
Copy link

RankRao commented Apr 7, 2025

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 |
+----+---------+---------------------+--------------+

@meiji163
Copy link
Contributor

meiji163 commented Apr 23, 2025

We added --panic-on-warnings to panic gh-ost if it detects Duplicate entry for unique key errors like this during the copy process #1500

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants