Cannot restore gitea-db.sql from mysql to postgresql #6473

New Issue

GiteaMirror · 2025-11-02T06:57:02-06:00

GiteaMirror commented

2025-11-02 06:57:02 -06:00

Originally created by @jtktam on GitHub (Dec 6, 2020).

Gitea version (or commit ref): 1.14.0+dev-321-g8561f2669
Git version:
Operating system: docker image from docker hub
Database (use [x]):
- PostgreSQL
- MySQL
- MSSQL
- SQLite
Can you reproduce the bug at https://try.gitea.io:
- Yes (provide example URL)
- No
Log gist:

Description

I am currently trying to migrate from mysql to postgresql as the backend database. I did a dump of the current mysql database and everything looks fine.

When I try to run the backup sql file (gitea-db.sql) it is failing on insert into the user table

when I use psql (with the same login credentials and datbase name) and do a "select * from user" i get the built in user table. I think this is why the inserts are failing

I tried to create the database table manually and set the search path to "public" only and it still picks up the built in user table.

what's the workaround?

thanks

Originally created by @jtktam on GitHub (Dec 6, 2020).   - Gitea version (or commit ref): 1.14.0+dev-321-g8561f2669 - Git version: - Operating system: docker image from docker hub    - Database (use `[x]`): - [x] PostgreSQL - [x] MySQL - [ ] MSSQL - [ ] SQLite - Can you reproduce the bug at https://try.gitea.io: - [ ] Yes (provide example URL) - [x] No - Log gist:    ## Description I am currently trying to migrate from mysql to postgresql as the backend database. I did a dump of the current mysql database and everything looks fine. When I try to run the backup sql file (gitea-db.sql) it is failing on insert into the user table when I use psql (with the same login credentials and datbase name) and do a "select * from user" i get the built in user table. I think this is why the inserts are failing I tried to create the database table manually and set the search path to "public" only and it still picks up the built in user table. what's the workaround? thanks

GiteaMirror added the type/question label 2025-11-02 06:57:02 -06:00

GiteaMirror closed this issue

2025-11-02 06:57:02 -06:00

GiteaMirror commented

2025-11-02 06:57:02 -06:00

@lafriks commented on GitHub (Dec 7, 2020):

Add double quotes around user table name, ex "user"

@lafriks commented on GitHub (Dec 7, 2020): Add double quotes around user table name, ex `"user"`

GiteaMirror commented

2025-11-02 06:57:03 -06:00

@jtktam commented on GitHub (Dec 8, 2020):

Add double quotes around user table name, ex "user"

it's already quoted in the dump script

@jtktam commented on GitHub (Dec 8, 2020): > Add double quotes around user table name, ex `"user"` it's already quoted in the dump script

GiteaMirror commented

2025-11-02 06:57:03 -06:00

@6543 commented on GitHub (Dec 8, 2020):

@jtktam did you add the database argument?

./gitea dump --database postgres

@6543 commented on GitHub (Dec 8, 2020): @jtktam did you add the database argument? `./gitea dump --database postgres`

GiteaMirror commented

2025-11-02 06:57:03 -06:00

@jtktam commented on GitHub (Dec 8, 2020):

yes

@jtktam commented on GitHub (Dec 8, 2020): yes

GiteaMirror commented

2025-11-02 06:57:03 -06:00

@6543 commented on GitHub (Dec 8, 2020):

can you test sed -i 's/\ user/\ `user`/g' gitea-db.sql ?

@6543 commented on GitHub (Dec 8, 2020): can you test ```sed -i 's/\ user/\ `user`/g' gitea-db.sql``` ?

GiteaMirror commented

2025-11-02 06:57:03 -06:00

@ghost commented on GitHub (Dec 29, 2020):

When I try to run the backup sql file (gitea-db.sql) it is failing on insert into the user table

What command did you use to try restoring that caused the failure? And what was the failure message?

@ghost commented on GitHub (Dec 29, 2020): > When I try to run the backup sql file (gitea-db.sql) it is failing on insert into the user table What command did you use to try restoring that caused the failure? And what was the failure message?

GiteaMirror commented

2025-11-02 06:57:04 -06:00

@visteras commented on GitHub (May 5, 2021):

I also tried to move from mysql (mariadb) to postgres, but only the first user was added (but the dump has everything), and in the version table there is only an id column (and no version column), this is what I noticed.
I see this error after start(migration): https://github.com/go-gitea/gitea/blob/main/models/migrations/migrations.go#L385

@visteras commented on GitHub (May 5, 2021): I also tried to move from mysql (mariadb) to postgres, but only the first user was added (but the dump has everything), and in the version table there is only an id column (and no version column), this is what I noticed. I see this error after start(migration): https://github.com/go-gitea/gitea/blob/main/models/migrations/migrations.go#L385

GiteaMirror commented

2025-11-02 06:57:04 -06:00

@visteras commented on GitHub (May 6, 2021):

User cannot inserted because keep_activity_private in mysql(mariadb) is null, but real field has status "not null".
And the release table has the correct number of rows, but the IDs are different, I don't know if this will lead to an error or not ...

@visteras commented on GitHub (May 6, 2021): User cannot inserted because keep_activity_private in mysql(mariadb) is null, but real field has status "not null". And the release table has the correct number of rows, but the IDs are different, I don't know if this will lead to an error or not ...

GiteaMirror commented

2025-11-02 06:57:04 -06:00

@zeripath commented on GitHub (May 10, 2021):

I suspect this is related to the issues I've noted with cross-db dumps in gitea. Ultimately the problem is that the cross db dump does a direct translation of the db schemas without reference to objects attached to those schemas. For most fields this doesn't cause a problem but it does when a db uses things like int for bool - e.g. MySQL. https://gitea.com/xorm/xorm/pulls/1872 and all of its follow-ups should fix this and will be in xorm 1.0.8 which will very likely be used by Gitea 1.15. (We'll have to think about whether backporting it is the right thing to do but we'll need serious testing.)

In the meantime users who want/need previews of this fixed functionality should contact me either on discord or by email. I can provide builds of 1.14.x (and perhaps earlier) with the fixed xorm PRs 1.0.8.x pulled in to gitea so that you can get a fixed migrations scripts and we can work through any issues.

@zeripath commented on GitHub (May 10, 2021): I suspect this is related to the issues I've noted with cross-db dumps in gitea. Ultimately the problem is that the cross db dump does a direct translation of the db schemas without reference to objects attached to those schemas. For most fields this doesn't cause a problem but it does when a db uses things like `int` for `bool` - e.g. MySQL. https://gitea.com/xorm/xorm/pulls/1872 and all of its follow-ups should fix this and will be in xorm 1.0.8 which will very likely be used by Gitea 1.15. (We'll have to think about whether backporting it is the right thing to do but we'll need serious testing.) In the meantime users who want/need previews of this fixed functionality should contact me either on discord or by email. I can provide builds of 1.14.x (and perhaps earlier) with the fixed xorm PRs 1.0.8.x pulled in to gitea so that you can get a fixed migrations scripts and we can work through any issues.

GiteaMirror commented

2025-11-02 06:57:04 -06:00

@lunny commented on GitHub (Jun 1, 2021):

Could you help to confirm that #15885 resolved the problem?

@lunny commented on GitHub (Jun 1, 2021): Could you help to confirm that #15885 resolved the problem?

GiteaMirror commented

2025-11-02 06:57:04 -06:00

@Adito5393 commented on GitHub (Jun 11, 2021):

I am planning to migrate my Gitea DB from MySQL to PostgreSQL, is this still a valid issue for v1.14.2?

If no one knows, I can try to migrate and see what errors/warnings I encounter.

@Adito5393 commented on GitHub (Jun 11, 2021): I am planning to migrate my Gitea DB from MySQL to PostgreSQL, is this still a valid issue for **v1.14.2**? If no one knows, I can try to migrate and see what errors/warnings I encounter.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#6473