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