Gitea: Clean Up Database When Upgrading

[MTecknology]

• Gitea version (or commit ref): 1.3.2
• Git version: 2.11
• Operating system: Debian
• Database (use [x]):
  
  
  
  • [ ] PostgreSQL
  
  
  • [ ] MySQL
  
  
  • [ ] MSSQL
  
  
  • [x] SQLite
  
  
• Can you reproduce the bug at https://try.gitea.io:
  
  
  
  • [ ] Yes (provide example URL)
  
  
  • [ ] No
  
  
  • [x] Not relevant
  
  
• Log:

==> /var/log/gitea/xorm.log <==
2017/12/29 22:20:16 [W] Table user Column allow_create_organization db default is , struct default is 1
2017/12/29 22:20:16 [W] Table user Column prohibit_login db default is , struct default is 0
2017/12/29 22:20:16 [W] Table user Column prohibit_login db nullable is true, struct nullable is false
2017/12/29 22:20:16 [W] Table user Column diff_view_style db default is '''', struct default is ''
2017/12/29 22:20:16 [W] Table repository Column size db default is , struct default is 0
2017/12/29 22:20:16 [W] Table repository Column size db nullable is true, struct nullable is false
2017/12/29 22:20:16 [W] Table release Column is_prerelease db default is , struct default is 0
2017/12/29 22:20:16 [W] Table release Column is_prerelease db nullable is true, struct nullable is false
2017/12/29 22:20:16 [W] Table protected_branch Column can_push db default is , struct default is 0
2017/12/29 22:20:16 [W] Table protected_branch Column can_push db nullable is true, struct nullable is false
2017/12/29 22:20:16 [W] Table deleted_branch Column deleted_by_id db nullable is false, struct nullable is true
2017/12/29 22:20:16 [W] Table repo_indexer_status Column repo_id db nullable is false, struct nullable is true
2017/12/29 22:20:16 [W] Table user has column created but struct has not related field
2017/12/29 22:20:16 [W] Table user has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table user has column num_followings but struct has not related field
2017/12/29 22:20:16 [W] Table public_key has column created but struct has not related field
2017/12/29 22:20:16 [W] Table public_key has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table access_token has column created but struct has not related field
2017/12/29 22:20:16 [W] Table access_token has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column enable_wiki but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column enable_external_wiki but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column external_wiki_url but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column enable_issues but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column enable_external_tracker but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column external_tracker_format but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column enable_pulls but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column created but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column external_tracker_style but struct has not related field
2017/12/29 22:20:16 [W] Table repository has column external_tracker_url but struct has not related field
2017/12/29 22:20:16 [W] Table deploy_key has column created but struct has not related field
2017/12/29 22:20:16 [W] Table deploy_key has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table collaboration has column created but struct has not related field
2017/12/29 22:20:16 [W] Table action has column act_user_name but struct has not related field
2017/12/29 22:20:16 [W] Table action has column act_email but struct has not related field
2017/12/29 22:20:16 [W] Table action has column repo_user_name but struct has not related field
2017/12/29 22:20:16 [W] Table action has column repo_name but struct has not related field
2017/12/29 22:20:16 [W] Table action has column created but struct has not related field
2017/12/29 22:20:16 [W] Table issue has column deadline but struct has not related field
2017/12/29 22:20:16 [W] Table issue has column created but struct has not related field
2017/12/29 22:20:16 [W] Table issue has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table pull_request has column merged but struct has not related field
2017/12/29 22:20:16 [W] Table comment has column created but struct has not related field
2017/12/29 22:20:16 [W] Table attachment has column created but struct has not related field
2017/12/29 22:20:16 [W] Table issue_user has column repo_id but struct has not related field
2017/12/29 22:20:16 [W] Table issue_user has column milestone_id but struct has not related field
2017/12/29 22:20:16 [W] Table issue_user has column is_poster but struct has not related field
2017/12/29 22:20:16 [W] Table issue_user has column is_closed but struct has not related field
2017/12/29 22:20:16 [W] Table milestone has column deadline but struct has not related field
2017/12/29 22:20:16 [W] Table milestone has column closed_date but struct has not related field
2017/12/29 22:20:16 [W] Table mirror has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table mirror has column next_update but struct has not related field
2017/12/29 22:20:16 [W] Table release has column created but struct has not related field
2017/12/29 22:20:16 [W] Table login_source has column created but struct has not related field
2017/12/29 22:20:16 [W] Table login_source has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table webhook has column created but struct has not related field
2017/12/29 22:20:16 [W] Table webhook has column updated but struct has not related field
2017/12/29 22:20:16 [W] Table notice has column created but struct has not related field
2017/12/29 22:20:16 [W] Table repo_unit has column index but struct has not related field

Description

Every upgrade seems to come with more and more of these lines. It doesn't seem like the schema update process is doing any clean-up and it doesn't seem that there is a manual process available.

[lunny]

We needs some button to clean up the database on admin panel after upgrade successfully.

[bkcsoft]

@lunny IMO the engine.Sync function should drop any column not present in the struct :neutral_face:

[xor-gate]

Dropping columns will not solve the defaults in struct are in sync right? We have seen this behaviour also from migration between 1.4.0 and 1.4.3.

2018/07/16 10:22:25 [W] Table user Column allow_create_organization db default is , struct default is true
2018/07/16 10:22:25 [W] Table user Column prohibit_login db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table repository Column is_fork db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table repository Column size db default is , struct default is 0
2018/07/16 10:22:25 [W] Table repository Column size db nullable is true, struct nullable is false
2018/07/16 10:22:25 [W] Table action Column is_deleted db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table action Column is_private db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table mirror Column enable_prune db default is 1, struct default is true
2018/07/16 10:22:25 [W] Table release Column is_draft db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table release Column is_prerelease db default is , struct default is false
2018/07/16 10:22:25 [W] Table release Column is_prerelease db nullable is true, struct nullable is false
2018/07/16 10:22:25 [W] Table release Column is_tag db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table login_source Column is_actived db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table login_source Column is_sync_enabled db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table protected_branch Column can_push db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table user_open_id Column show db default is 0, struct default is false
2018/07/16 10:22:25 [W] Table deleted_branch Column deleted_by_id db nullable is false, struct nullable is true
2018/07/16 10:22:25 [W] Table repo_indexer_status Column repo_id db nullable is false, struct nullable is true
2018/07/16 10:22:25 [W] Table access_token has column created but struct has not related field
2018/07/16 10:22:25 [W] Table access_token has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table action has column act_email but struct has not related field
2018/07/16 10:22:25 [W] Table action has column created but struct has not related field
2018/07/16 10:22:25 [W] Table attachment has column created but struct has not related field
2018/07/16 10:22:25 [W] Table collaboration has column created but struct has not related field
2018/07/16 10:22:25 [W] Table comment has column created but struct has not related field
2018/07/16 10:22:25 [W] Table deploy_key has column created but struct has not related field
2018/07/16 10:22:25 [W] Table deploy_key has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table hook_task has column signature but struct has not related field
2018/07/16 10:22:25 [W] Table issue has column deadline but struct has not related field
2018/07/16 10:22:25 [W] Table issue has column created but struct has not related field
2018/07/16 10:22:25 [W] Table issue has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table issue_user has column repo_id but struct has not related field
2018/07/16 10:22:25 [W] Table issue_user has column milestone_id but struct has not related field
2018/07/16 10:22:25 [W] Table issue_user has column is_poster but struct has not related field
2018/07/16 10:22:25 [W] Table issue_user has column is_closed but struct has not related field
2018/07/16 10:22:25 [W] Table login_source has column created but struct has not related field
2018/07/16 10:22:25 [W] Table login_source has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table milestone has column deadline but struct has not related field
2018/07/16 10:22:25 [W] Table milestone has column closed_date but struct has not related field
2018/07/16 10:22:25 [W] Table mirror has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table mirror has column next_update but struct has not related field
2018/07/16 10:22:25 [W] Table notice has column created but struct has not related field
2018/07/16 10:22:25 [W] Table public_key has column created but struct has not related field
2018/07/16 10:22:25 [W] Table public_key has column updated but struct has not related field
2018/07/16 10:22:25 [W] Table pull_request has column merged but struct has not related field
2018/07/16 10:22:25 [W] Table release has column created but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column enable_wiki but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column enable_external_wiki but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column external_wiki_url but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column enable_issues but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column enable_external_tracker but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column external_tracker_format but struct has not related field
2018/07/16 10:22:25 [W] Table repository has column enable_pulls but struct has not related field

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[zeripath]

Hmm. I think we need to keep this issue around. @MTecknology is right.

I guess there are two problems, I don't think it's good practice to go along destroying users data, so I would be loathe to suggest we rigidly enforce our current schema on databases - thus we may have to make a clean database CMD etc. The second issue is that we should probably change our migration scripts so that they automatically clean as they go along so this stops happening - (I think at migration it's fair enough to change schema relevant to the migration)

[lunny]

@bkcsoft Sync2 will never drop the columns.

[lunny]

@zeripath this warning to help user to drop the columns or change the default value manually.

[zeripath]

Ok so auto drop at migration is probably a bad idea.

@lunny do you think we should just leave it up to the users to drop the old columns then?

[deoren]

FWIW, this was the first hit I got when searching Google for the ouput I see when starting up the latest 1.6.x version of gitea. If there is any action required on my part as a user, it's not immediately obvious.

I'm a fairly new user to gitea, so I'm not sure I would notice if anything wasn't working quite right.

[lunny]

Maybe we could add a new command cleanup to do that. We left some unused columns when database migrations for safe considering.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

[samangh]

I'm also seeing this issue on upgrading, running on MySQL.

Is it safe for me to just drop those columns?

[lunny]

It should be safe but gitea will not drop them automatically. But please backup before you do that.

[samangh]

I'm actually getting a slightly different set of errors. If I delete all these columns then gitea gets bricked (e.g. I can't login):

Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column full_name db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column keep_email_private db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column must_change_password db default is 0, struct default is false
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column login_type db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column login_name db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column type db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column location db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column website db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column rands db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column salt db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column language db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column description db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column created_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column updated_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column last_login_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column last_repo_visibility db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column is_active db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column is_admin db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column allow_git_hook db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column allow_import_local db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column allow_create_organization db default is 1, struct default is true
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column prohibit_login db default is 0, struct default is false
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column use_custom_avatar db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column num_followers db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column num_stars db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column num_repos db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column num_teams db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column num_members db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column diff_view_style db default is '''', struct default is ''
Nov  6 14:34:20 server gitea[21171]: [WARN] Table user Column theme db default is '''', struct default is ''
Nov  6 14:34:20 server gitea[21171]: [WARN] Table public_key Column created_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table public_key Column updated_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column uid db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column name db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column token_hash db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column token_salt db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column token_last_eight db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column created_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table access_token Column updated_unix db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column owner_id db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column description db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column website db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column default_branch db default is 'NULL', struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column num_watches db default is NULL, struct default is
Nov  6 14:34:20 server gitea[21171]: [WARN] Table repository Column num_stars db default is NULL, struct default is

[lunny]

For this default value problem, you cannot drop the columns.