500 error accessing dashboard after upgrade to 1.16.1 from 1.15.10, caused by mismatched SQLite colmun type

[nicorac]

Gitea Version

1.16.1

Git Version

2.25.1

Operating System

Ubuntu Server 20.04 LTS

How are you running Gitea?

Running as service with binaries (gitea-1.16.1-linux-amd64).

Database

SQLite

Can you reproduce the bug on the Gitea demo site?

No

Description

After upgrading from 1.15.10 to 1.16.1 I get a 500 error when accessing the dashboard.
Other pages seem to work correctly if I open them directly (repos, issues, ...).

(the same issue happened a week ago when I tried to upgrade from 1.15.10 to 1.16.0 but I haven't opened an issue at that time due to lack of time, sorry)

Log contains only this relevant line:

.../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax

Database version before update was 189, after first run it was 209.

I've reverted back to 1.15.10 (both binaries and DB).

[lunny]

Could you paste more logs before this log and after?

[lunny]

And could you paste your database table action's structure?

[nicorac]

This is the first log line in gitea.log as of today, repeated more times (I hit F5 morte than once).
Then another appeared, maybe related.
The last line is my attempt to get back to 1.15.10 before restoring the DB:

2022/02/07 08:22:41 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:22:43 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:22:44 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:22:54 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:23:47 ...s/context/context.go:290:PlainTextBytes() [E] PlainTextBytes: Not found.
2022/02/07 08:23:47 ...s/context/context.go:290:PlainTextBytes() [E] PlainTextBytes: Not found.
2022/02/07 08:25:22 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:25:43 .../web/feed/profile.go:22:RetrieveFeeds() [E] GetFeeds: Find: converting driver.Value type string to a int64: invalid syntax
2022/02/07 08:26:48 ...ations/migrations.go:414:Migrate() [F] Downgrading database version from '209' to '189' is not supported and may result in loss of data integrity.
        If you really know what you're doing, execute `UPDATE version SET version=189 WHERE id=1;`

This is the requested table structure (in v1.15.10 DB):

CREATE TABLE [action] (
    id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    user_id      INTEGER,
    op_type      INTEGER,
    act_user_id  INTEGER,
    repo_id      INTEGER,
    comment_id   INTEGER,
    is_deleted   INTEGER DEFAULT 0 NOT NULL,
    ref_name     TEXT,
    is_private   INTEGER DEFAULT 0 NOT NULL,
    content      TEXT,
    created_unix INTEGER
);
CREATE INDEX IDX_action_user_id ON [action] (user_id);
CREATE INDEX IDX_action_act_user_id ON [action] (act_user_id);
CREATE INDEX IDX_action_repo_id ON [action] (repo_id);
CREATE INDEX IDX_action_comment_id ON [action] (comment_id);
CREATE INDEX IDX_action_is_deleted ON [action] (is_deleted);
CREATE INDEX IDX_action_is_private ON [action] (is_private);
CREATE INDEX IDX_action_created_unix ON [action] (created_unix);

[zeripath]

The error is coming from the sqlite DB itself and it indicates that one or more of the rows of action has a string value in one of its integer columns.

How this has happened I do not know. Gitea shouldn't have done this directly. Have you restored from a cross db backup previously?

The problem is that this cannot be automatically fixed. You're going to have to look at the sqlite DB yourself using sqlitebrowser or sqlite3. My suspicion is going to be that one of the is_ prefixed columns contains "true" and/or "false" when they should be 1 or 0.

[wxiaoguang]

Maybe related to

• Users and Organizations gone #17748 (comment)

The SQLite database was corrupted

[nicorac]

The database worked correctly up to 1.15.10.

Have you restored from a cross db backup previously?

My steps:

• stopped 1.15.10
• backed up the DB file
• downloaded 1.16.1 binary and started it
• got 500 error
• reverted to 1.15.10 and restored DB
• restarted 1.15.10... it still works

My suspicion is going to be that one of the is_ prefixed columns contains "true" and/or "false" when they should be 1 or 0.

This is a good starting point, thanks.
I'll check it out as soon as I can stop the service 👍

[zeripath]

There should be no need to stop the service to just read the data - sqlite will not take a lock unless you start writing to the db and once you write the lock will be released.

The issue would come if you start editing data but don't write it immediately as that will lock the table and gitea will hold-up until you release that lock.

[nicorac]

I've checked actions table content and none of the is_* columns contain text, only 0 and 1.

The first 5 records in the table (id 118 ->122) have some differences with all the others:

• they are the only one with is_private == 0
• 4 of them they have repo_id == 0
• ref_name contains a number instead of branch/tag names like "master", "integration", ...
• created_unix column is empty
• next id after these 5 rows is 359 (no IDs from 123 to 358...)

id	user_id	op_type	act_user_id	repo_id	comment_id	is_deleted	ref_name	is_private	content	created_unix
118	2	2	22	0	0	0	0	0
119	0	2	19	0	0	0	0	0
120	2	2	19	0	0	0	0	0
121	9	2	29	0	0	0	2	0
122	7	2	29	17	0	0	0	0
359	4	1	4	11	0	0		1		1549289674
360	1	1	4	11	0	0		1		1549289674
361	4	1	4	12	0	0		1		1549292689
362	1	1	4	12	0	0		1		1549292689
...(other 14000+ similar records, with consecutive IDs)

Could it be that those 5 leading records are causing the issue?

[lunny]

You can try to deleting them after you have a backup.

[nicorac]

You can try to deleting them after you have a backup.

It worked: after deleting those 5 records I can now access toe dashboard without errors, thanks!

Just wondering what I've lost by deleting them and if there's a way to fix and re-adding them back...

[wxiaoguang]

These records are not important, they are used used to show user activities, so you can safely delete them (I even truncated the action table before, Gitea still works correctly).

It seems that empty created_unix causes the bug. SQLite doesn't have data type inside.

Is your database created by a very old Gitea?

[nicorac]

These records are not important, they are used used to show user activities, so you can safely delete them

Ok, I've suspected that... can live without them 😉

Is your database created by a very old Gitea?

Yes it is.
I don't remember the version, but it surely was one of the very first ones...

[lunny]

But I think the created_unix should be there when action table created.

[nicorac]

But I think the created_unix should be there when action table created.

I think so, but it didn't cause any issue till 1.15.10.
I hope this thread will help any other one lucky as me 😄

[wxiaoguang]

But I think the created_unix should be there when action table created.

No, I have reviewed the whole history, it changed a lot.