chenwei791129 opened a new issue, #9241: URL: https://github.com/apache/devlake/issues/9241
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar issues. ### What happened Reported in apache/devlake-helm-chart#381 (DevLake v1.0.3-beta15, PostgreSQL 18, official Helm chart, external `DB_URL`). DevLake panics at startup during migration: ```text modify _tool_sonarqube_file_metrics file_metrics_key from varchar(191) to varchar(500) ERROR: constraint "_tool_sonarqube_file_metrics_pkey" of relation "_tool_sonarqube_file_metrics" does not exist (SQLSTATE 42704) ``` The failing statement is the first step of migration `20230927145127` (`backend/plugins/sonarqube/models/migrationscripts/20230927_modify_file_metrics_key_length.go`). `Up()` does: 1. Drop the primary key without checking that it exists: - MySQL: `ALTER TABLE _tool_sonarqube_file_metrics DROP PRIMARY KEY` - otherwise (PostgreSQL): `ALTER TABLE _tool_sonarqube_file_metrics DROP CONSTRAINT _tool_sonarqube_file_metrics_pkey` 2. `migrationhelper.ChangeColumnsType` (rename / add / copy / drop column) 3. `ALTER TABLE _tool_sonarqube_file_metrics ADD PRIMARY KEY (connection_id, file_metrics_key)` `migrator.Execute()` (`backend/core/migration/migrator.go`) runs scripts one by one, not in a transaction, and writes the `_devlake_migration_history` row only after `Up()` succeeds. If the process stops after step 1 but before the history row is written, the next startup runs the script again. Step 1 then fails because the primary key is already gone, and every later startup fails the same way: ```text 1st start: DROP pkey OK ──▶ interrupted / failed ──▶ no history row 2nd start: script re-runs ──▶ DROP pkey fails: constraint does not exist ──▶ panic ``` **MySQL is affected too.** The helm-chart report is about PostgreSQL, but while investigating it I found that the MySQL branch has the same problem. MySQL has no `IF EXISTS` form of `DROP PRIMARY KEY`, so after the same kind of interruption the re-run fails with: ```text Error 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists ``` I confirmed this on MySQL 8 with the same simulated interruption (see below). The second error in that report (migration `20260701000000` dropping `idx__tool_sonarqube_issue_code_blocks_component`) was already fixed by #9012 (closes #9011, see also #9033), included since v1.0.3-beta16. It is out of scope here. ### What do you expect to happen Migration `20230927145127` should be safe to re-run. If the primary key is already gone, it should skip the drop and still end with `PRIMARY KEY (connection_id, file_metrics_key)`, so DevLake can start. ### How to reproduce A truly fresh database does **not** trigger this. With PostgreSQL 18 and the official `apache/devlake:v1.0.3-beta15` image (all plugins, `FORCE_MIGRATION=true`), `20230927145127` succeeds and the primary key exists. Only the already-fixed `20260701000000` error shows up. To reproduce, simulate a run that was interrupted right after the drop: 1. Start DevLake once against PostgreSQL so that migration `20230927145127` has run. 2. Run: ```sql ALTER TABLE _tool_sonarqube_file_metrics DROP CONSTRAINT _tool_sonarqube_file_metrics_pkey; DELETE FROM _devlake_migration_history WHERE script_version = 20230927145127; ``` 3. Restart DevLake with `FORCE_MIGRATION=true`. 4. Migration fails with the same `constraint "_tool_sonarqube_file_metrics_pkey" ... does not exist (SQLSTATE 42704)` error and the server panics. On MySQL, use `ALTER TABLE _tool_sonarqube_file_metrics DROP PRIMARY KEY;` in step 2. Step 4 then fails with `Error 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists`. ### Anything else **Possible trigger in the Helm setup** (a hypothesis, not confirmed) - With `FORCE_MIGRATION`, `CreateAndRunApiServer` (`backend/server/api/api.go`) runs `services.InitExecuteMigration()` before `RunApiServer` starts listening. - The chart's default lake `livenessProbe` is `httpGet /ping` with `initialDelaySeconds: 30`, `periodSeconds: 5`, `failureThreshold: 5`. A slow migration against a remote database can get the pod killed and restarted in the middle of it. - The panic from the `20260701000000` error also causes CrashLoop restarts. Any interruption while this script is running leaves the table without a primary key. **Proposed fix** (PR to follow) Make the script idempotent on both databases by dropping the primary key only if it exists: - PostgreSQL: `ALTER TABLE ... DROP CONSTRAINT IF EXISTS _tool_sonarqube_file_metrics_pkey` - MySQL: check `information_schema.table_constraints` for an existing primary key before `DROP PRIMARY KEY` The normal path is unchanged. The PR adds an e2e regression test that covers the normal run and a re-run without the primary key. It fails without the fix on both MySQL 8 and PostgreSQL 18, and passes with it. The script has not changed since #6157 and #6196 (2023). ### Version v1.0.3-beta15 (the script is unchanged on `main` at ee0079ab3) ### 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](https://www.apache.org/foundation/policies/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]
