Wrong defaults in gitea dump postgresql boolean fields

[tomwll]

• Gitea version (or commit ref): 1.12.3
• Git version: 2.20.1
• Operating system: r= Raspbian OS 10.4
• Database (use [x]):
  • [x] PostgreSQL
  • [ ] MySQL
  • [ ] MSSQL
  • [x] SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • [ ] Yes (provide example URL)
  • [ ] No
  • [x] Not relevant
• Log gist:

Description

The base scenario is to switch gitea from using sqlite as database to make it use PostgreSQL (PostrgreSQL 11.7 in this case, but should not matter). Result is that e. g. the user table is not being created, and thus users aren't being able to login into the "restored" postgresql instance.

One creates a gitea backup (original datbase store sqlite) with

./gitea dump --skip-log --database postgres -f gitea.dmp

Then extracts the dump

mkdir restore && unzip -d restore gitea.dmp

At this point a postgresql database named gitea exists on localhost, along with a user able to access that database. Restoring the database dump yields messages like:

psql -U gitea < restore/gitea-db.sql
... output omitted...
ERROR:  column "is_fork" is of type boolean but default expression is of type integer
HINT:  You will need to rewrite or cast the expression.

One can "fix" the SQL dump by running the command

sed -i.bak \
    -e 's/BOOL DEFAULT 0/BOOL DEFAULT false/g'  \
    -e 's/BOOL DEFAULT 1/BOOL DEFAULT true/' \
    restore/gitea-db.sql

This would set proper bool defaults for Postgres. After that, importing the dump works correctly and without errors.

Example: psql -U gitea < restore/gitea-db.sql

[lunny]

This should be a bug of xorm.

[tomwll]

Well, sequences generated by gitea dump are also off. The consequence is that you'll get duplicate key violations when adding new issues/labels/... in the migrated gitea instance.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. I am here to help clear issues left open even if solved or waiting for more insight. This issue will be closed if no further activity occurs during the next 2 weeks. If the issue is still valid just add a comment to keep it alive. Thank you for your contributions.