Gitea sqlite3 restore fails

Install/Maintain/Configure
1

My setup is ubuntu 18.04 version OS.
I’ve created backup based on documentation and restore same way that documentation followed.
Gitea version 1.11.3

Command I used for restore
sqlite3 /var/lib/gitea/data/gitea.db <gitea-db.sql

Error: near line 4: index IDX_user_last_login_unix already exists
Error: near line 5: index IDX_user_is_active already exists
Error: near line 6: index UQE_user_lower_name already exists
Error: near line 7: index UQE_user_name already exists
Error: near line 8: index IDX_user_created_unix already exists
Error: near line 9: index IDX_user_updated_unix already exists
Error: near line 10: no such column: false
Error: near line 13: index IDX_public_key_fingerprint already exists
Error: near line 14: index IDX_public_key_owner_id already exists
Error: near line 18: index IDX_access_token_uid already exists
Error: near line 19: index UQE_access_token_token_hash already exists
Error: near line 20: index IDX_access_token_created_unix already exists
Error: near line 21: index IDX_access_token_updated_unix already exists
Error: near line 24: index IDX_repository_is_fork already exists
Error: near line 25: index IDX_repository_fork_id already exists
Error: near line 26: index IDX_repository_template_id already exists
Error: near line 27: index UQE_repository_s already exists
Error: near line 28: index IDX_repository_is_template already exists
Error: near line 29: index IDX_repository_is_archived already exists
Error: near line 30: index IDX_repository_is_empty already exists
Error: near line 31: index IDX_repository_is_mirror already exists
Error: near line 32: index IDX_repository_updated_unix already exists
Error: near line 33: index IDX_repository_name already exists
Error: near line 34: index IDX_repository_lower_name already exists
Error: near line 35: index IDX_repository_original_service_type already exists
Error: near line 36: index IDX_repository_is_private already exists
Error: near line 37: index IDX_repository_created_unix already exists
Error: near line 38: index IDX_repository_owner_id already exists
Error: near line 39: no such column: false
Error: near line 40: no such column: false
Error: near line 43: index UQE_deploy_key_s already exists
Error: near line 44: index IDX_deploy_key_key_id already exists
Error: near line 45: index IDX_deploy_key_repo_id already exists
Error: near line 48: index IDX_collaboration_user_id already exists
Error: near line 49: index UQE_collaboration_s already exists
Error: near line 50: index IDX_collaboration_repo_id already exists
Error: near line 53: index UQE_access_s already exists
Error: near line 56: index UQE_upload_uuid already exists
Error: near line 59: index UQE_watch_watch already exists
Error: near line 64: index UQE_star_s already exists
Error: near line 67: index UQE_follow_follow already exists
Error: near line 70: index IDX_action_user_id already exists
Error: near line 71: index IDX_action_act_user_id already exists
Error: near line 72: index IDX_action_repo_id already exists
Error: near line 73: index IDX_action_comment_id already exists
Error: near line 74: index IDX_action_is_deleted already exists
Error: near line 75: index IDX_action_is_private already exists
Error: near line 76: index IDX_action_created_unix already exists
Error: near line 77: no such column: false
Error: near line 78: no such column: false
Error: near line 79: no such column: false
Error: near line 80: no such column: false
Error: near line 81: no such column: false
Error: near line 82: no such column: false
Error: near line 83: no such column: false
Error: near line 84: no such column: false
Error: near line 85: no such column: false
Error: near line 86: no such column: false
Error: near line 89: index IDX_issue_deadline_unix already exists
Error: near line 90: index IDX_issue_created_unix already exists
Error: near line 91: index IDX_issue_updated_unix already exists
Error: near line 92: index IDX_issue_closed_unix already exists
Error: near line 93: index IDX_issue_repo_id already exists
Error: near line 94: index IDX_issue_poster_id already exists
Error: near line 95: index IDX_issue_is_closed already exists
Error: near line 96: index IDX_issue_is_pull already exists
Error: near line 97: index UQE_issue_repo_index already exists
Error: near line 98: index IDX_issue_original_author_id already exists
Error: near line 99: index IDX_issue_milestone_id already exists
Error: near line 100: no such column: false
Error: near line 103: index IDX_pull_request_issue_id already exists
Error: near line 104: index IDX_pull_request_head_repo_id already exists
Error: near line 105: index IDX_pull_request_base_repo_id already exists
Error: near line 106: index IDX_pull_request_has_merged already exists
Error: near line 107: index IDX_pull_request_merger_id already exists
Error: near line 108: index IDX_pull_request_merged_unix already exists
Error: near line 111: index IDX_comment_review_id already exists
Error: near line 112: index IDX_comment_ref_issue_id already exists
Error: near line 113: index IDX_comment_ref_comment_id already exists
Error: near line 114: index IDX_comment_updated_unix already exists
Error: near line 115: index IDX_comment_poster_id already exists
Error: near line 116: index IDX_comment_issue_id already exists
Error: near line 117: index IDX_comment_created_unix already exists
Error: near line 118: index IDX_comment_ref_repo_id already exists
Error: near line 119: index IDX_comment_type already exists
Error: near line 122: index UQE_attachment_uuid already exists
Error: near line 123: index IDX_attachment_issue_id already exists
Error: near line 124: index IDX_attachment_release_id already exists
Error: near line 125: index IDX_attachment_uploader_id already exists
Error: near line 128: index IDX_label_repo_id already exists
Error: near line 131: index UQE_issue_label_s already exists
Error: near line 134: index IDX_milestone_repo_id already exists
Error: near line 137: index IDX_mirror_repo_id already exists
Error: near line 138: index IDX_mirror_updated_unix already exists
Error: near line 139: index IDX_mirror_next_update_unix already exists
Error: near line 142: index IDX_release_created_unix already exists
Error: near line 143: index UQE_release_n already exists
Error: near line 144: index IDX_release_repo_id already exists
Error: near line 145: index IDX_release_publisher_id already exists
Error: near line 146: index IDX_release_tag_name already exists
Error: near line 147: index IDX_release_original_author_id already exists
Error: near line 150: index UQE_login_source_name already exists
Error: near line 151: index IDX_login_source_is_actived already exists
Error: near line 152: index IDX_login_source_is_sync_enabled already exists
Error: near line 153: index IDX_login_source_created_unix already exists
Error: near line 154: index IDX_login_source_updated_unix already exists
Error: near line 157: index IDX_webhook_repo_id already exists
Error: near line 158: index IDX_webhook_org_id already exists
Error: near line 159: index IDX_webhook_is_active already exists
Error: near line 160: index IDX_webhook_created_unix already exists
Error: near line 161: index IDX_webhook_updated_unix already exists
Error: near line 164: index IDX_hook_task_repo_id already exists
Error: near line 167: index IDX_team_org_id already exists
Error: near line 170: index IDX_org_user_uid already exists
Error: near line 171: index UQE_org_user_s already exists
Error: near line 172: index IDX_org_user_org_id already exists
Error: near line 173: index IDX_org_user_is_public already exists
Error: near line 176: index UQE_team_user_s already exists
Error: near line 177: index IDX_team_user_org_id already exists
Error: near line 180: index IDX_team_repo_org_id already exists
Error: near line 181: index UQE_team_repo_s already exists
Error: near line 184: index IDX_notice_created_unix already exists
Error: near line 187: index IDX_email_address_uid already exists
Error: near line 188: index UQE_email_address_email already exists
Error: near line 191: index IDX_notification_commit_id already exists
Error: near line 192: index IDX_notification_created_unix already exists
Error: near line 193: index IDX_notification_updated_unix already exists
Error: near line 194: index IDX_notification_repo_id already exists
Error: near line 195: index IDX_notification_status already exists
Error: near line 196: index IDX_notification_source already exists
Error: near line 197: index IDX_notification_issue_id already exists
Error: near line 198: index IDX_notification_user_id already exists
Error: near line 199: index IDX_notification_updated_by already exists
Error: near line 202: index IDX_issue_user_uid already exists
Error: near line 203: no such column: true
Error: near line 206: index UQE_lfs_meta_object_s already exists
Error: near line 207: index IDX_lfs_meta_object_oid already exists
Error: near line 208: index IDX_lfs_meta_object_repository_id already exists
Error: near line 211: index UQE_two_factor_uid already exists
Error: near line 212: index IDX_two_factor_created_unix already exists
Error: near line 213: index IDX_two_factor_updated_unix already exists
Error: near line 216: index IDX_gpg_key_owner_id already exists
Error: near line 217: index IDX_gpg_key_key_id already exists
Error: near line 222: index IDX_repo_unit_s already exists
Error: near line 223: index IDX_repo_unit_created_unix already exists
Error: near line 236: index UQE_repo_redirect_s already exists
Error: near line 237: index IDX_repo_redirect_lower_name already exists
Error: near line 240: index IDX_external_login_user_provider already exists
Error: near line 241: index IDX_external_login_user_user_id already exists
Error: near line 244: index UQE_protected_branch_s already exists
Error: near line 247: index IDX_user_open_id_uid already exists
Error: near line 248: index UQE_user_open_id_uri already exists
Error: near line 251: index UQE_issue_watch_watch already exists
Error: near line 254: index UQE_commit_status_repo_sha_index already exists
Error: near line 255: index IDX_commit_status_index already exists
Error: near line 256: index IDX_commit_status_repo_id already exists
Error: near line 257: index IDX_commit_status_sha already exists
Error: near line 258: index IDX_commit_status_context_hash already exists
Error: near line 259: index IDX_commit_status_created_unix already exists
Error: near line 260: index IDX_commit_status_updated_unix already exists
Error: near line 263: index IDX_stopwatch_issue_id already exists
Error: near line 264: index IDX_stopwatch_user_id already exists
Error: near line 267: index IDX_tracked_time_issue_id already exists
Error: near line 268: index IDX_tracked_time_user_id already exists
Error: near line 271: index IDX_deleted_branch_deleted_by_id already exists
Error: near line 272: index IDX_deleted_branch_deleted_unix already exists
Error: near line 273: index IDX_deleted_branch_repo_id already exists
Error: near line 274: index UQE_deleted_branch_s already exists
Error: near line 277: index IDX_repo_indexer_status_repo_id already exists
Error: near line 280: index UQE_issue_dependency_issue_dependency already exists
Error: near line 283: index IDX_lfs_lock_repo_id already exists
Error: near line 284: index IDX_lfs_lock_owner_id already exists
Error: near line 287: index IDX_reaction_comment_id already exists
Error: near line 288: index IDX_reaction_user_id already exists
Error: near line 289: index IDX_reaction_created_unix already exists
Error: near line 290: index IDX_reaction_type already exists
Error: near line 291: index UQE_reaction_s already exists
Error: near line 292: index IDX_reaction_issue_id already exists
Error: near line 295: index IDX_issue_assignees_assignee_id already exists
Error: near line 296: index IDX_issue_assignees_issue_id already exists
Error: near line 299: index IDX_u2f_registration_user_id already exists
Error: near line 300: index IDX_u2f_registration_created_unix already exists
Error: near line 301: index IDX_u2f_registration_updated_unix already exists
Error: near line 304: index IDX_team_unit_org_id already exists
Error: near line 305: index UQE_team_unit_s already exists
Error: near line 308: index IDX_review_updated_unix already exists
Error: near line 309: index IDX_review_reviewer_id already exists
Error: near line 310: index IDX_review_issue_id already exists
Error: near line 311: index IDX_review_created_unix already exists
Error: near line 314: index IDX_oauth2_application_uid already exists
Error: near line 315: index UQE_oauth2_application_client_id already exists
Error: near line 316: index IDX_oauth2_application_created_unix already exists
Error: near line 317: index IDX_oauth2_application_updated_unix already exists
Error: near line 320: index IDX_oauth2_authorization_code_valid_until already exists
Error: near line 321: index UQE_oauth2_authorization_code_code already exists
Error: near line 324: index UQE_oauth2_grant_user_application already exists
Error: near line 325: index IDX_oauth2_grant_user_id already exists
Error: near line 326: index IDX_oauth2_grant_application_id already exists
Error: near line 329: index IDX_task_doer_id already exists
Error: near line 330: index IDX_task_owner_id already exists
Error: near line 331: index IDX_task_repo_id already exists
Error: near line 332: index IDX_task_status already exists
Error: near line 335: index IDX_topic_updated_unix already exists
Error: near line 336: index UQE_topic_name already exists
Error: near line 337: index IDX_topic_created_unix already exists
Error: near line 340: index UQE_repo_topic_s already exists
2

You’d have to start from an empty database to restore such backup. Try renaming the existing database file (if any) and creating a new one:

mv /var/lib/gitea/data/gitea.db /var/lib/gitea/data/gitea.db.old
sqlite3 /var/lib/gitea/data/gitea.db <gitea-db.sql
1 Like
3

Thanks for the answer.

after I do so it outputs

Error: near line 10: no such column: false
Error: near line 39: no such column: false
Error: near line 40: no such column: false
Error: near line 77: no such column: false
Error: near line 78: no such column: false
Error: near line 79: no such column: false
Error: near line 80: no such column: false
Error: near line 81: no such column: false
Error: near line 82: no such column: false
Error: near line 83: no such column: false
Error: near line 84: no such column: false
Error: near line 85: no such column: false
Error: near line 86: no such column: false
Error: near line 100: no such column: false
Error: near line 203: no such column: true

And then

service gitea restart

I cant seem to login with the credentials that I earlier used on the old instance.

4

Would you mind posting the first 100 lines of gitea-db.sql? So we can see what we’re dealing with. Make sure you redact any sensitive information if any.

5

Yeah sure here it is. I should have changed the email , my name and username and ssh key to something else. Otherwise those datas should not be sensitive.

 /*Generated by xorm v0.8.0.1015 2020-04-04 21:03:30, from sqlite3 to SQLITE3*/

    CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `lower_name` TEXT NOT NULL, `name` TEXT NOT NULL, `full_name` TEXT NULL, `email` TEXT NOT NULL, `keep_email_private` INTEGER NULL, `email_notifications_preference` TEXT DEFAULT 'enabled' NOT NULL, `passwd` TEXT NOT NULL, `passwd_hash_algo` TEXT DEFAULT 'pbkdf2' NOT NULL, `must_change_password` INTEGER DEFAULT 0 NOT NULL, `login_type` INTEGER NULL, `login_source` INTEGER DEFAULT 0 NOT NULL, `login_name` TEXT NULL, `type` INTEGER NULL, `location` TEXT NULL, `website` TEXT NULL, `rands` TEXT NULL, `salt` TEXT NULL, `language` TEXT NULL, `description` TEXT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL, `last_login_unix` INTEGER NULL, `last_repo_visibility` INTEGER NULL, `max_repo_creation` INTEGER DEFAULT -1 NOT NULL, `is_active` INTEGER NULL, `is_admin` INTEGER NULL, `allow_git_hook` INTEGER NULL, `allow_import_local` INTEGER NULL, `allow_create_organization` INTEGER DEFAULT 1 NULL, `prohibit_login` INTEGER DEFAULT 0 NOT NULL, `avatar` TEXT NOT NULL, `avatar_email` TEXT NOT NULL, `use_custom_avatar` INTEGER NULL, `num_followers` INTEGER NULL, `num_following` INTEGER DEFAULT 0 NOT NULL, `num_stars` INTEGER NULL, `num_repos` INTEGER NULL, `num_teams` INTEGER NULL, `num_members` INTEGER NULL, `visibility` INTEGER DEFAULT 0 NOT NULL, `repo_admin_change_team_access` INTEGER DEFAULT 0 NOT NULL, `diff_view_style` TEXT DEFAULT '' NOT NULL, `theme` TEXT DEFAULT '' NOT NULL);
    CREATE INDEX `IDX_user_last_login_unix` ON `user` (`last_login_unix`);
    CREATE INDEX `IDX_user_is_active` ON `user` (`is_active`);
    CREATE UNIQUE INDEX `UQE_user_lower_name` ON `user` (`lower_name`);
    CREATE UNIQUE INDEX `UQE_user_name` ON `user` (`name`);
    CREATE INDEX `IDX_user_created_unix` ON `user` (`created_unix`);
    CREATE INDEX `IDX_user_updated_unix` ON `user` (`updated_unix`);
    INSERT INTO `user` (`id`, `lower_name`, `name`, `full_name`, `email`, `keep_email_private`, `email_notifications_preference`, `passwd`, `passwd_hash_algo`, `must_change_password`, `login_type`, `login_source`, `login_name`, `type`, `location`, `website`, `rands`, `salt`, `language`, `description`, `created_unix`, `updated_unix`, `last_login_unix`, `last_repo_visibility`, `max_repo_creation`, `is_active`, `is_admin`, `allow_git_hook`, `allow_import_local`, `allow_create_organization`, `prohibit_login`, `avatar`, `avatar_email`, `use_custom_avatar`, `num_followers`, `num_following`, `num_stars`, `num_repos`, `num_teams`, `num_members`, `visibility`, `repo_admin_change_team_access`, `diff_view_style`, `theme`) VALUES (1, 'sucka', 'sucka', '', 'change@hotmail.com', false, 'enabled', 'ef5ca0f9200012a43cbd7906a63d921c261526a569b88068e84f78c1f7501d14f31182d67dd4cef3aa9db97810e6e7a27bb0', 'pbkdf2', false, 0, 0, '', 0, '', '', 'Zzal72Ta3O', 'kyaOrFcN6e', 'en-US', '', 1584733185, 1585509380, 1585509380, false, -1, true, true, false, false, true, false, '4b2c1304cc5e7534b2ab773f27f32589', 'mailfiltered@hotmail.com', false, 0, 0, 0, 2, 0, 0, 0, false, '', 'arc-green');

    CREATE TABLE IF NOT EXISTS `public_key` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `owner_id` INTEGER NOT NULL, `name` TEXT NOT NULL, `fingerprint` TEXT NOT NULL, `content` TEXT NOT NULL, `mode` INTEGER DEFAULT 2 NOT NULL, `type` INTEGER DEFAULT 1 NOT NULL, `login_source_id` INTEGER DEFAULT 0 NOT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL);
    CREATE INDEX `IDX_public_key_fingerprint` ON `public_key` (`fingerprint`);
    CREATE INDEX `IDX_public_key_owner_id` ON `public_key` (`owner_id`);
    INSERT INTO `public_key` (`id`, `owner_id`, `name`, `fingerprint`, `content`, `mode`, `type`, `login_source_id`, `created_unix`, `updated_unix`) VALUES (1, 1, 'hanged@arch', 'SHA256:changed', 'ssh-rsa edited-out hanged@arch', 2, 1, 0, 1584735357, 1586009979);

    CREATE TABLE IF NOT EXISTS `access_token` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `uid` INTEGER NULL, `name` TEXT NULL, `token_hash` TEXT NULL, `token_salt` TEXT NULL, `token_last_eight` TEXT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL);
    CREATE INDEX `IDX_access_token_uid` ON `access_token` (`uid`);
    CREATE UNIQUE INDEX `UQE_access_token_token_hash` ON `access_token` (`token_hash`);
    CREATE INDEX `IDX_access_token_created_unix` ON `access_token` (`created_unix`);
    CREATE INDEX `IDX_access_token_updated_unix` ON `access_token` (`updated_unix`);

    CREATE TABLE IF NOT EXISTS `repository` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `owner_id` INTEGER NULL, `lower_name` TEXT NOT NULL, `name` TEXT NOT NULL, `description` TEXT NULL, `website` TEXT NULL, `original_service_type` INTEGER NULL, `original_url` TEXT NULL, `default_branch` TEXT NULL, `num_watches` INTEGER NULL, `num_stars` INTEGER NULL, `num_forks` INTEGER NULL, `num_issues` INTEGER NULL, `num_closed_issues` INTEGER NULL, `num_pulls` INTEGER NULL, `num_closed_pulls` INTEGER NULL, `num_milestones` INTEGER DEFAULT 0 NOT NULL, `num_closed_milestones` INTEGER DEFAULT 0 NOT NULL, `is_private` INTEGER NULL, `is_empty` INTEGER NULL, `is_archived` INTEGER NULL, `is_mirror` INTEGER NULL, `status` INTEGER DEFAULT 0 NOT NULL, `is_fork` INTEGER DEFAULT 0 NOT NULL, `fork_id` INTEGER NULL, `is_template` INTEGER DEFAULT 0 NOT NULL, `template_id` INTEGER NULL, `size` INTEGER DEFAULT 0 NOT NULL, `is_fsck_enabled` INTEGER DEFAULT 1 NOT NULL, `close_issues_via_commit_in_any_branch` INTEGER DEFAULT 0 NOT NULL, `topics` TEXT NULL, `avatar` TEXT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL);
    CREATE INDEX `IDX_repository_is_fork` ON `repository` (`is_fork`);
    CREATE INDEX `IDX_repository_fork_id` ON `repository` (`fork_id`);
    CREATE INDEX `IDX_repository_template_id` ON `repository` (`template_id`);
    CREATE UNIQUE INDEX `UQE_repository_s` ON `repository` (`owner_id`,`lower_name`);
    CREATE INDEX `IDX_repository_is_template` ON `repository` (`is_template`);
    CREATE INDEX `IDX_repository_is_archived` ON `repository` (`is_archived`);
    CREATE INDEX `IDX_repository_is_empty` ON `repository` (`is_empty`);
    CREATE INDEX `IDX_repository_is_mirror` ON `repository` (`is_mirror`);
    CREATE INDEX `IDX_repository_updated_unix` ON `repository` (`updated_unix`);
    CREATE INDEX `IDX_repository_name` ON `repository` (`name`);
    CREATE INDEX `IDX_repository_lower_name` ON `repository` (`lower_name`);
    CREATE INDEX `IDX_repository_original_service_type` ON `repository` (`original_service_type`);
    CREATE INDEX `IDX_repository_is_private` ON `repository` (`is_private`);
    CREATE INDEX `IDX_repository_created_unix` ON `repository` (`created_unix`);
    CREATE INDEX `IDX_repository_owner_id` ON `repository` (`owner_id`);
    INSERT INTO `repository` (`id`, `owner_id`, `lower_name`, `name`, `description`, `website`, `original_service_type`, `original_url`, `default_branch`, `num_watches`, `num_stars`, `num_forks`, `num_issues`, `num_closed_issues`, `num_pulls`, `num_closed_pulls`, `num_milestones`, `num_closed_milestones`, `is_private`, `is_empty`, `is_archived`, `is_mirror`, `status`, `is_fork`, `fork_id`, `is_template`, `template_id`, `size`, `is_fsck_enabled`, `close_issues_via_commit_in_any_branch`, `topics`, `avatar`, `created_unix`, `updated_unix`) VALUES (5, 1, 'makefiles', 'makefiles', '', '', 0, '', 'master', 1, 0, 0, 1, 0, 0, 0, 0, 0, false, false, false, false, 0, false, 0, false, 0, 23528604, true, false, 'null', '', 1584740708, 1586009979);
    INSERT INTO `repository` (`id`, `owner_id`, `lower_name`, `name`, `description`, `website`, `original_service_type`, `original_url`, `default_branch`, `num_watches`, `num_stars`, `num_forks`, `num_issues`, `num_closed_issues`, `num_pulls`, `num_closed_pulls`, `num_milestones`, `num_closed_milestones`, `is_private`, `is_empty`, `is_archived`, `is_mirror`, `status`, `is_fork`, `fork_id`, `is_template`, `template_id`, `size`, `is_fsck_enabled`, `close_issues_via_commit_in_any_branch`, `topics`, `avatar`, `created_unix`, `updated_unix`) VALUES (6, 1, 'testcrypt', 'testcrypt', '', '', 0, '', 'master', 1, 0, 0, 0, 0, 0, 0, 0, 0, false, false, false, false, 0, false, 0, false, 0, 21101, true, false, 'null', '', 1584796898, 1584797036);

    CREATE TABLE IF NOT EXISTS `deploy_key` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `key_id` INTEGER NULL, `repo_id` INTEGER NULL, `name` TEXT NULL, `fingerprint` TEXT NULL, `mode` INTEGER DEFAULT 1 NOT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL);
    CREATE UNIQUE INDEX `UQE_deploy_key_s` ON `deploy_key` (`key_id`,`repo_id`);
    CREATE INDEX `IDX_deploy_key_key_id` ON `deploy_key` (`key_id`);
    CREATE INDEX `IDX_deploy_key_repo_id` ON `deploy_key` (`repo_id`);

    CREATE TABLE IF NOT EXISTS `collaboration` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `repo_id` INTEGER NOT NULL, `user_id` INTEGER NOT NULL, `mode` INTEGER DEFAULT 2 NOT NULL);
    CREATE INDEX `IDX_collaboration_user_id` ON `collaboration` (`user_id`);
    CREATE UNIQUE INDEX `UQE_collaboration_s` ON `collaboration` (`repo_id`,`user_id`);
    CREATE INDEX `IDX_collaboration_repo_id` ON `collaboration` (`repo_id`);

    CREATE TABLE IF NOT EXISTS `access` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `user_id` INTEGER NULL, `repo_id` INTEGER NULL, `mode` INTEGER NULL);
    CREATE UNIQUE INDEX `UQE_access_s` ON `access` (`user_id`,`repo_id`);

    CREATE TABLE IF NOT EXISTS `upload` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `uuid` UUID NULL, `name` TEXT NULL);
    CREATE UNIQUE INDEX `UQE_upload_uuid` ON `upload` (`uuid`);

    CREATE TABLE IF NOT EXISTS `watch` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `user_id` INTEGER NULL, `repo_id` INTEGER NULL, `mode` INTEGER DEFAULT 1 NOT NULL);
    CREATE UNIQUE INDEX `UQE_watch_watch` ON `watch` (`user_id`,`repo_id`);
    INSERT INTO `watch` (`id`, `user_id`, `repo_id`, `mode`) VALUES (5, 1, 5, 1);
    INSERT INTO `watch` (`id`, `user_id`, `repo_id`, `mode`) VALUES (6, 1, 6, 1);

    CREATE TABLE IF NOT EXISTS `star` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `uid` INTEGER NULL, `repo_id` INTEGER NULL);
    CREATE UNIQUE INDEX `UQE_star_s` ON `star` (`uid`,`repo_id`);

    CREATE TABLE IF NOT EXISTS `follow` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `user_id` INTEGER NULL, `follow_id` INTEGER NULL);
    CREATE UNIQUE INDEX `UQE_follow_follow` ON `follow` (`user_id`,`follow_id`);

    CREATE TABLE IF NOT EXISTS `action` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `user_id` INTEGER NULL, `op_type` INTEGER NULL, `act_user_id` INTEGER NULL, `repo_id` INTEGER NULL, `comment_id` INTEGER NULL, `is_deleted` INTEGER DEFAULT 0 NOT NULL, `ref_name` TEXT NULL, `is_private` INTEGER DEFAULT 0 NOT NULL, `content` TEXT NULL, `created_unix` INTEGER NULL);
    CREATE INDEX `IDX_action_user_id` ON `action` (`user_id`);
    CREATE INDEX `IDX_action_act_user_id` ON `action` (`act_user_id`);
    CREATE INDEX `IDX_action_repo_id` ON `action` (`repo_id`);
    CREATE INDEX `IDX_action_comment_id` ON `action` (`comment_id`);
    CREATE INDEX `IDX_action_is_deleted` ON `action` (`is_deleted`);
    CREATE INDEX `IDX_action_is_private` ON `action` (`is_private`);
    CREATE INDEX `IDX_action_created_unix` ON `action` (`created_unix`);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (6, 1, 1, 1, 5, 0, false, '', false, '', 1584740708);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (7, 1, 5, 1, 5, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"3cd042a5a71d69ed159647edad75b6f286a4a43e","Message":"initial commit\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-20T23:46:25+02:00"}],"CompareURL":""}', 1584740788);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (8, 1, 5, 1, 5, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"f59a83fa15f99d3da06369c5d59f52793fc36f94","Message":"added lxc test platform\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T10:27:12+02:00"}],"CompareURL":"sucka/makefiles/compare/3cd042a5a71d69ed159647edad75b6f286a4a43e...f59a83fa15f99d3da06369c5d59f52793fc36f94"}', 1584779236);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (9, 1, 5, 1, 5, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"66a162120cdf0016228f8b1342d2dac609987751","Message":"lxc added\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T10:58:47+02:00"}],"CompareURL":"sucka/makefiles/compare/f59a83fa15f99d3da06369c5d59f52793fc36f94...66a162120cdf0016228f8b1342d2dac609987751"}', 1584781128);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (10, 1, 5, 1, 5, 0, false, 'master', false, '{"Len":2,"Commits":[{"Sha1":"aaa059b6a3c93d9b1bb39aa943f13fe4da178cca","Message":"added terraformer\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T14:04:04+02:00"},{"Sha1":"c4adbeac282e613b9cdac2ebfb741516b0a19179","Message":" fixors on goinstall\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T13:07:20+02:00"}],"CompareURL":"sucka/makefiles/compare/66a162120cdf0016228f8b1342d2dac609987751...aaa059b6a3c93d9b1bb39aa943f13fe4da178cca"}', 1584792245);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (11, 1, 1, 1, 6, 0, false, '', false, '', 1584796898);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (12, 1, 5, 1, 6, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"c498ee63dc4ce7416c9ebfedaa3a9b3ec8eca38f","Message":"testi\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T15:22:02+02:00"}],"CompareURL":""}', 1584796937);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (13, 1, 5, 1, 6, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"b5d530c1272db27e3cdabf9605876efdbbb58d4c","Message":"testi\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-03-21T15:23:49+02:00"}],"CompareURL":"sucka/testcrypt/compare/c498ee63dc4ce7416c9ebfedaa3a9b3ec8eca38f...b5d530c1272db27e3cdabf9605876efdbbb58d4c"}', 1584797036);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (14, 1, 6, 1, 5, 0, false, '', false, '1|importante feature: Link files instead of copying. Also make the strucutre in better  way.', 1584996280);
    INSERT INTO `action` (`id`, `user_id`, `op_type`, `act_user_id`, `repo_id`, `comment_id`, `is_deleted`, `ref_name`, `is_private`, `content`, `created_unix`) VALUES (15, 1, 5, 1, 5, 0, false, 'master', false, '{"Len":1,"Commits":[{"Sha1":"8d1c6afb828404bdcaed511a2d12072fd3aac62a","Message":"loginf.conf lisäys\n","AuthorEmail":"mailfiltered@hotmail.com","AuthorName":"myname","CommitterEmail":"mailfiltered@hotmail.com","CommitterName":"myname","Timestamp":"2020-04-04T17:19:38+03:00"}],"CompareURL":"sucka/makefiles/compare/aaa059b6a3c93d9b1bb39aa943f13fe4da178cca...8d1c6afb828404bdcaed511a2d12072fd3aac62a"}', 1586009979);

    CREATE TABLE IF NOT EXISTS `issue` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `repo_id` INTEGER NULL, `index` INTEGER NULL, `poster_id` INTEGER NULL, `original_author` TEXT NULL, `original_author_id` INTEGER NULL, `name` TEXT NULL, `content` TEXT NULL, `milestone_id` INTEGER NULL, `priority` INTEGER NULL, `is_closed` INTEGER NULL, `is_pull` INTEGER NULL, `num_comments` INTEGER NULL, `ref` TEXT NULL, `deadline_unix` INTEGER NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL, `closed_unix` INTEGER NULL, `is_locked` INTEGER DEFAULT 0 NOT NULL);
    CREATE INDEX `IDX_issue_deadline_unix` ON `issue` (`deadline_unix`);
    CREATE INDEX `IDX_issue_created_unix` ON `issue` (`created_unix`);
    CREATE INDEX `IDX_issue_updated_unix` ON `issue` (`updated_unix`);
    CREATE INDEX `IDX_issue_closed_unix` ON `issue` (`closed_unix`);
    CREATE INDEX `IDX_issue_repo_id` ON `issue` (`repo_id`);
    CREATE INDEX `IDX_issue_poster_id` ON `issue` (`poster_id`);
    CREATE INDEX `IDX_issue_is_closed` ON `issue` (`is_closed`);
    CREATE INDEX `IDX_issue_is_pull` ON `issue` (`is_pull`);
    CREATE UNIQUE INDEX `UQE_issue_repo_index` ON `issue` (`repo_id`,`index`);
    CREATE INDEX `IDX_issue_original_author_id` ON `issue` (`original_author_id`);
    CREATE INDEX `IDX_issue_milestone_id` ON `issue` (`milestone_id`);
    INSERT INTO `issue` (`id`, `repo_id`, `index`, `poster_id`, `original_author`, `original_author_id`, `name`, `content`, `milestone_id`, `priority`, `is_closed`, `is_pull`, `num_comments`, `ref`, `deadline_unix`, `created_unix`, `updated_unix`, `closed_unix`, `is_locked`) VALUES (1, 5, 1, 1, '', 0, 'importante feature: Link files instead of copying. Also make the strucutre in better  way.', '', 0, 0, false, false, 0, '', 0, 1584996280, 1584996280, 0, false);
6

Is it possible that your backup was performed from a different kind of database? true and false don’t exist in SQLite3.

7

Thats weird.

I’ve accessed the old server that I took backup from.
app.ini says

DB_TYPE = sqlite3

It doesnt have mysql, nor postgresql installed in the server. So its kinda strange.

from the Server Configuration

Gitea Version
    1.11.3 built with GNU Make 4.1, go1.13.8 : bindata, sqlite, sqlite_unlock_notify

Database Configuration

Type
    sqlite3
Path
    /var/lib/gitea/data/gitea.db
8

Also I made the backup using this command
/usr/local/bin/gitea dump -c /etc/gitea/gitea.ini

9

I was going to ask that. There’s an option to specify the output format (-d value). It’s weird that you’d need to use it; it should use the setting from app.ini. Are you able to export the database again? (or maybe your old server is gone, I hope it’s not).

10

My app.ini is actually gitea.ini

From the gitea.service file I’ve had this.

ExecStart=/usr/local/bin/gitea web -c /etc/gitea/gitea.ini

The old server exists now as I’ve restored it, but need to get this backup and restore work so I can migrate it to new server and also have a sense of security as I do the backup once a while.

Wondering what are the -d value example values?

If I dont define the *.ini file it just says

2020/06/25 18:19:58 main.go:111:main() [F] Failed to run app with [/usr/local/bin/gitea dump]: Failed to connect to database: Unknown database type:

but throught the gitea.ini file which has it defined as sqlite3,…hmm

Also gitea -h command has no mention of -d value.

11

I think the valid -d values are the same as those in DB_TYPE. You can try dumping and checking the file; it should not show up any true or false columns in the inserts, like here:

INSERT INTO `issue` (`id`, `repo_id`, `index`, `poster_id`, `original_author`, `original_author_id`,
`name`, `content`, `milestone_id`, `priority`, `is_closed`, `is_pull`, `num_comments`, `ref`,
`deadline_unix`, `created_unix`, `updated_unix`, `closed_unix`, `is_locked`)
VALUES (1, 5, 1, 1, '', 0, 'importante feature: Link files instead of copying.
Also make the strucutre in better  way.', '', 0, 0,
false, false, 0, '', 0, 1584996280, 1584996280, 0, false);

(Check the last line; those “false” should be “0” instead).

12

gitea dump -d -c /etc/gitea/gitea.ini

I tried that command and it gave me.

2020/06/25 18:53:35 ...dules/setting/git.go:93:newGit() [I] Git Version: 2.17.1
2020/06/25 18:53:35 ...dules/setting/log.go:233:newLogService() [I] Gitea v1.12.1 built with GNU Make 4.1, go1.14.4 : bindata, sqlite, sqlite_unlock_notify
2020/06/25 18:53:35 ...dules/setting/log.go:279:newLogService() [I] Gitea Log Mode: Console(Console:info)
2020/06/25 18:53:35 ...les/setting/cache.go:70:newCacheService() [I] Cache Service Enabled
2020/06/25 18:53:35 ...les/setting/cache.go:81:newCacheService() [I] Last Commit Cache Service Enabled
2020/06/25 18:53:35 ...s/setting/session.go:63:newSessionService() [I] Session Service Enabled
2020/06/25 18:53:35 main.go:111:main() [F] Failed to run app with [gitea dump -d -c /etc/gitea/gitea.ini]: Failed to connect to database: Unknown database type:

Altho with

gitea dump -c /etc/gitea/gitea.ini
2020/06/25 18:54:49 …dules/setting/git.go:93:newGit() [I] Git Version: 2.17.1
and it creates the zip file etc.

13

What about…?

gitea dump -d sqlite3 -c /etc/gitea/gitea.ini
14

That worked but the database file also consists true and false… strange

15

If you’re still unable to solve this problem, it’s starting to sound like a bug. Please file an issue on https://github.com/go-gitea/gitea/issues

1 Like