[Bug][q_dev] MySQL 8.x incompatibility: ADD COLUMN IF NOT EXISTS / CREATE INDEX IF NOT EXISTS in migration scripts #8744

Search before asking

• I had searched in the issues 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).

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)

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 — 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 — 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 — Same class of bug in q_dev plugin (PostgreSQL-incompatible MODIFY syntax), fixed by #8565 using db.HasColumn() + db.Dialect() switch

Suggested fix:

Replace MariaDB-specific syntax with DAL-native methods, following the same pattern established by #8565:

For ADD COLUMN:

// 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:

// 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?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct