Missing referential integrity leads to database inconsistencies #12836

New Issue

2025-11-02T10:22:28-06:00

GiteaMirror commented

2025-11-02 10:22:28 -06:00

Originally created by @proxity on GitHub (Apr 12, 2024).

Description

We have pushed a high amount of maven packages and files into a well configured Gitea instance. We were also deleting release versions on Gitea and then pushing them again. All of this was done using a script. Esp. when doing parallel uploads with up to 16 CPUs it was possible to create invalid records in the database.
Here's how I verified it in our PostgreSQL v12.17:

Fatal inconsistencies leading to error 500

-- Package Files with non-existing Blob
select count(1) from package_file where not exists (select 1 from package_blob where id=package_file.blob_id);

Here we had one entry which made it impossible to use the package file. Therefore, we had to delete the defective record.

Non-fatal inconsistencies

-- Package Versions without a Package
select count(1) from package_version where not exists (select 1 from package where id=package_version.package_id);

-- Package Files without a Version
select count(1) from package_file where not exists (select 1 from package_version where id=package_file.version_id);

-- Blobs without a File
select count(1) from package_blob where not exists (select 1 from package_file where blob_id=package_blob.id);

Query 1 returned 0 which is fine. But query 2 returned a high number of orphaned files. And query 3 also returned a considerable number of orphaned blobs.
The good news is, since Gitea is iterating through the hierarchy from top to bottom, that orphaned records waste disk space, but do not show up as errors in the UI. We will delete them, including the data which is connected with the affected blobs.

Diagnosis

As I found out, XORM which is in use by gitea, doesn't support foreign keys yet. This leads to this kind of DB inconsistencies, since there's no referential integrity.

In package_file the version_id is a BIGINT NOT NULL.
What should be added is REFERENCES package_version, that would guarantee that no invalid value is possible.
In addition, ON DELETE CASCADE would automatically delete all package_files which package_version is deleted.

As it happens during concurrent uploads, I guess that the transaction handling is not perfect at the moment. Please fix the transactions if foreign keys cannot be implemented now.

Gitea Version

1.21.7

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

No response

How are you running Gitea?

We are running the official docker image docker.io/gitea/gitea:1.21.7 in a K8S cluster.

Database

PostgreSQL

Originally created by @proxity on GitHub (Apr 12, 2024). ### Description We have pushed a high amount of maven packages and files into a well configured Gitea instance. We were also deleting release versions on Gitea and then pushing them again. All of this was done using a script. Esp. when doing parallel uploads with up to 16 CPUs it was possible to create invalid records in the database. Here's how I verified it in our PostgreSQL v12.17: ### Fatal inconsistencies leading to error 500 ``` -- Package Files with non-existing Blob select count(1) from package_file where not exists (select 1 from package_blob where id=package_file.blob_id); ``` Here we had one entry which made it impossible to use the package file. Therefore, we had to delete the defective record. ### Non-fatal inconsistencies ``` -- Package Versions without a Package select count(1) from package_version where not exists (select 1 from package where id=package_version.package_id); -- Package Files without a Version select count(1) from package_file where not exists (select 1 from package_version where id=package_file.version_id); -- Blobs without a File select count(1) from package_blob where not exists (select 1 from package_file where blob_id=package_blob.id); ``` Query 1 returned 0 which is fine. But **query 2 returned a high number of orphaned files**. And **query 3 also returned a considerable number of orphaned blobs**. The good news is, since Gitea is iterating through the hierarchy from top to bottom, that orphaned records waste disk space, but do not show up as errors in the UI. We will delete them, including the data which is connected with the affected blobs. ### Diagnosis As I found out, XORM which is in use by gitea, doesn't support foreign keys yet. This leads to this kind of DB inconsistencies, since there's no referential integrity. In package_file the version_id is a BIGINT NOT NULL. What should be added is REFERENCES package_version, that would guarantee that no invalid value is possible. In addition, ON DELETE CASCADE would automatically delete all package_files which package_version is deleted. As it happens during concurrent uploads, I guess that the transaction handling is not perfect at the moment. Please fix the transactions if foreign keys cannot be implemented now. ### Gitea Version 1.21.7 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Screenshots _No response_ ### Git Version _No response_ ### Operating System _No response_ ### How are you running Gitea? We are running the official docker image docker.io/gitea/gitea:1.21.7 in a K8S cluster. ### Database PostgreSQL

GiteaMirror added the topic/packages type/bug labels 2025-11-02 10:22:28 -06:00

GiteaMirror commented

2025-11-02 10:22:29 -06:00

@tlusser commented on GitHub (Apr 15, 2024):

Is it also possible to create a check also to the gitea doctor command to cleanup and show any issues there?

@tlusser commented on GitHub (Apr 15, 2024): Is it also possible to create a check also to the gitea doctor command to cleanup and show any issues there?

GiteaMirror commented

2025-11-02 10:22:29 -06:00

@lunny commented on GitHub (Apr 15, 2024):

related to #30171 and should be fixed by #30335

@lunny commented on GitHub (Apr 15, 2024): related to #30171 and should be fixed by #30335

GiteaMirror commented

2025-11-02 10:22:29 -06:00

@proxity commented on GitHub (Apr 15, 2024):

@tlusser that's a good idea. gitea doctor check did not find any of my reported problems.

@proxity commented on GitHub (Apr 15, 2024): @tlusser that's a good idea. `gitea doctor check` did not find any of my reported problems.

GiteaMirror commented

2025-11-02 10:22:30 -06:00

@proxity commented on GitHub (Dec 18, 2024):

@lunny the problem is still present in 1.22.6. I've developed a bash script (attached as a text file) which pushes some random files into two different package versions. It can be used to reproduce the problem. The result is a damaged package version and Blobs without a File as queried by the last select statement mentioned above.
maven-test.sh.txt

@proxity commented on GitHub (Dec 18, 2024): @lunny the problem is still present in 1.22.6. I've developed a bash script (attached as a text file) which pushes some random files into two different package versions. It can be used to reproduce the problem. The result is a damaged package version and Blobs without a File as queried by the last select statement mentioned above. [maven-test.sh.txt](https://github.com/user-attachments/files/18186878/maven-test.sh.txt)

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#12836