You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@devlake.apache.org by "yamoyamoto (via GitHub)" <gi...@apache.org> on 2026/03/02 10:48:01 UTC
[I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
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: dev-unsubscribe@devlake.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh closed issue #8744: [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts
URL: https://github.com/apache/incubator-devlake/issues/8744
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #8744:
URL: https://github.com/apache/incubator-devlake/issues/8744#issuecomment-3983630055
<!-- Greeting -->
Hi @yamoyamoto! I'm [Dosu](https://go.dosu.dev/dosubot) and I’m helping the incubator-devlake team.
<!-- Answer -->
Thanks for the detailed bug report! Your analysis is spot-on. The issue is that `ADD COLUMN IF NOT EXISTS` and `CREATE INDEX IF NOT EXISTS` are MariaDB-specific extensions not supported in MySQL 8.x.
Your suggested fix aligns well with DevLake's established patterns. Looking at other plugins in the codebase:
**For adding columns**, the [TAPD plugin](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/tapd/models/migrationscripts/20230411_modify_custom_field_name.go#L74-L89) demonstrates the recommended pattern:
```go
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")
}
}
```
**Alternatively**, you can use `migrationhelper.AutoMigrateTables()` which handles database differences automatically—this is the [primary pattern used across DevLake plugins](https://github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/helpers/migrationhelper/migrationhelper.go#L36-L46).
**For indexes**, your approach of dropping `IF NOT EXISTS` and letting duplicate errors be handled at runtime is correct—it's a safe runtime error rather than a syntax error.
[PR #8565](https://github.com/apache/incubator-devlake/pull/8565) which fixed the PostgreSQL compatibility issue in q_dev is a good reference for the dialect-aware pattern when more complex operations are needed.
<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*
---
<sup>How did I do? [Good](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/1a4b4786-12ec-4bf6-b47a-59ecbc736cf9?feedback_type=other)</sup>
[](https://app.dosu.dev/b4e8e847-d479-4541-83a8-d88d83fea5c9/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Thanks%20for%20the%20detailed%20bug%20report%21%20Your%20analysis%20is%20spot-on.%20The%20issue%20is%20that%20%60ADD%20COLUMN%20IF%20NOT%20EXISTS%60%20and%20%60CREATE%20INDEX%20IF%20NOT%20EXISTS%60%20are%20MariaDB-specific%20extensions%20not%20supported%20in%20MySQL%208.x.%0A%0AYour%20suggested%20fix%20aligns%20well%20with%20DevLake%27s%20established%20patterns.%20Looking%20at%20other%20plugins%20in%20the%20codebase%3A%0A%0A%2A%2AFor%20adding%20columns%2A%2A%2C%20the%20%5BTAPD%20plugin%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/plugins/tapd/models/migrationscripts/20230411_modify_custom_field_name.go%23L74-L89%29%20demonstrates%20the%20recommended%20pattern%3A%0A%0A%60%60%60go%0Aif%20%21
db.HasColumn%28%22_tool_q_dev_user_data%22%2C%20%22scope_id%22%29%20%7B%0A%20%20%20%20if%20err%20%3A%3D%20db.AddColumn%28%22_tool_q_dev_user_data%22%2C%20%22scope_id%22%2C%20dal.Varchar%29%3B%20err%20%21%3D%20nil%20%7B%0A%20%20%20%20%20%20%20%20return%20errors.Default.Wrap%28err%2C%20%22failed%20to%20add%20scope_id%22%29%0A%20%20%20%20%7D%0A%7D%0A%60%60%60%0A%0A%2A%2AAlternatively%2A%2A%2C%20you%20can%20use%20%60migrationhelper.AutoMigrateTables%28%29%60%20which%20handles%20database%20differences%20automatically%E2%80%94this%20is%20the%20%5Bprimary%20pattern%20used%20across%20DevLake%20plugins%5D%28https%3A//github.com/apache/incubator-devlake/blob/2c15ad880dce830c6512fa58039527cbf4745b8b/backend/helpers/migrationhelper/migrationhelper.go%23L36-L46%29.%0A%0A%2A%2AFor%20indexes%2A%2A%2C%20your%20approach%20of%20dropping%20%60IF%20NOT%20EXISTS%60%20and%20letting%20duplicate%20errors%20be%20handled%20at%20runtime%20is%20correct%E2%80%94it%27s%20a%20safe%20runtime%20error%20rather%20tha
n%20a%20syntax%20error.%0A%0A%5BPR%20%238565%5D%28https%3A//github.com/apache/incubator-devlake/pull/8565%29%20which%20fixed%20the%20PostgreSQL%20compatibility%20issue%20in%20q_dev%20is%20a%20good%20reference%20for%20the%20dialect-aware%20pattern%20when%20more%20complex%20operations%20are%20needed.) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/incubator-devlake/issues/8744)
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh commented on issue #8744:
URL: https://github.com/apache/incubator-devlake/issues/8744#issuecomment-3984155643
Very clean and thorough—looking forward to your PR!
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
Posted by "yamoyamoto (via GitHub)" <gi...@apache.org>.
yamoyamoto commented on issue #8744:
URL: https://github.com/apache/incubator-devlake/issues/8744#issuecomment-3987742002
I've submitted a fix: #8745
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts [incubator-devlake]
Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh commented on issue #8744:
URL: https://github.com/apache/incubator-devlake/issues/8744#issuecomment-3984166661
@warren830 FYI
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org