chenwei791129 opened a new pull request, #9242:
URL: https://github.com/apache/devlake/pull/9242
### Summary
SonarQube migration `20230927145127` fails on every startup if an earlier
run of it was cut off partway. This PR
makes the migration safe to run again.
**What goes wrong today**
The migration does three things in order:
1. Remove the primary key of `_tool_sonarqube_file_metrics`
2. Make the `file_metrics_key` column longer (191 → 500)
3. Add the primary key back
DevLake records a migration as done only after all three steps succeed, and
the steps are not in a transaction.
If DevLake stops after step 1 (for example, a pod restart), the table is
left with no primary key and the
migration is still marked as not done. On the next start it runs again, step
1 tries to remove a key that no
longer exists, and DevLake crashes:
- PostgreSQL: `constraint "_tool_sonarqube_file_metrics_pkey" ... does not
exist (SQLSTATE 42704)`
- MySQL: `Error 1091 (42000): Can't DROP 'PRIMARY'; check that column/key
exists`
DevLake then cannot start until someone fixes the database by hand.
**The fix**
Step 1 now removes the primary key only if it is still there:
- PostgreSQL: `DROP CONSTRAINT IF EXISTS`
- MySQL: there is no `IF EXISTS` form of `DROP PRIMARY KEY`, so it first
checks
`information_schema.table_constraints`
Steps 2 and 3 are unchanged. On a normal run the key exists, so the
migration behaves exactly as before. After an
interrupted run, it skips the removal and still ends with `PRIMARY KEY
(connection_id, file_metrics_key)`.
The issue was reported for PostgreSQL. While investigating it I found that
MySQL has the same problem, so both are
fixed here.
### Does this close any open issues?
Closes #9241
### Screenshots
N/A. Database migration only, no UI changes.
### Other Information
- Originally reported in apache/devlake-helm-chart#381. The other error in
that report
(`idx__tool_sonarqube_issue_code_blocks_component`) was already fixed by
#9012 and is not part of this PR.
- A brand-new database does not trigger this bug. It needs a run that was
interrupted inside this migration.
- The migration version and name are unchanged, so databases that already
ran it are not affected.
### Tests
New e2e test `TestSonarqubeFileMetricsKeyLengthMigrationRerun`
(`backend/plugins/sonarqube/e2e/file_metrics_key_length_migration_test.go`).
It starts from the table as it was
before this migration (`varchar(191)` key, one row) and checks two cases:
1. Normal run: the primary key is re-created, the column becomes
`varchar(500)`, and the row is kept.
2. Re-run after an interruption: the primary key is removed first, then the
migration runs. The same checks pass.
Results:
| | MySQL 8 | PostgreSQL 18 |
| --- | --- | --- |
| New test without the fix | Fails (`Error 1091`) | Fails (`SQLSTATE 42704`)
|
| New test with the fix | Passes | Passes |
| `go test ./plugins/sonarqube/...` | Passes | Passes |
`golangci-lint run ./plugins/sonarqube/...` reports 0 issues, and the
migration script linter passes.
I also tested a real DevLake server against PostgreSQL. With the primary key
removed and the history row deleted,
the server crashed without the fix. With the fix, it started normally and
the primary key was back.
--
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]
