stored generated columns can not be modified to regular fields
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
Running v1.0.48 (also reproduced on v1.0.49)
Server version: 5.7.24-log MySQL Community Server (GPL)
- Problem:
Migration through the ghost tool, causing the entire column of data to be lost。
- Reproducing:
- ---
Let's prepare the initial environment:
create database mydb;
use mydb;
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`chinese` double DEFAULT NULL,
`math` double NOT NULL DEFAULT '0',
`english` double NOT NULL DEFAULT '0',
`total_score` double GENERATED ALWAYS AS (((`chinese` + `math`) + `english`)) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into mydb.test(chinese, math, english) values(66, 72, 54);
insert into mydb.test(chinese, math, english) values(33, 44, 55);
select * from mydb.test;
+----+---------+------+---------+-------------+
| id | chinese | math | english | total_score |
+----+---------+------+---------+-------------+
| 1 | 66 | 72 | 54 | 192 |
| 2 | 33 | 44 | 55 | 132 |
+----+---------+------+---------+-------------+
alter table test modify column total_score double;
select * from mydb.test;
+----+---------+------+---------+-------------+
| id | chinese | math | english | total_score |
+----+---------+------+---------+-------------+
| 1 | 66 | 72 | 54 | 192 |
| 2 | 33 | 44 | 55 | 132 |
+----+---------+------+---------+-------------+
In the first case,Performing a native ddl operation, we get the desired result.The data for the total_score column is correct.
Now let's reinitialize the environment:
use mydb;
drop table test;
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`chinese` double DEFAULT NULL,
`math` double NOT NULL DEFAULT '0',
`english` double NOT NULL DEFAULT '0',
`total_score` double GENERATED ALWAYS AS (((`chinese` + `math`) + `english`)) STORED,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into mydb.test(chinese, math, english) values(66, 72, 54);
insert into mydb.test(chinese, math, english) values(33, 44, 55);
select * from mydb.test;
+----+---------+------+---------+-------------+
| id | chinese | math | english | total_score |
+----+---------+------+---------+-------------+
| 1 | 66 | 72 | 54 | 192 |
| 2 | 33 | 44 | 55 | 132 |
+----+---------+------+---------+-------------+
In the second case, we use the ghost tool to perform the migration. The execution command is as follows:
“”gh-ost \
--user="root" \
--ask-pass \
--max-load=Threads_running=100 \
--critical-load=Threads_running=1000 \
--critical-load-interval-millis=3000 \
--critical-load-hibernate-seconds=600 \
--chunk-size=1000 \
--max-lag-millis=1000 \
--host="192.168.1.42" \
--port=3307 \
--database="mydb" \
--table="test" \
--alter="modify column total_score double" \
--verbose \
--assume-rbr \
--allow-on-master \
--assume-master-host=192.168.1.42:3307 \
--heartbeat-interval-millis=100 \
--timestamp-old-table \
--cut-over=default \
--default-retries=1000 \
--concurrent-rowcount \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
--serve-socket-file=/tmp/ghost.sock \
--replica-server-id=99999 \
--execute“”
Let's look at the results, we lost the entire column of total_score.
select * from mydb.test;
+----+---------+------+---------+-------------+
| id | chinese | math | english | total_score |
+----+---------+------+---------+-------------+
| 1 | 66 | 72 | 54 | NULL |
| 2 | 33 | 44 | 55 | NULL |
+----+---------+------+---------+-------------+
- ---
How should we solve this problem, maybe a small bug?
Contributor guide
Assessment
This issue has not been assessed yet.