yamoyamoto opened a new issue, #8744: URL: https://github.com/apache/incubator-devlake/issues/8744
### 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 On first startup of DevLake v1.0.3-beta9 with **MySQL 8.x**, the q_dev plugin migration `20251209000001` fails with SQL syntax errors because it uses `ADD COLUMN IF NOT EXISTS` and `CREATE INDEX IF NOT EXISTS` syntax. Additionally, the same MariaDB-specific pattern was introduced again in migration `20260220000001` (merged to main after v1.0.3-beta9, not yet released). These are **MariaDB-specific extensions** and are **not supported by MySQL 8.x** ([MySQL ALTER TABLE docs](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html)). The Config UI shows **"Operation failed."** messages for the q_dev plugin migrations, causing confusion for users even though the q_dev plugin itself may not be in use. **Actual error logs from MySQL 8.x (4 errors from migration `20251209000001`):** ``` Error 1064 (42000): You have an error in your SQL syntax; ... near 'IF NOT EXISTS scope_id VARCHAR(255) DEFAULT NULL' at line 2 ALTER TABLE _tool_q_dev_user_data ADD COLUMN IF NOT EXISTS scope_id VARCHAR(255) DEFAULT NULL Error 1064 (42000): You have an error in your SQL syntax; ... near 'IF NOT EXISTS idx_q_dev_user_data_scope_id ON _tool_q_dev_user_data(scope_id)' at line 1 CREATE INDEX IF NOT EXISTS idx_q_dev_user_data_scope_id ON _tool_q_dev_user_data(scope_id) Error 1064 (42000): You have an error in your SQL syntax; ... near 'IF NOT EXISTS scope_id VARCHAR(255) DEFAULT NULL' at line 2 ALTER TABLE _tool_q_dev_s3_file_meta ADD COLUMN IF NOT EXISTS scope_id VARCHAR(255) DEFAULT NULL Error 1064 (42000): You have an error in your SQL syntax; ... near 'IF NOT EXISTS idx_q_dev_s3_file_meta_scope_id ON _tool_q_dev_s3_file_meta(scope_' at line 1 CREATE INDEX IF NOT EXISTS idx_q_dev_s3_file_meta_scope_id ON _tool_q_dev_s3_file_meta(scope_id) ``` <img width="970" height="409" alt="Image" src="https://github.com/user-attachments/assets/d612b1d5-8b5c-42cd-8b79-4034dd7c221a"; /> ### What do you expect to happen All migrations should succeed without errors on MySQL 8.x. The migration scripts should use database-agnostic patterns (e.g., `db.HasColumn()` + `db.AddColumn()` from the DAL interface) instead of MariaDB-specific syntax. ### How to reproduce 1. Deploy DevLake v1.0.3-beta9 with **MySQL 8.x** as the database backend 2. Start DevLake for the first time (or upgrade from a version without the q_dev tables) 3. Observe the Config UI showing "Operation failed." messages for q_dev migrations ### Anything else **Affected files:** 1. [`backend/plugins/q_dev/models/migrationscripts/20251209_add_scope_id_fields.go`](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/q_dev/models/migrationscripts/20251209_add_scope_id_fields.go) — **included in v1.0.3-beta9, confirmed reproducible** - Uses `ADD COLUMN IF NOT EXISTS` on `_tool_q_dev_user_data` and `_tool_q_dev_s3_file_meta` - Uses `CREATE INDEX IF NOT EXISTS` on both tables 2. [`backend/plugins/q_dev/models/migrationscripts/20260220_add_account_id_to_s3_slice.go`](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/q_dev/models/migrationscripts/20260220_add_account_id_to_s3_slice.go) — **not yet released, but likely affected in the next release** (merged to main after v1.0.3-beta9; uses the same `ADD COLUMN IF NOT EXISTS` pattern) - Uses `ADD COLUMN IF NOT EXISTS` on `_tool_q_dev_s3_slices` **Root cause:** The q_dev plugin is the **only plugin** in DevLake that uses raw `ADD COLUMN IF NOT EXISTS` / `CREATE INDEX IF NOT EXISTS` SQL. All other plugins use the database-agnostic DAL interface (`migrationhelper.AutoMigrateTables()`, `db.HasColumn()`, `db.AddColumn()`, etc.). Note: The existing fallback code (`if err != nil { _ = db.Exec(...) }`) partially mitigates the `ADD COLUMN` issue on fresh installs (the initial syntax error triggers the fallback which succeeds), so the migration can be completed by ignoring the "Operation failed." message and proceeding. However: - The `CREATE INDEX IF NOT EXISTS` has **no fallback** — errors are silently discarded with `_ =`, so **indexes are never created on MySQL** - The initial syntax errors are surfaced in the Config UI as "Operation failed.", causing user confusion **Related:** - [#8564](https://github.com/apache/incubator-devlake/issues/8564) — Same class of bug in q_dev plugin (PostgreSQL-incompatible `MODIFY` syntax), fixed by [#8565](https://github.com/apache/incubator-devlake/pull/8565) using `db.HasColumn()` + `db.Dialect()` switch **Suggested fix:** Replace MariaDB-specific syntax with DAL-native methods, following the same pattern established by [#8565](https://github.com/apache/incubator-devlake/pull/8565): For `ADD COLUMN`: ```go // Before (MariaDB-specific, fails on MySQL 8.x): err := db.Exec(`ALTER TABLE _tool_q_dev_user_data ADD COLUMN IF NOT EXISTS scope_id VARCHAR(255) DEFAULT NULL`) if err != nil { _ = db.Exec(`ALTER TABLE _tool_q_dev_user_data ADD COLUMN scope_id VARCHAR(255) DEFAULT NULL`) } // After (database-agnostic): if !db.HasColumn("_tool_q_dev_user_data", "scope_id") { if err := db.AddColumn("_tool_q_dev_user_data", "scope_id", dal.Varchar); err != nil { return errors.Default.Wrap(err, "failed to add scope_id to _tool_q_dev_user_data") } } ``` For `CREATE INDEX`: ```go // Before (MariaDB-specific, fails on MySQL 8.x): _ = db.Exec(`CREATE INDEX IF NOT EXISTS idx_q_dev_user_data_scope_id ON _tool_q_dev_user_data(scope_id)`) // After (works on both MySQL and PostgreSQL — duplicate index error is a safe runtime error, not a syntax error): _ = db.Exec(`CREATE INDEX idx_q_dev_user_data_scope_id ON _tool_q_dev_user_data(scope_id)`) ``` ### Version DevLake: v1.0.3-beta9, Database: MySQL 8.x ### 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]
