You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
CREATETABLE `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-0200:00:00 | 10 |
| 2 | Google | 2025-04-0200:00:00 | 100 |
+----+---------+---------------------+--------------+altertable 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 |
+----+---------+---------------------+--------------+
The text was updated successfully, but these errors were encountered:
MySQL version: 5.7.30
gh-ost version: 1.1.7
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 |
+----+---------+---------------------+--------------+
The text was updated successfully, but these errors were encountered: