Clean Up Database When Upgrading #3265

MTecknology · 2017-12-29T22:30:22Z

Gitea version (or commit ref): 1.3.2
Git version: 2.11
Operating system: Debian
Database (use [x]):
- PostgreSQL
- MySQL
- MSSQL
- SQLite
Can you reproduce the bug at https://try.gitea.io:
- Yes (provide example URL)
- No
- 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.

The text was updated successfully, but these errors were encountered:

lunny · 2017-12-30T04:18:39Z

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

bkcsoft · 2018-07-10T04:53:43Z

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

xor-gate · 2018-07-16T09:17:17Z

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 · 2019-01-17T22:11:41Z

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 · 2019-01-17T22:54:30Z

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 · 2019-01-18T03:30:38Z

@bkcsoft Sync2 will never drop the columns.

lunny · 2019-01-18T03:31:36Z

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

zeripath · 2019-01-18T06:20:10Z

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 · 2019-01-18T13:44:43Z

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 · 2019-01-20T06:02:17Z

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

stale · 2019-03-21T06:17:08Z

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 · 2019-11-06T09:16:57Z

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

Is it safe for me to just drop those columns?

lunny · 2019-11-06T11:43:41Z

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

samangh · 2019-11-06T17:20:00Z

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 · 2019-11-07T05:23:37Z

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

Provides new command: `gitea doctor recreate-table` which will recreate db tables and copy the old data in to the new table. This function can be used to remove the old warning of struct defaults being out of date. Fix go-gitea#8868 Fix go-gitea#3265 Fix go-gitea#8894 Signed-off-by: Andrew Thornton <art27@cantab.net>

Provides new command: `gitea doctor recreate-table` which will recreate db tables and copy the old data in to the new table. This function can be used to remove the old warning of struct defaults being out of date. Fix #8868 Fix #3265 Fix #8894 Signed-off-by: Andrew Thornton <art27@cantab.net>

lunny added the type/enhancement An improvement of existing functionality label Dec 30, 2017

lunny added this to the 1.x.x milestone Dec 30, 2017

stale bot added the issue/stale label Jan 17, 2019

stale bot removed the issue/stale label Jan 17, 2019

stale bot added the issue/stale label Mar 21, 2019

lafriks added the issue/confirmed Issue has been reviewed and confirmed to be present or accepted to be implemented label Mar 21, 2019

stale bot removed the issue/stale label Mar 21, 2019

zeripath mentioned this issue May 13, 2019

Make dropTableColumns drop columns on sqlite and constraints on all #6849

Merged

samangh mentioned this issue Nov 7, 2019

[WARN] Table repository Column <table> default is NULL, struct default is #8868

Closed

7 tasks

zeripath mentioned this issue Aug 2, 2020

Add command to recreate tables #12407

Merged

zeripath closed this as completed in #12407 Sep 6, 2020

lunny removed this from the 1.x.x milestone Sep 8, 2020

go-gitea locked and limited conversation to collaborators Nov 24, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Clean Up Database When Upgrading #3265

Clean Up Database When Upgrading #3265

MTecknology commented Dec 29, 2017

lunny commented Dec 30, 2017

bkcsoft commented Jul 10, 2018

xor-gate commented Jul 16, 2018

stale bot commented Jan 17, 2019

zeripath commented Jan 17, 2019

lunny commented Jan 18, 2019

lunny commented Jan 18, 2019

zeripath commented Jan 18, 2019

deoren commented Jan 18, 2019

lunny commented Jan 20, 2019

stale bot commented Mar 21, 2019

samangh commented Nov 6, 2019 •

edited

Loading

lunny commented Nov 6, 2019

samangh commented Nov 6, 2019

lunny commented Nov 7, 2019

Clean Up Database When Upgrading #3265

Clean Up Database When Upgrading #3265

Comments

MTecknology commented Dec 29, 2017

Description

lunny commented Dec 30, 2017

bkcsoft commented Jul 10, 2018

xor-gate commented Jul 16, 2018

stale bot commented Jan 17, 2019

zeripath commented Jan 17, 2019

lunny commented Jan 18, 2019

lunny commented Jan 18, 2019

zeripath commented Jan 18, 2019

deoren commented Jan 18, 2019

lunny commented Jan 20, 2019

stale bot commented Mar 21, 2019

samangh commented Nov 6, 2019 • edited Loading

lunny commented Nov 6, 2019

samangh commented Nov 6, 2019

lunny commented Nov 7, 2019

samangh commented Nov 6, 2019 •

edited

Loading