github-starred/vikunja
2
0
Fork 0
mirror of https://github.com/go-vikunja/vikunja.git synced 2026-03-22 21:15:18 -05:00
Code Issues 447 Packages Projects Releases 9 Wiki Activity

[PR #1903] [MERGED] fix: handle MySQL 8 CREATE INDEX without IF NOT EXISTS support #3774

New Issue
Closed
opened 2026-03-22 14:53:47 -05:00 by GiteaMirror · 0 comments
GiteaMirror commented 2026-03-22 14:53:47 -05:00
Owner
Copy Link

📋 Pull Request Information

Original PR: https://github.com/go-vikunja/vikunja/pull/1903
Author: @Copilot
Created: 11/28/2025
Status: ✅ Merged
Merged: 11/28/2025
Merged by: @kolaente

Base: main ← Head: copilot/update-migration-remove-if-not-exists

📝 Commits (5)

  • d87b879 Initial plan
  • 6f293d4 fix: handle MySQL 8 CREATE INDEX without IF NOT EXISTS support
  • d00f72a refactor: use consistent error handling pattern with existing migration
  • 75d15ad fix: handle MySQL 8 CREATE INDEX without IF NOT EXISTS support
  • 042bfe7 Merge branch 'main' into copilot/update-migration-remove-if-not-exists

📊 Changes

2 files changed (+20 additions, -14 deletions)

View changed files

📝 go.sum (+0 -12)
📝 pkg/migration/20251108154913.go (+20 -2)

📄 Description

MySQL 8 does not support CREATE INDEX IF NOT EXISTS syntax, causing migration 20251108154913 to fail with SQL syntax error.

Fixes #1833

Changes

  • Detect database type via db.Type() and use appropriate SQL syntax per engine
  • PostgreSQL/SQLite: retain CREATE INDEX IF NOT EXISTS
  • MySQL: use plain CREATE INDEX with error handling for duplicate key (Error 1061)

Follows existing pattern from migration 20241028131622.go:

switch db.Type() {
case schemas.POSTGRES, schemas.SQLITE:
    query = "CREATE INDEX IF NOT EXISTS IDX_task_comments_task_id ON task_comments (task_id)"
case schemas.MYSQL:
    query = "CREATE INDEX IDX_task_comments_task_id ON task_comments (task_id)"
}

_, err := tx.Exec(query)
if err != nil && (!strings.Contains(err.Error(), "Error 1061") || !strings.Contains(err.Error(), "Duplicate key name")) {
    return err
}
Original prompt

MySQL 8 (including 8.4.x) does not support CREATE INDEX IF NOT EXISTS at all, so there is no “correct” version of that exact statement for MySQL.[1][2]

Valid MySQL 8 syntax

The supported CREATE INDEX form in MySQL 8 is essentially:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    ON table_name (column1 [, column2, ...]);

So for your case, the valid MySQL 8 statement is simply:

CREATE INDEX IDX_task_comments_task_id
    ON task_comments (task_id);

without any IF NOT EXISTS clause.[3][4][1]

Handling “if not exists” logic

Because MySQL lacks IF NOT EXISTS for CREATE INDEX, you must emulate it if you need idempotent migrations, for example:

  • Check information_schema.statistics first and only create the index when it does not exist, or
  • Just run CREATE INDEX ... in a migration that is only executed once per database, as many projects do.[2][5][6]

In other words, the migration in that Vikunja issue must be changed to remove IF NOT EXISTS when targeting MySQL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

✨ Let Copilot coding agent set things up for you — coding agent works faster and does higher quality work when set up for your repo.

🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.

## 📋 Pull Request Information **Original PR:** https://github.com/go-vikunja/vikunja/pull/1903 **Author:** [@Copilot](https://github.com/apps/copilot-swe-agent) **Created:** 11/28/2025 **Status:** ✅ Merged **Merged:** 11/28/2025 **Merged by:** [@kolaente](https://github.com/kolaente) **Base:** `main` ← **Head:** `copilot/update-migration-remove-if-not-exists` --- ### 📝 Commits (5) - [`d87b879`](https://github.com/go-vikunja/vikunja/commit/d87b879a775e83eccbe7e4ece69cbd9e4fbdb3df) Initial plan - [`6f293d4`](https://github.com/go-vikunja/vikunja/commit/6f293d4e1c9d246377b05d1a6e3d01ab8cc2aa4c) fix: handle MySQL 8 CREATE INDEX without IF NOT EXISTS support - [`d00f72a`](https://github.com/go-vikunja/vikunja/commit/d00f72a2602b7a0c7a29459c65dcb0fadd47c6b9) refactor: use consistent error handling pattern with existing migration - [`75d15ad`](https://github.com/go-vikunja/vikunja/commit/75d15add05159764444cea260b5985354205d3b7) fix: handle MySQL 8 CREATE INDEX without IF NOT EXISTS support - [`042bfe7`](https://github.com/go-vikunja/vikunja/commit/042bfe72c5a495fcf517ec69b76a4d2a16883317) Merge branch 'main' into copilot/update-migration-remove-if-not-exists ### 📊 Changes **2 files changed** (+20 additions, -14 deletions) <details> <summary>View changed files</summary> 📝 `go.sum` (+0 -12) 📝 `pkg/migration/20251108154913.go` (+20 -2) </details> ### 📄 Description MySQL 8 does not support `CREATE INDEX IF NOT EXISTS` syntax, causing migration `20251108154913` to fail with SQL syntax error. Fixes #1833 ## Changes - Detect database type via `db.Type()` and use appropriate SQL syntax per engine - PostgreSQL/SQLite: retain `CREATE INDEX IF NOT EXISTS` - MySQL: use plain `CREATE INDEX` with error handling for duplicate key (Error 1061) Follows existing pattern from migration `20241028131622.go`: ```go switch db.Type() { case schemas.POSTGRES, schemas.SQLITE: query = "CREATE INDEX IF NOT EXISTS IDX_task_comments_task_id ON task_comments (task_id)" case schemas.MYSQL: query = "CREATE INDEX IDX_task_comments_task_id ON task_comments (task_id)" } _, err := tx.Exec(query) if err != nil && (!strings.Contains(err.Error(), "Error 1061") || !strings.Contains(err.Error(), "Duplicate key name")) { return err } ``` <!-- START COPILOT CODING AGENT SUFFIX --> <details> <summary>Original prompt</summary> > MySQL 8 (including 8.4.x) does not support `CREATE INDEX IF NOT EXISTS` at all, so there is no “correct” version of that exact statement for MySQL.[1][2] > > ## Valid MySQL 8 syntax > > The supported `CREATE INDEX` form in MySQL 8 is essentially: > > ```sql > CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name > ON table_name (column1 [, column2, ...]); > ``` > > So for your case, the valid MySQL 8 statement is simply: > > ```sql > CREATE INDEX IDX_task_comments_task_id > ON task_comments (task_id); > ``` > > without any `IF NOT EXISTS` clause.[3][4][1] > > ## Handling “if not exists” logic > > Because MySQL lacks `IF NOT EXISTS` for `CREATE INDEX`, you must emulate it if you need idempotent migrations, for example: > > - Check `information_schema.statistics` first and only create the index when it does not exist, or > - Just run `CREATE INDEX ...` in a migration that is only executed once per database, as many projects do.[2][5][6] > > In other words, the migration in that Vikunja issue must be changed to remove `IF NOT EXISTS` when targeting MySQL. > > [1](https://dev.mysql.com/doc/en/create-index.html) > [2](https://github.com/jOOQ/jOOQ/issues/12387) > [3](https://www.percona.com/blog/mysql-8-0-functional-indexes/) > [4](https://popsql.com/learn-sql/mysql/how-to-create-an-index-in-mysql) > [5](https://github.com/PrivateBin/PrivateBin/issues/943) > [6](https://stackoverflow.com/questions/30259196/add-index-to-table-if-it-does-not-exist) > [7](https://github.com/go-vikunja/vikunja/issues/1833) > [8](https://www.w3schools.com/mysql/mysql_create_index.asp) > [9](https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) > [10](https://www.sqlservercentral.com/forums/topic/if-exists-drop-index-if-not-exists-create-index) > [11](https://www.bytebase.com/reference/mysql/how-to/how-to-create-index-mysql/) > [12](https://stackoverflow.com/questions/17823322/1062-duplicate-entry-for-key-unique-id-when-trying-to-add-unique-key-my/17823771) > [13](https://dev.mysql.com/doc/refman/8.1/en/dynindex-statement.html) > [14](https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html) > [15](https://dev.mysql.com/doc/refman/8.4/en/replication-features-create-if-not-exists.html) > [16](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html) > [17](https://forum.xojo.com/t/how-to-prevent-duplicate-records-on-my-mysql-table/25228) > [18](https://community.latenode.com/t/mysql-single-query-method-to-insert-records-only-when-they-dont-exist/31218) > [19](https://dev.mysql.com/doc/en/create-table-foreign-keys.html) > [20](https://www.drupal.org/project/drupal/issues/998474) </details> <!-- START COPILOT CODING AGENT TIPS --> --- ✨ Let Copilot coding agent [set things up for you](https://github.com/go-vikunja/vikunja/issues/new?title=✨+Set+up+Copilot+instructions&body=Configure%20instructions%20for%20this%20repository%20as%20documented%20in%20%5BBest%20practices%20for%20Copilot%20coding%20agent%20in%20your%20repository%5D%28https://gh.io/copilot-coding-agent-tips%29%2E%0A%0A%3COnboard%20this%20repo%3E&assignees=copilot) — coding agent works faster and does higher quality work when set up for your repo. --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>
GiteaMirror added the pull-request label 2026-03-22 14:53:47 -05:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
area/api
area/caldav
area/filters
area/frontend
area/gantt
area/internal-code
area/typesense
bug
changes requested
confirmed
dependencies
enhancement
good first issue
help wanted
kind/bug
kind/feature
needs reproduction
pull-request
Mirrored from GitHub Pull Request
 question
security
support
upstream issue
waiting for reply
wontfix
No Label pull-request
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/vikunja#3774
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?