Database conversion is a nightmare #13383

@delvh commented on GitHub (Aug 12, 2024):

Erm… How is that related to Gitea?
Switching DBs is always hard, no matter what application you are running.
Additionally, Gitea is not supposed to be doing anything there.
The gitea dump tool is intended for making backups of your current DB for the same DB.
For nothing else.
No one expected it to be able to do cross-DB migrations.

Making the switch easier is the job of other tools.
Gitea simply uses whatever DB you give it, and writes to it expecting the same type of DB.

I can understand that you are frustrated because of the switch, but still I don't think Gitea is the right place to blame here.
If anyone, blame the missing standards of DBs.

@delvh commented on GitHub (Aug 12, 2024): Erm… How is that related to Gitea? Switching DBs is always hard, no matter what application you are running. Additionally, Gitea is not supposed to be doing anything there. The `gitea dump` tool is intended for making backups of your current DB **for the same DB**. For nothing else. No one expected it to be able to do cross-DB migrations. Making the switch easier is the job of other tools. Gitea simply uses whatever DB you give it, and writes to it expecting the same type of DB. I can understand that you are frustrated because of the switch, but still I don't think Gitea is the right place to blame here. If anyone, blame the missing standards of DBs.

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 12, 2024):

Erm… How is that related to Gitea?

Because those DB incompatibilities make it necessary to have application-specific code to handle the conversion, it would seem.

Switching DBs is always hard

Did you mean "Software is always poorly engineered"?

no matter what application you are running.
Additionally, Gitea is not supposed to be doing anything there.

Nextcloud seems to have understood this problem and provides occ db:convert-type.

@sbourdeauducq commented on GitHub (Aug 12, 2024): > Erm… How is that related to Gitea? Because those DB incompatibilities make it necessary to have application-specific code to handle the conversion, it would seem. > Switching DBs is always hard Did you mean "Software is always poorly engineered"? > no matter what application you are running. > Additionally, Gitea is not supposed to be doing anything there. Nextcloud seems to have understood this problem and provides ``occ db:convert-type``.

GiteaMirror commented

https://postgresqlco.nf/doc/en/param/synchronous_commit/

@eeyrjmr commented on GitHub (Aug 13, 2024):

(2) you need to import the SQL file with SET synchronous_commit TO off; \i dump.sql otherwise the INSERT statements take forever to execute.

RECOMMENDATIONS
If data integrity is less important to you than response times (for example, if you are running a social networking application or processing logs) you can turn this off, making your transaction logs asynchronous

you opted to disable an integrity check for an import convenience that can and does expose you for an inconsistent database.

@eeyrjmr commented on GitHub (Aug 13, 2024): > (2) you need to import the SQL file with SET synchronous_commit TO off; \i dump.sql otherwise the INSERT statements take forever to execute. https://postgresqlco.nf/doc/en/param/synchronous_commit/ > RECOMMENDATIONS > If data integrity is **less important** to you than response times (for example, if you are running a social networking application or processing logs) you can turn this off, making your transaction logs asynchronous you opted to disable an integrity check for an import convenience that can and does expose you for an inconsistent database.

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 13, 2024):

you opted to disable an integrity check for an import convenience that can and does expose you for an inconsistent database.

Nothing crashed during the import, and also I had attempted twice and got exactly the same result (screenshot above), so most likely Psql did not corrupt the data and the problem is something else?

@sbourdeauducq commented on GitHub (Aug 13, 2024): > you opted to disable an integrity check for an import convenience that can and does expose you for an inconsistent database. Nothing crashed during the import, and also I had attempted twice and got exactly the same result (screenshot above), so most likely Psql did not corrupt the data and the problem is something else?

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 13, 2024):

The gitea dump tool is intended for making backups of your current DB for the same DB.
For nothing else.
No one expected it to be able to do cross-DB migrations.

One could expect it to produce the same output regardless of the underlying database, from the principles of consistency and separation of concerns, and a fortiori be able to do DB migrations.

Making the switch easier is the job of other tools.

Could you point to one that f****ng works?

@sbourdeauducq commented on GitHub (Aug 13, 2024): > The gitea dump tool is intended for making backups of your current DB for the same DB. > For nothing else. > No one expected it to be able to do cross-DB migrations. One could expect it to produce the same output regardless of the underlying database, from the principles of consistency and separation of concerns, and a fortiori be able to do DB migrations. > Making the switch easier is the job of other tools. Could you point to one that f****ng works?

GiteaMirror commented

@KN4CK3R commented on GitHub (Aug 14, 2024):

The gitea dump tool is intended for making backups of your current DB for the same DB. For nothing else. No one expected it to be able to do cross-DB migrations.

I don't think that's right. We have the --database parameter to explicit generate a dump for different database type.

@KN4CK3R commented on GitHub (Aug 14, 2024): > The `gitea dump` tool is intended for making backups of your current DB **for the same DB**. For nothing else. No one expected it to be able to do cross-DB migrations. I don't think that's right. We have the `--database` parameter to explicit generate a dump for different database type.

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

This one also does not work:
https://github.com/go-gitea/gitea/issues/5651#issuecomment-661953408

KABOOM!
SIMPLE-ERROR: pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\""
An unhandled error condition has been signalled:
   pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\""




What I am doing here?

pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\""

@sbourdeauducq commented on GitHub (Aug 17, 2024): This one also does not work: https://github.com/go-gitea/gitea/issues/5651#issuecomment-661953408 ``` KABOOM! SIMPLE-ERROR: pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\"" An unhandled error condition has been signalled: pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\"" What I am doing here? pgloader failed to find column "public"."repository"."is_bare" in target table "\"public\".\"repository\"" ```

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

gitea=> alter table repository add column is_bare BOOL;
ALTER TABLE

And then:

pgloader failed to find column "public"."hook_task"."repo_id" in target table "\"public\".\"hook_task\""

What a clusterfuck.

@sbourdeauducq commented on GitHub (Aug 17, 2024): ``` gitea=> alter table repository add column is_bare BOOL; ALTER TABLE ``` And then: ``` pgloader failed to find column "public"."hook_task"."repo_id" in target table "\"public\".\"hook_task\"" ``` What a clusterfuck.

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

Tried importing only the data from the gitea dump with the schema having already been created by gitea into postgresql. Same bug as before:

Render failed, failed to render template: user/dashboard/dashboard, error: template error: builtin(static):user/dashboard/feeds:94:55 : executing "user/dashboard/feeds" at <$repo.ComposeMetas>: error calling ComposeMetas: runtime error: invalid memory address or nil pointer dereference
----------------------------------------------------------------------
									{{RenderCommitMessage $.Context .Message $repo.ComposeMetas}}
									                                              ^
----------------------------------------------------------------------

@sbourdeauducq commented on GitHub (Aug 17, 2024): Tried importing only the data from the ``gitea dump`` with the schema having already been created by gitea into postgresql. Same bug as before: ``` Render failed, failed to render template: user/dashboard/dashboard, error: template error: builtin(static):user/dashboard/feeds:94:55 : executing "user/dashboard/feeds" at <$repo.ComposeMetas>: error calling ComposeMetas: runtime error: invalid memory address or nil pointer dereference ---------------------------------------------------------------------- {{RenderCommitMessage $.Context .Message $repo.ComposeMetas}} ^ ---------------------------------------------------------------------- ```

GiteaMirror commented

@eeyrjmr commented on GitHub (Aug 17, 2024):

Have you used gitea doctor to check and repair any inconsistencies in the database prior to export

@eeyrjmr commented on GitHub (Aug 17, 2024): Have you used **gitea doctor** to check and repair any inconsistencies in the database prior to export

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

No, but it doesn't seem there were any.

[1] Check paths and basic configuration
 - [I] Configuration File Path:    "/var/lib/gitea/custom/conf/app.ini"
 - [I] Repository Root Path:       "/var/lib/gitea/repositories"
 - [I] Data Root Path:             "/var/lib/gitea/data"
 - [I] Custom File Root Path:      "/var/lib/gitea/custom"
 - [I] Work directory:             "/var/lib/gitea"
 - [I] Log Root Path:              "/var/lib/gitea/log"
 - [I] Static File Root Path:      "/nix/store/5cl1q3a3ng1nh8xr1dzybr8jsn2iyixa-gitea-1.21.11-data"
OK

[2] Check Database Version
 - [I] Expected database version: 280
OK

[3] Check if user with wrong type exist
OK

[4] Check if OpenSSH authorized_keys file is up-to-date
OK

[5] Synchronize repo HEADs
 - [I] All 201 repos have their HEADs in the correct state
OK

All done (checks: 5).

@sbourdeauducq commented on GitHub (Aug 17, 2024): No, but it doesn't seem there were any. ``` [1] Check paths and basic configuration - [I] Configuration File Path: "/var/lib/gitea/custom/conf/app.ini" - [I] Repository Root Path: "/var/lib/gitea/repositories" - [I] Data Root Path: "/var/lib/gitea/data" - [I] Custom File Root Path: "/var/lib/gitea/custom" - [I] Work directory: "/var/lib/gitea" - [I] Log Root Path: "/var/lib/gitea/log" - [I] Static File Root Path: "/nix/store/5cl1q3a3ng1nh8xr1dzybr8jsn2iyixa-gitea-1.21.11-data" OK [2] Check Database Version - [I] Expected database version: 280 OK [3] Check if user with wrong type exist OK [4] Check if OpenSSH authorized_keys file is up-to-date OK [5] Synchronize repo HEADs - [I] All 201 repos have their HEADs in the correct state OK All done (checks: 5). ```

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

gitea migrate does print some warnings on the other hand.

2024/08/17 17:38:29 cmd/migrate.go:33:runMigrate() [I] AppPath: /nix/store/48qww9cmj7kzl0xixmwxa7z3cnd63m81-gitea-1.21.11/bin/gitea
2024/08/17 17:38:29 cmd/migrate.go:34:runMigrate() [I] AppWorkPath: /var/lib/gitea
2024/08/17 17:38:29 cmd/migrate.go:35:runMigrate() [I] Custom path: /var/lib/gitea/custom
2024/08/17 17:38:29 cmd/migrate.go:36:runMigrate() [I] Log path: /var/lib/gitea/log
2024/08/17 17:38:29 cmd/migrate.go:37:runMigrate() [I] Configuration file: /var/lib/gitea/custom/conf/app.ini
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [I] PING DATABASE sqlite3
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table system_setting Column version db default is , struct default is 1
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table email_address Column lower_email db nullable is true, struct nullable is false
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table user Column keep_activity_private db default is , struct default is 0
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table user Column keep_activity_private db nullable is true, struct nullable is false
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table repository Column original_service_type db default is 0, struct default is 
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table repository has column is_bare but struct has not related field
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table project Column card_type db default is 0, struct default is 
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table project Column card_type db nullable is false, struct nullable is true
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table hook_task has column repo_id but struct has not related field
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column is_system_webhook db default is 0, struct default is 
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column is_system_webhook db nullable is false, struct nullable is true
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column http_method db default is 'POST', struct default is 
2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table tracked_time Column time db nullable is true, struct nullable is false

I don't understand the crash and cannot tell if those are related.

@sbourdeauducq commented on GitHub (Aug 17, 2024): gitea migrate does print some warnings on the other hand. ``` 2024/08/17 17:38:29 cmd/migrate.go:33:runMigrate() [I] AppPath: /nix/store/48qww9cmj7kzl0xixmwxa7z3cnd63m81-gitea-1.21.11/bin/gitea 2024/08/17 17:38:29 cmd/migrate.go:34:runMigrate() [I] AppWorkPath: /var/lib/gitea 2024/08/17 17:38:29 cmd/migrate.go:35:runMigrate() [I] Custom path: /var/lib/gitea/custom 2024/08/17 17:38:29 cmd/migrate.go:36:runMigrate() [I] Log path: /var/lib/gitea/log 2024/08/17 17:38:29 cmd/migrate.go:37:runMigrate() [I] Configuration file: /var/lib/gitea/custom/conf/app.ini 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [I] PING DATABASE sqlite3 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table system_setting Column version db default is , struct default is 1 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table email_address Column lower_email db nullable is true, struct nullable is false 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table user Column keep_activity_private db default is , struct default is 0 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table user Column keep_activity_private db nullable is true, struct nullable is false 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table repository Column original_service_type db default is 0, struct default is 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table repository has column is_bare but struct has not related field 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table project Column card_type db default is 0, struct default is 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table project Column card_type db nullable is false, struct nullable is true 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table hook_task has column repo_id but struct has not related field 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column is_system_webhook db default is 0, struct default is 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column is_system_webhook db nullable is false, struct nullable is true 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table webhook Column http_method db default is 'POST', struct default is 2024/08/17 17:38:29 ...2@v2.25.7/command.go:267:Run() [W] Table tracked_time Column time db nullable is true, struct nullable is false ``` I don't understand the crash and cannot tell if those are related.

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

Fixed those with doctor recreate-table but this one persists

2024/08/17 17:41:22 ...2@v2.25.7/command.go:267:Run() [W] Table system_setting Column version db default is , struct default is 1

@sbourdeauducq commented on GitHub (Aug 17, 2024): Fixed those with ``doctor recreate-table`` but this one persists ``` 2024/08/17 17:41:22 ...2@v2.25.7/command.go:267:Run() [W] Table system_setting Column version db default is , struct default is 1 ```

GiteaMirror commented

@eeyrjmr commented on GitHub (Aug 17, 2024):

No, but it doesn't seem there were any.

[1] Check paths and basic configuration
 - [I] Configuration File Path:    "/var/lib/gitea/custom/conf/app.ini"
 - [I] Repository Root Path:       "/var/lib/gitea/repositories"
 - [I] Data Root Path:             "/var/lib/gitea/data"
 - [I] Custom File Root Path:      "/var/lib/gitea/custom"
 - [I] Work directory:             "/var/lib/gitea"
 - [I] Log Root Path:              "/var/lib/gitea/log"
 - [I] Static File Root Path:      "/nix/store/5cl1q3a3ng1nh8xr1dzybr8jsn2iyixa-gitea-1.21.11-data"
OK

[2] Check Database Version
 - [I] Expected database version: 280
OK

[3] Check if user with wrong type exist
OK

[4] Check if OpenSSH authorized_keys file is up-to-date
OK

[5] Synchronize repo HEADs
 - [I] All 201 repos have their HEADs in the correct state
OK

All done (checks: 5).

Doctor can do more checks.
gitea doctor check --all
There is a --fix option to git gitea the ok to fix what it can

The gitea dump to a different database schema will create an SQL dump that should be compatible with the target database. There could be bugs with the xorm interface that produces an invalid dump file as it is advise to use the native db dump for backups.
Databases are not trivial and equally are far from compatible between themselves

Gitea talks to postgrs via xorm and it is those commands that are dumped but if postgrs needs or does others things internally to keep the database consistent to itself ... This is where the gap will be

@eeyrjmr commented on GitHub (Aug 17, 2024): > No, but it doesn't seem there were any. > > ``` > [1] Check paths and basic configuration > - [I] Configuration File Path: "/var/lib/gitea/custom/conf/app.ini" > - [I] Repository Root Path: "/var/lib/gitea/repositories" > - [I] Data Root Path: "/var/lib/gitea/data" > - [I] Custom File Root Path: "/var/lib/gitea/custom" > - [I] Work directory: "/var/lib/gitea" > - [I] Log Root Path: "/var/lib/gitea/log" > - [I] Static File Root Path: "/nix/store/5cl1q3a3ng1nh8xr1dzybr8jsn2iyixa-gitea-1.21.11-data" > OK > > [2] Check Database Version > - [I] Expected database version: 280 > OK > > [3] Check if user with wrong type exist > OK > > [4] Check if OpenSSH authorized_keys file is up-to-date > OK > > [5] Synchronize repo HEADs > - [I] All 201 repos have their HEADs in the correct state > OK > > All done (checks: 5). > ``` Doctor can do more checks. gitea doctor check --all There is a --fix option to git gitea the ok to fix what it can The gitea dump to a different database schema will create an SQL dump that *should* be compatible with the target database. There could be bugs with the xorm interface that produces an invalid dump file as it is advise to use the native db dump for backups. Databases are not trivial and equally are far from compatible between themselves Gitea talks to postgrs via xorm and it is those commands that are dumped but if postgrs needs or does others things internally to keep the database consistent to itself ... This is where the gap will be

GiteaMirror commented

@sbourdeauducq commented on GitHub (Aug 17, 2024):

Any ideas in particular about what could cause error calling ComposeMetas: runtime error: invalid memory address or nil pointer dereference ?

@sbourdeauducq commented on GitHub (Aug 17, 2024): Any ideas in particular about what could cause ``error calling ComposeMetas: runtime error: invalid memory address or nil pointer dereference`` ?

GiteaMirror commented