Cannot restore gitea-db.sql from mysql to postgresql

[jtktam]

• 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

[lafriks]

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

[jtktam]

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

it's already quoted in the dump script

[6543]

@jtktam did you add the database argument?

./gitea dump --database postgres

[jtktam]

yes

[6543]

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

[ghost]

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?

[visteras]

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]

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 ...

[zeripath]

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.

[lunny]

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

[Adito5393]

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.