Migrating Database from Mysql to SQLite3 Missing columns #6895

Closed
opened 2025-11-02 07:10:22 -06:00 by GiteaMirror · 2 comments
Owner

Originally created by @pecuna on GitHub (Feb 18, 2021).

  • Gitea version (or commit ref): 1.12.6 / 1.13.2
  • Git version:
  • Operating system: Armbian Buster
  • Database (use [x]):
    • MySQL
  • Can you reproduce the bug at https://try.gitea.io:
    • No
  • Log gist:

2021/02/18 00:26:41 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 682.809µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 635.561µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 342.571µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT id, version FROM version WHERE id=? LIMIT 1 [1] - 405.694µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 545.898µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 549.688µs
2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 644.477µs
2021/02/18 00:26:41 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #6/10 failed. Error: sync database struct error: Unknown col
"name"
seq 0, in index "user_UQE_user_name" of table user, columns [id" INTEGER lower_name" VARCHAR(255) NOT name" VARCHAR(255) NOT full_name" VARCHAR(255) email" VARCHAR(255) NOT keep_email_private" TINYINT passwd" VARCHAR(255) NOT must_change_password" TINYINT login_type" INTEGER login_source" BIGINT login_name" VARCHAR(255) type" INTEGER location" VARCHAR(255) website" VARCHAR(255) rands" VARCHAR(10) salt" VARCHAR(10) language" VARCHAR(5) created_unix" BIGINT updated_unix" BIGINT last_login_unix" BIGINT last_repo_visibility" TINYINT max_repo_creation" INTEGER is_active" TINYINT is_admin" TINYINT allow_git_hook" TINYINT allow_import_local" TINYINT allow_create_organization" TINYINT prohibit_login" TINYINT avatar" VARCHAR(2048) avatar_email" VARCHAR(255) use_custom_avatar" TINYINT num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER description" VARCHAR(255) num_teams" INTEGER num_members" INTEGER diff_view_style" VARCHAR(255) NOT theme" VARCHAR(255) NOT visibility" INTEGER email_notifications_preference" VARCHAR(20) NOT repo_admin_change_team_access" TINYINT passwd_hash_algo" VARCHAR(255) NOT is_restricted" TINYINT keep_activity_private" TINYINT ]
2021/02/18 00:26:41 routers/init.go:77:initDBEngine() [I] Backing off for 3 seconds

Description

Hi all!
I am using Gitea from some years now. Since the beginning the database was on MariaDB server.
Now I wan't to migrate to SQLite3 single db file.
I tried with 'mysql-to-sqlite3' python utility but as you can see the logs above there are some columns missing.
I found that BIGINT Primary key can't use Auto_Increment in SQLite3, so I changed all such primary keys to INT, and after that manully added AUTO_INCREMENT property for the column. But the main problem is the missing columns in the tables.
Is there official way to migrate DB from Mysql to SQLite3?
If I have to make a new install with SQLite db initially and then import just the data from Mysql dump, as a more proper way of doing things - I will.
Just to point out that the DB is under 7mb in MariaDB.

Originally created by @pecuna on GitHub (Feb 18, 2021). <!-- NOTE: If your issue is a security concern, please send an email to security@gitea.io instead of opening a public issue --> <!-- 1. Please speak English, this is the language all maintainers can speak and write. 2. Please ask questions or configuration/deploy problems on our Discord server (https://discord.gg/gitea) or forum (https://discourse.gitea.io). 3. Please take a moment to check that your issue doesn't already exist. 4. Make sure it's not mentioned in the FAQ (https://docs.gitea.io/en-us/faq) 5. Please give all relevant information below for bug reports, because incomplete details will be handled as an invalid report. --> - Gitea version (or commit ref): 1.12.6 / 1.13.2 - Git version: - Operating system: Armbian Buster <!-- Please include information on whether you built gitea yourself, used one of our downloads or are using some other package --> <!-- Please also tell us how you are running gitea, e.g. if it is being run from docker, a command-line, systemd etc. ---> <!-- If you are using a package or systemd tell us what distribution you are using --> - Database (use `[x]`): - [X] MySQL - Can you reproduce the bug at https://try.gitea.io: - [x] No - Log gist: <!-- It really is important to provide pertinent logs --> <!-- Please read https://docs.gitea.io/en-us/logging-configuration/#debugging-problems --> <!-- In addition, if your problem relates to git commands set `RUN_MODE=dev` at the top of app.ini --> > 2021/02/18 00:26:41 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 682.809µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 635.561µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 342.571µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT `id`, `version` FROM `version` WHERE `id`=? LIMIT 1 [1] - 405.694µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 545.898µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 549.688µs 2021/02/18 00:26:41 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 644.477µs 2021/02/18 00:26:41 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #6/10 failed. Error: sync database struct error: Unknown col "name" seq 0, in index "user_UQE_user_name" of table user, columns [id" INTEGER lower_name" VARCHAR(255) NOT name" VARCHAR(255) NOT full_name" VARCHAR(255) email" VARCHAR(255) NOT keep_email_private" TINYINT passwd" VARCHAR(255) NOT must_change_password" TINYINT login_type" INTEGER login_source" BIGINT login_name" VARCHAR(255) type" INTEGER location" VARCHAR(255) website" VARCHAR(255) rands" VARCHAR(10) salt" VARCHAR(10) language" VARCHAR(5) created_unix" BIGINT updated_unix" BIGINT last_login_unix" BIGINT last_repo_visibility" TINYINT max_repo_creation" INTEGER is_active" TINYINT is_admin" TINYINT allow_git_hook" TINYINT allow_import_local" TINYINT allow_create_organization" TINYINT prohibit_login" TINYINT avatar" VARCHAR(2048) avatar_email" VARCHAR(255) use_custom_avatar" TINYINT num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER description" VARCHAR(255) num_teams" INTEGER num_members" INTEGER diff_view_style" VARCHAR(255) NOT theme" VARCHAR(255) NOT visibility" INTEGER email_notifications_preference" VARCHAR(20) NOT repo_admin_change_team_access" TINYINT passwd_hash_algo" VARCHAR(255) NOT is_restricted" TINYINT keep_activity_private" TINYINT ] 2021/02/18 00:26:41 routers/init.go:77:initDBEngine() [I] Backing off for 3 seconds ## Description <!-- If using a proxy or a CDN (e.g. CloudFlare) in front of gitea, please disable the proxy/CDN fully and connect to gitea directly to confirm the issue still persists without those services. --> Hi all! I am using Gitea from some years now. Since the beginning the database was on MariaDB server. Now I wan't to migrate to SQLite3 single db file. I tried with 'mysql-to-sqlite3' python utility but as you can see the logs above there are some columns missing. I found that BIGINT Primary key can't use Auto_Increment in SQLite3, so I changed all such primary keys to INT, and after that manully added AUTO_INCREMENT property for the column. But the main problem is the missing columns in the tables. Is there official way to migrate DB from Mysql to SQLite3? If I have to make a new install with SQLite db initially and then import just the data from Mysql dump, as a more proper way of doing things - I will. Just to point out that the DB is under 7mb in MariaDB.
GiteaMirror added the type/question label 2025-11-02 07:10:22 -06:00
Author
Owner

@pecuna commented on GitHub (Feb 18, 2021):

I tried installing a new Gitea instance with SQLite3 DB, and after that import just the data from old Mysql.
After fixing again missing columns in some tables to match imported data, I've got the same issues.
The columns mentioned in the log are in place in user table. I tried to "modify" mentioned indexes but that didn't lead to successful start of Gitea.

2021/02/18 17:38:43 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 469.025µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 406.902µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 493.274µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT id, version FROM version WHERE id=? LIMIT 1 [1] - 435.651µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 433.318µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 498.274µs
2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 548.897µs
2021/02/18 17:38:43 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #2/10 failed. Error: sync database struct error: Unknown col
"lower_name"
seq 0, in index "UQE_user_lower_name" of table user, columns [id" INTEGER lower_name" TEXT name" TEXT full_name" TEXT email" TEXT keep_email_private" INTEGER email_notifications_preference" TEXT passwd" TEXT passwd_hash_algo" TEXT must_change_password" INTEGER login_type" INTEGER login_source" INTEGER login_name" TEXT type" INTEGER location" TEXT website" TEXT rands" TEXT salt" TEXT language" TEXT description" TEXT created_unix" INTEGER updated_unix" INTEGER last_login_unix" INTEGER last_repo_visibility" INTEGER max_repo_creation" INTEGER is_active" INTEGER is_admin" INTEGER is_restricted" INTEGER allow_git_hook" INTEGER allow_import_local" INTEGER allow_create_organization" INTEGER prohibit_login" INTEGER avatar" TEXT avatar_email" TEXT use_custom_avatar" INTEGER num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER num_teams" INTEGER num_members" INTEGER visibility" INTEGER repo_admin_change_team_access" INTEGER diff_view_style" TEXT theme" TEXT keep_activity_private" INTEGER ]
2021/02/18 17:38:43 routers/init.go:77:initDBEngine() [I] Backing off for 3 seconds
2021/02/18 17:38:46 routers/init.go:70:initDBEngine() [I] ORM engine initialization attempt #3/10...
2021/02/18 17:38:46 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 854.178µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 698.892µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 529.939µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT id, version FROM version WHERE id=? LIMIT 1 [1] - 755.348µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 748.098µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 964.257µs
2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 876.802µs
2021/02/18 17:38:46 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #3/10 failed. Error: sync database struct error: Unknown col
"name"
seq 0, in index "UQE_user_name" of table user, columns [id" INTEGER lower_name" TEXT name" TEXT full_name" TEXT email" TEXT keep_email_private" INTEGER email_notifications_preference" TEXT passwd" TEXT passwd_hash_algo" TEXT must_change_password" INTEGER login_type" INTEGER login_source" INTEGER login_name" TEXT type" INTEGER location" TEXT website" TEXT rands" TEXT salt" TEXT language" TEXT description" TEXT created_unix" INTEGER updated_unix" INTEGER last_login_unix" INTEGER last_repo_visibility" INTEGER max_repo_creation" INTEGER is_active" INTEGER is_admin" INTEGER is_restricted" INTEGER allow_git_hook" INTEGER allow_import_local" INTEGER allow_create_organization" INTEGER prohibit_login" INTEGER avatar" TEXT avatar_email" TEXT use_custom_avatar" INTEGER num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER num_teams" INTEGER num_members" INTEGER visibility" INTEGER repo_admin_change_team_access" INTEGER diff_view_style" TEXT theme" TEXT keep_activity_private" INTEGER ]

@pecuna commented on GitHub (Feb 18, 2021): I tried installing a new Gitea instance with SQLite3 DB, and after that import just the data from old Mysql. After fixing again missing columns in some tables to match imported data, I've got the same issues. The columns mentioned in the log are in place in user table. I tried to "modify" mentioned indexes but that didn't lead to successful start of Gitea. > 2021/02/18 17:38:43 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 469.025µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 406.902µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 493.274µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT `id`, `version` FROM `version` WHERE `id`=? LIMIT 1 [1] - 435.651µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 433.318µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 498.274µs 2021/02/18 17:38:43 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 548.897µs 2021/02/18 17:38:43 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #2/10 failed. Error: sync database struct error: Unknown col "lower_name" seq 0, in index "UQE_user_lower_name" of table user, columns [id" INTEGER lower_name" TEXT name" TEXT full_name" TEXT email" TEXT keep_email_private" INTEGER email_notifications_preference" TEXT passwd" TEXT passwd_hash_algo" TEXT must_change_password" INTEGER login_type" INTEGER login_source" INTEGER login_name" TEXT type" INTEGER location" TEXT website" TEXT rands" TEXT salt" TEXT language" TEXT description" TEXT created_unix" INTEGER updated_unix" INTEGER last_login_unix" INTEGER last_repo_visibility" INTEGER max_repo_creation" INTEGER is_active" INTEGER is_admin" INTEGER is_restricted" INTEGER allow_git_hook" INTEGER allow_import_local" INTEGER allow_create_organization" INTEGER prohibit_login" INTEGER avatar" TEXT avatar_email" TEXT use_custom_avatar" INTEGER num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER num_teams" INTEGER num_members" INTEGER visibility" INTEGER repo_admin_change_team_access" INTEGER diff_view_style" TEXT theme" TEXT keep_activity_private" INTEGER ] 2021/02/18 17:38:43 routers/init.go:77:initDBEngine() [I] Backing off for 3 seconds 2021/02/18 17:38:46 routers/init.go:70:initDBEngine() [I] ORM engine initialization attempt #3/10... 2021/02/18 17:38:46 ...rm/session_schema.go:25:Ping() [I] PING DATABASE sqlite3 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' and name = ? [version] - 854.178µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 698.892µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT * FROM version LIMIT 0 [] - 529.939µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT `id`, `version` FROM `version` WHERE `id`=? LIMIT 1 [1] - 755.348µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT name FROM sqlite_master WHERE type='table' [] - 748.098µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='table' and name = ? [user] - 964.257µs 2021/02/18 17:38:46 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT sql FROM sqlite_master WHERE type='index' and tbl_name = ? [user] - 876.802µs 2021/02/18 17:38:46 routers/init.go:76:initDBEngine() [E] ORM engine initialization attempt #3/10 failed. Error: sync database struct error: Unknown col "name" seq 0, in index "UQE_user_name" of table user, columns [id" INTEGER lower_name" TEXT name" TEXT full_name" TEXT email" TEXT keep_email_private" INTEGER email_notifications_preference" TEXT passwd" TEXT passwd_hash_algo" TEXT must_change_password" INTEGER login_type" INTEGER login_source" INTEGER login_name" TEXT type" INTEGER location" TEXT website" TEXT rands" TEXT salt" TEXT language" TEXT description" TEXT created_unix" INTEGER updated_unix" INTEGER last_login_unix" INTEGER last_repo_visibility" INTEGER max_repo_creation" INTEGER is_active" INTEGER is_admin" INTEGER is_restricted" INTEGER allow_git_hook" INTEGER allow_import_local" INTEGER allow_create_organization" INTEGER prohibit_login" INTEGER avatar" TEXT avatar_email" TEXT use_custom_avatar" INTEGER num_followers" INTEGER num_following" INTEGER num_stars" INTEGER num_repos" INTEGER num_teams" INTEGER num_members" INTEGER visibility" INTEGER repo_admin_change_team_access" INTEGER diff_view_style" TEXT theme" TEXT keep_activity_private" INTEGER ]
Author
Owner

@zuhairamahdi commented on GitHub (Feb 24, 2021):

Hi,

I had a similar issue (migrating from SQLite to MSSQL) and there is not straight forward way to doing it. best way was to export the data as script then make Gitea database connection to take SQLite (which will create a new database) then you migrate your data from the script. anyway I noticed a huge performance boost after migrating from SQLite to MSSQL. so if you asked me 'unless necessary' MariaDB is a better choice than SQLite.

@zuhairamahdi commented on GitHub (Feb 24, 2021): Hi, I had a similar issue (migrating from SQLite to MSSQL) and there is not straight forward way to doing it. best way was to export the data as script then make Gitea database connection to take SQLite (which will create a new database) then you migrate your data from the script. anyway I noticed a huge performance boost after migrating from SQLite to MSSQL. so if you asked me 'unless necessary' MariaDB is a better choice than SQLite.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#6895