tpam28 opened a new issue, #9194:
URL: https://github.com/apache/devlake/issues/9194
### Search before asking
- [x] I had searched in the issues and found no similar issues.
### What happened
On a fresh database, migration `20240124155129` ("modify refs id length from
255 to 500") fails on MySQL with Group Replication. DevLake doesn't start.
The migration calls `migrationhelper.ChangePrimaryKeyColumnsType`. On MySQL
that function:
1. runs `ALTER TABLE refs DROP PRIMARY KEY`;
2. renames `id` to a temp column, adds a new `id` column, and copies data
with `UPDATE refs SET id = <tmp> ...`;
3. runs `ALTER TABLE refs ADD PRIMARY KEY (id)`.
Group Replication requires every replicated table to have a primary key or
an equivalent non-null unique key
(https://dev.mysql.com/doc/refman/8.0/en/group-replication-requirements.html).
It rejects DML on tables without one, so step 2 fails even on an empty table:
```
[migrator] applying migration script modify refs id length from 255 to
500:20240124155129
dalgorm.go:286 Error 3098 (HY000): The table does not comply with the
requirements by an external plugin.
[rows:0] UPDATE `refs` SET `id`= `id_5F25450158E7FFB26E242E4DA0AE9F41`
,`updated_at`='...' WHERE `id_5F25450158E7FFB26E242E4DA0AE9F41` IS NOT NULL
panic: Error 3098 (HY000): The table does not comply with the requirements
by an external plugin. (500)
```
Step 3 never runs. The rollback in `ChangeColumnsType` restores the `id`
column but not the primary key, so `refs` stays without one. The next start
fails on the same migration, now at step 1:
```
panic: Error 1091 (42000): Can't DROP 'PRIMARY'; check that column/key
exists (500)
```
### What do you expect to happen
The migration succeeds on MySQL with Group Replication, and `refs` keeps its
primary key the whole time. Group Replication is what MySQL InnoDB Cluster runs
on, so DevLake can't use an HA MySQL setup while this migration drops the key
before copying data.
### How to reproduce
1. Start a single-node MySQL 8.0 with Group Replication:
```
cat > gr.cnf <<'EOF'
[mysqld]
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
plugin_load_add=group_replication.so
loose-group_replication_group_name=aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address=127.0.0.1:33061
loose-group_replication_group_seeds=127.0.0.1:33061
EOF
docker run -d --name devlake-gr -p 3307:3306 \
-e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=lake \
-e MYSQL_USER=merico -e MYSQL_PASSWORD=merico \
-v "$PWD/gr.cnf:/etc/mysql/conf.d/gr.cnf:ro" mysql:8.0
docker exec devlake-gr mysql -uroot -proot -e "SET GLOBAL
group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL
group_replication_bootstrap_group=OFF;"
```
2. Start DevLake with
`DB_URL=mysql://merico:[email protected]:3307/lake?charset=utf8mb4&parseTime=True&loc=UTC`
and `FORCE_MIGRATION=true`.
3. DevLake panics on migration `20240124155129` with error 3098.
4. `SHOW CREATE TABLE refs` shows no primary key. Starting DevLake again
panics with error 1091.
### Anything else
It happens every time on a fresh database. On plain MySQL without Group
Replication the migration passes.
Related: #6451 (Group Replication, missing primary keys on other tables;
closed as stale), #6292 (same drop-then-re-add pattern failing on Azure MySQL
with GIPK). Neither covers the `refs` migration.
Widening a varchar primary key doesn't need the drop/copy/re-add sequence.
On MySQL, `ALTER TABLE refs MODIFY id varchar(500)` keeps the key and the data.
`ChangePrimaryKeyColumnsType` is only used by this migration.
### Version
02dab0cda304298a2a47737ada4a616921b7c914 (current main); also seen on
b3f18854ad58b5cf0e9a56c14266af17ae19894f
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's Code of Conduct
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
