Migration issues from 1.14.4 to 1.15.0 on task table

[anderson-dev]

Unfortunately, I mistakenly deployed my team's instance of Gitea using the 'latest' tag of the official Gitea docker image around 3-4 months ago (I believe it was around version 1.14.2 at that time) thinking it was the latest stable release rather than an unstable dev version. I upgraded to the stable gitea/gitea:1.14.4 about 2 months ago. It required me to force migrate the underlying sqlite db in order for it to take. My hope was that some of the extra fields it added, but didn't use yet, would simply be used properly upon upgrade to the next release version. Yesterday I backed up my volume and attempted to upgrade to 1.15.0 but it failed during db migration.

What is the simplest way to ensure I'm back to the correct db schema version? Should I just blast away the volume, start with a fresh install, re-configure, and re-push all local repos to the new installation?

• Gitea version (or commit ref): 1.14.4 -> 1.15.0
• Git version:
• Operating system: official docker image
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

1.14.4 (Current warnings relating to current db schema)

2021/08/26 12:26:49 routers/init.go:150:GlobalInit() [I] SQLite3 Supported
2021/08/26 12:26:49 routers/init.go:68:initDBEngine() [I] Beginning ORM engine initialization.
2021/08/26 12:26:49 routers/init.go:75:initDBEngine() [I] ORM engine initialization attempt #1/10...
2021/08/26 12:26:49 ...om/urfave/cli/app.go:524:HandleAction() [I] PING DATABASE sqlite3
2021/08/26 12:26:49 ...rfave/cli/command.go:173:Run() [W] Table mirror has column lfs_enabled but struct has not related field
2021/08/26 12:26:49 ...rfave/cli/command.go:173:Run() [W] Table mirror has column lfs_endpoint but struct has not related field
2021/08/26 12:26:49 ...rfave/cli/command.go:173:Run() [W] Table email_address has column lower_email but struct has not related field
2021/08/26 12:26:49 ...rfave/cli/command.go:173:Run() [W] Table email_address has column is_primary but struct has not related field
2021/08/26 12:26:49 ...rfave/cli/command.go:173:Run() [W] Table task has column message but struct has not related field
2021/08/26 12:26:49 routers/init.go:155:GlobalInit() [I] ORM engine initialization successful!
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: update_mirrors
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: repo_health_check
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: check_repo_stats
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: archive_cleanup
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: sync_external_users
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: deleted_branches_cleanup
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: update_migration_poster_id
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: cleanup_hook_task_table
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: delete_inactive_accounts
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: delete_repo_archives
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: git_gc_repos
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: resync_all_sshkeys
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: resync_all_sshprincipals
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: resync_all_hooks
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: reinit_missing_repos
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: delete_missing_repos
2021/08/26 12:26:49 ...odules/cron/tasks.go:120:RegisterTask() [D] Registering task: delete_generated_repository_avatars
2021/08/26 12:26:49 ...er/issues/indexer.go:142:func2() [I] PID 18: Initializing Issue Indexer: db
2021/08/26 12:26:49 ...er/issues/indexer.go:221:func3() [I] Issue Indexer Initialization took 22.5µs
2021/08/26 12:26:49 ...xer/stats/indexer.go:38:populateRepoIndexer() [I] Populating the repo stats indexer with existing repositories
2021/08/26 12:26:49 ...ueue_disk_channel.go:162:Run() [D] PersistableChannelUniqueQueue: repo_stats_update Starting
2021/08/26 12:26:49 ...ue/queue_bytefifo.go:96:Run() [D] unique-level: repo_stats_update-level Starting
2021/08/26 12:26:49 ...xer/stats/indexer.go:84:populateRepoIndexer() [I] Done (re)populating the repo stats indexer with existing repositories
2021/08/26 12:26:49 ...ueue_disk_channel.go:162:Run() [D] PersistableChannelUniqueQueue: pr_patch_checker Starting
2021/08/26 12:26:49 ...ue/queue_bytefifo.go:96:Run() [D] unique-level: pr_patch_checker-level Starting
2021/08/26 12:26:50 ...ueue_disk_channel.go:137:Run() [D] PersistableChannelQueue: task Starting
2021/08/26 12:26:50 ...ue/queue_bytefifo.go:96:Run() [D] level: task-level Starting
2021/08/26 12:26:50 cmd/web.go:189:listen() [I] Listen: http://0.0.0.0:3000
2021/08/26 12:26:50 cmd/web.go:192:listen() [I] LFS server enabled
2021/08/26 12:26:50 ...s/graceful/server.go:62:NewServer() [I] Starting new Web server: tcp:0.0.0.0:3000 on PID: 18
2021/08/26 12:26:50 ...s/graceful/server.go:75:func1() [D] Starting server on tcp:0.0.0.0:3000 (PID: 18)



1.15.0 (Errors seen during attempted migration from 1.14.4 w/ schema discrepancies)

2021/08/26 11:57:38 routers/init.go:93:GlobalInit() [I] SQLite3 Supported
2021/08/26 11:57:38 routers/common/db.go:20:InitDBEngine() [I] Beginning ORM engine initialization.
2021/08/26 11:57:38 routers/common/db.go:27:InitDBEngine() [I] ORM engine initialization attempt #1/10...
2021/08/26 11:57:38 ...om/urfave/cli/app.go:524:HandleAction() [I] PING DATABASE sqlite3
2021/08/26 11:57:38 ...ations/migrations.go:419:Migrate() [I] Migration[178]: Add LFS columns to Mirror
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column id but struct has not related field
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column repo_id but struct has not related field
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column interval but struct has not related field
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column enable_prune but struct has not related field
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column updated_unix but struct has not related field
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table mirror has column next_update_unix but struct has not related field
2021/08/26 11:57:38 ...ations/migrations.go:419:Migrate() [I] Migration[179]: Convert avatar url to text
2021/08/26 11:57:38 ...ations/migrations.go:419:Migrate() [I] Migration[180]: Delete credentials from past migrations
2021/08/26 11:57:38 ...ations/migrations.go:419:Migrate() [I] Migration[181]: Always save primary email on email address table
2021/08/26 11:57:38 routers/common/db.go:28:InitDBEngine() [W] Table email_address Column lower_email db nullable is true, struct nullable is false
2021/08/26 11:57:40 ...ations/migrations.go:419:Migrate() [I] Migration[182]: Add issue resource index table
2021/08/26 11:57:40 ...ations/migrations.go:419:Migrate() [I] Migration[183]: Create PushMirror table
2021/08/26 11:57:40 ...ations/migrations.go:419:Migrate() [I] Migration[184]: Rename Task errors to message
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column id but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column doer_id but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column owner_id but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column repo_id but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column start_time but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column end_time but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column payload_content but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column message but struct has not related field
2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column created but struct has not related field
2021/08/26 11:57:40 routers/common/db.go:33:InitDBEngine() [E] ORM engine initialization attempt #1/10 failed. Error: migrate: do migrate: error in table task after rename: duplicate column name: message

[zeripath]

Although most of the warnings ... task has column x messages are expected:

2021/08/26 11:57:40 routers/init.go:97:GlobalInit() [W] Table task has column message but struct has not related field

This one is not.

There should not be a message column in task.

There is no code that would have added this so I don't know how it could be there. 2 options:

1. You will have to look at your db and consider deleting the column or even the table.
2. If you still have the gitea binary 1.14 you could do a gitea doctor recreate-table task which should remove the problem column and then reattempt migration.

[lunny]

Or you can just drop column message in task table and try again. Please back up before you do any change in production environment.

[tacerus]

I think my issue might be related.

I had one failed migration attempt. After rolling back I attempted it again, and received this:

2021/08/28 21:16:30 ...ations/migrations.go:49:Migrate() [I] [SQL] SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? [gitea task] - 1.345881ms     2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column id but struct has not related field                                         2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 doer_id but struct has not related field                                    
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 owner_id but struct has not related field                                   
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 repo_id but struct has not related field                                    
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 start_time but struct has not related field                                 
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 end_time but struct has not related field                                   
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 payload_content but struct has not related field                            
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 message but struct has not related field                                    
2021/08/28 21:16:30 routers/init.go:97:GlobalInit() [W] Table task has column
 created but struct has not related field                                    
2021/08/28 21:16:30 ...ations/migrations.go:422:Migrate() [I] [SQL] ALTER TAB
LE `task` CHANGE errors message text [] - 19.307246ms                        
2021/08/28 21:16:30 ...ations/migrations.go:422:Migrate() [I] [SQL] ROLLBACK 
[] - 162.84µs                                                                
2021/08/28 21:16:30 routers/common/db.go:33:InitDBEngine() [E] ORM engine ini
tialization attempt #6/10 failed. Error: migrate: do migrate: Error 1060: Dup
licate column name 'message'                                                 
2021/08/28 21:16:30 routers/common/db.go:34:InitDBEngine() [I] Backing off fo
r 3 seconds        

I stopped Gitea and inspected the database...

The gitea.tasks table looked like this:

mysql> SHOW COLUMNS FROM task;
+-----------------+--------+------+-----+---------+----------------+
| Field           | Type   | Null | Key | Default | Extra          |
+-----------------+--------+------+-----+---------+----------------+
| id              | bigint | NO   | PRI | NULL    | auto_increment |
| doer_id         | bigint | YES  |     | NULL    |                |
| owner_id        | bigint | YES  |     | NULL    |                |
| repo_id         | bigint | YES  |     | NULL    |                |
| type            | int    | YES  |     | NULL    |                |
| status          | int    | YES  | MUL | NULL    |                |
| start_time      | bigint | YES  |     | NULL    |                |
| end_time        | bigint | YES  |     | NULL    |                |
| payload_content | text   | YES  |     | NULL    |                |
| message         | text   | YES  |     | NULL    |                |
| created         | bigint | YES  |     | NULL    |                |
| errors          | text   | YES  |     | NULL    |                |
+-----------------+--------+------+-----+---------+----------------+
12 rows in set (0.01 sec)

The message column seems to be empty on all rows:

mysql> SELECT id, message FROM task;
+----+---------+
| id | message |
+----+---------+
|  5 |         |
|  8 |         |
| 11 |         |
| 14 |         |
| 17 |         |
| 20 |         |
| 23 |         |
| 26 |         |
| 29 |         |
| 32 |         |
| 38 |         |
| 41 |         |
| 44 |         |
| 47 |         |
| 50 |         |
| 53 |         |
+----+---------+
16 rows in set (0.00 sec)

Hence I removed the "ghost"-column, as suggested:

mysql> ALTER TABLE task DROP COLUMN message;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

... and started Gitea again.

The migrations continued and Gitea started back up with 1.15.0.

My "500 issue" from #16725 came back into action however.

I hope I do everyone a favor by using a Pastebin for the trace-log instead of extending this already long comment:
https://pasta.lysergic.dev/?6c1b79c0de06b567#7cqeueQrpzupubyJk6nbz5UiAwj4bN1nq7yAHH4Mdx1C

[anderson-dev]

Migration appears successful however I still notice a few quirks that still exist:

1. in-line code review comments are not seen by anyone other than the author & no notification is sent that an in-line code review has started
2. one user shows with the same exact email (the rest used to show this way prior to migration but this is the only one that still appears incorrectly)
   

[tacerus]

I just attempted the upgrade again with v1.15.2. Out of the box, similar errors about the column message appeared:

...
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column id but struct has not related field                                     
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column doer_id but struct has not related field                                
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column owner_id but struct has not related field                               
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column repo_id but struct has not related field                                
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column start_time but struct has not related field                             
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column end_time but struct has not related field                               
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column payload_content but struct has not related field
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column message but struct has not related field 
2021/09/11 02:09:08 routers/init.go:97:GlobalInit() [W] Table task has column created but struct has not related field        
2021/09/11 02:09:08 routers/common/db.go:33:InitDBEngine() [E] ORM engine initialization attempt #9/10 failed. Error: migrate: do migrate: Error 1060: Duplicate column
 name 'message'                                                                                                                                       
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column id but struct has not related field                                     
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column doer_id but struct has not related field        
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column owner_id but struct has not related field
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column repo_id but struct has not related field
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column start_time but struct has not related field                             
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column end_time but struct has not related field                                                
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column payload_content but struct has not related field                                         
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column message but struct has not related field                                                 
2021/09/11 02:09:11 routers/init.go:97:GlobalInit() [W] Table task has column created but struct has not related field
2021/09/11 02:09:11 routers/init.go:100:GlobalInit() [F] ORM engine initialization failed: migrate: do migrate: Error 1060: Duplicate column name 'message'
...

I again applied the "patch" from my last comment. It again allowed the database migrations to finish running, but this time did not cause any 500 errors.

Since it seems to be working this way, I will not downgrade again, but can someone confirm if this "patch"ed database is fine to be used, or if there is something possible trying to use this message column I removed in the future / in some feature?

[zeripath]

It's fine.

The message field shouldn't have been present in the task table in the first place. I'm not sure how it got there - did you run a dev version on this db at some point? The Errors field in that task table has now been renamed to message and everything will work fine from now on.

Similarly the primary key problem coming back doesn't make any sense at all. Did you migrate using 1.15.0 and 1.15.2? You should just migrate using version 1.15.2 - once we release 1.15.y you shouldn't use 1.15.x where x<y. If you use 1.15.0 you're not going to get any of the fixes we make to migrations in say 1.15.6.

You're fine though. Just run the doctor recreate-table command for the issue_index primary key problem and you're done.

---

But for anyone else watching:

• If we have versions up to 1.14.7 and 1.15.2.
• and your current version is 1.14.2
• just update to 1.15.2 or do 1.14.7 then 1.15.2 only.
• DO NOT DO 1.14.3, ..., 1.14.7, 1.15.0, 1.15.1, 1.15.2

If you are on 1.14.3-1.14.6 because there was a bug in the dump command in these versions: if you backup your db using dump then you need to migrate to 1.14.7 to get a dump before you upgrade to 1.15 - but even then do not do 1.14.3,1.14.4,1.14.5,1.14.6,1.14.7 just go straight to 1.14.7 - make the dump and thence straight to 1.15.2.

---

So what do you do if you are on 1.13.x or lower?

Backup your db and go straight to 1.15.2 (or 1.15.x where X is the largest point release). That's it. Read the breaking notes for 1.14.0 and 1.15.0 on the blog though.

You can if you prefer go via 1.14.7 but you don't need to and from 1.15.3 that's a dead branch. Do not go via 1.14.0 etc - because if you do that you are needlessly exposing yourself to any bugs in migrations in 1.14.0 (which IIRC there were).

You get one shot at each migration.

We will mitigate bugs in migrations but better will fix them in the code so future users don't experience them. By going via out of date point releases you are needlessly exposing yourself to old bugs in these and preventing us from fixing things.

[anderson-dev]

Just wanted to mention, after correcting the table schema as suggested, I still experienced small issues such as in-code review not being viewable by others. As a result, I ended up having to export all repos, start from scratch with a stable version of gitea, recreate/reconfigure all orgs and settings, reimport all repos, recreate all users/permissions, and reconfigure all repos. We lost all of our pr history but now we are confident in our foundations on a stable Gitea environment rather than the one that started as a dev build and things are working much more reliably.

I’ll be much more careful whether Docker latest is considered dev or stable in the future for other software. Also thanks for your great efforts @zeripath to help us through these partial migrations and hopefully your recent code fix reduces the chance of users falling through the cracks like we did.