richdocuments Numeric value out of range: 1264 Out of range value for column 'fileid' at row 1

trafficstars

Describe the bug Viewing documents now reports " Document loading failed" / "Unauthorised WOPI host. Please try again later and report to your administrator if the issue persists."

See the log at the bottom

To Reproduce Steps to reproduce the behavior:

Open any file in Nextcloud

Expected behavior The file opens

Screenshots If applicable, add screenshots to help explain your problem.

Client details:

OS: Ubuntu 24.04
Browser Firefox
Version 136.0.1
Device: Laptop

Server details

Operating system: Ubuntu 24.04

Web server: nginx version: nginx/1.24.0 (Ubuntu)

Database: Server version: 10.11.8-MariaDB-0ubuntu0.24.04.1-log Ubuntu 24.04

PHP version: PHP 8.3.6 (cli) (built: Dec 2 2024 12:36:18) (NTS)

Nextcloud version: Nextcloud Hub 10 (31.0.1)

Version of the richdocuments app 8.6.2

Version of Collabora Online collabora/code:24.04.13.2.1

Configuration of the richdocuments app

{
    "apps": {
        "richdocuments": {
            "canonical_webroot": "https:\/\/cloud.example.com",
            "disable_certificate_verification": "",
            "doc_format": "odf",
            "enabled": "yes",
            "external_apps": "",
            "installed_version": "8.6.2",
            "public_wopi_url": "https:\/\/loo.example.com",
            "types": "prevent_group_restriction",
            "use_groups": "",
            "wopi_allowlist": "192.168.1.1",
            "wopi_url": "https:\/\/loo.example.com"
        }
    }
}

Logs

Nextcloud log (data/nextcloud.log)

Omitted due to post size limit, will attach separately...

Browser log

NA

Mar 17 '25 19:03 PriceChild

nextcloud.log

Mar 17 '25 19:03 PriceChild

Saw #4547 which was closed without a clear answer?

Mar 17 '25 19:03 PriceChild

Saw #4547 which was closed without a clear answer?

I closed it because I fixed it myself.

For some reason, richdocuments had an outdated table definition.
I have deleted the richdocument table definitions and imported the current one.

Still did not work because richdocuments 8.6.2 and coolwsd 24.04.13.1 did not work due to another problem #4551.

Mar 17 '25 20:03 snetAT

FWIW:

> describe oc_richdocuments_wopi;
+----------------------+---------------------+------+-----+-----------+----------------+
| Field                | Type                | Null | Key | Default   | Extra          |
+----------------------+---------------------+------+-----+-----------+----------------+
| id                   | bigint(20) unsigned | NO   | PRI | NULL      | auto_increment |
| owner_uid            | varchar(64)         | YES  |     | NULL      |                |
| editor_uid           | varchar(64)         | YES  |     | NULL      |                |
| guest_displayname    | varchar(64)         | YES  |     | NULL      |                |
| fileid               | bigint(20) unsigned | NO   |     | NULL      |                |
| version              | bigint(20) unsigned | YES  |     | 0         |                |
| canwrite             | tinyint(1)          | YES  |     | 0         |                |
| server_host          | varchar(255)        | NO   |     | localhost |                |
| token                | varchar(32)         | YES  | UNI |           |                |
| expiry               | bigint(20) unsigned | YES  |     | NULL      |                |
| template_destination | bigint(20) unsigned | YES  |     | NULL      |                |
| hide_download        | tinyint(1)          | YES  |     | 0         |                |
| direct               | tinyint(1)          | YES  |     | 0         |                |
| remote_server        | varchar(255)        | YES  |     |           |                |
| remote_server_token  | varchar(32)         | YES  |     |           |                |
| template_id          | bigint(20) unsigned | YES  |     | NULL      |                |
| share                | varchar(64)         | YES  |     | NULL      |                |
| token_type           | int(11)             | YES  |     | 0         |                |
+----------------------+---------------------+------+-----+-----------+----------------+
18 rows in set (0.001 sec)

> describe oc_richdocuments_assets;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| uid       | varchar(64)         | YES  |     | NULL    |                |
| fileid    | bigint(20) unsigned | NO   |     | NULL    |                |
| token     | varchar(64)         | YES  | UNI | NULL    |                |
| timestamp | bigint(20) unsigned | YES  | UNI | 0       |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

> describe oc_richdocuments_direct;
+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| id                   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| token                | varchar(64)         | YES  | UNI | NULL    |                |
| uid                  | varchar(64)         | YES  |     | NULL    |                |
| fileid               | bigint(20) unsigned | NO   |     | NULL    |                |
| timestamp            | bigint(20) unsigned | YES  | MUL | 0       |                |
| template_destination | bigint(20) unsigned | YES  |     | NULL    |                |
| template_id          | bigint(20) unsigned | YES  |     | NULL    |                |
| share                | varchar(64)         | YES  |     | NULL    |                |
| initiator_host       | varchar(255)        | YES  |     | NULL    |                |
| initiator_token      | varchar(64)         | YES  |     | NULL    |                |
+----------------------+---------------------+------+-----+---------+----------------+
10 rows in set (0.001 sec)

> describe oc_richdocuments_template;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| userid     | varchar(64)         | YES  | MUL | NULL    |                |
| fileid     | bigint(20)          | NO   |     | NULL    |                |
| templateid | bigint(20)          | NO   |     | NULL    |                |
| timestamp  | bigint(20) unsigned | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.000 sec)

Mar 17 '25 20:03 PriceChild

this is my table definition that works

describe oc_richdocuments_wopi;

+----------------------+---------------------+------+-----+-----------+----------------+
| Field                | Type                | Null | Key | Default   | Extra          |
+----------------------+---------------------+------+-----+-----------+----------------+
| id                   | bigint(20) unsigned | NO   | PRI | NULL      | auto_increment |
| owner_uid            | varchar(64)         | YES  |     | NULL      |                |
| editor_uid           | varchar(64)         | YES  |     | NULL      |                |
| guest_displayname    | varchar(64)         | YES  |     | NULL      |                |
| fileid               | bigint(20)          | NO   |     | NULL      |                |
| version              | bigint(20)          | YES  |     | 0         |                |
| canwrite             | tinyint(1)          | YES  |     | 0         |                |
| server_host          | varchar(255)        | NO   |     | localhost |                |
| token                | varchar(32)         | YES  | UNI |           |                |
| expiry               | bigint(20) unsigned | YES  |     | NULL      |                |
| template_destination | bigint(20)          | YES  |     | NULL      |                |
| template_id          | bigint(20)          | YES  |     | NULL      |                |
| hide_download        | tinyint(1)          | YES  |     | 0         |                |
| direct               | tinyint(1)          | YES  |     | 0         |                |
| remote_server        | varchar(255)        | YES  |     |           |                |
| remote_server_token  | varchar(32)         | YES  |     |           |                |
| share                | varchar(64)         | YES  |     | NULL      |                |
| token_type           | int(11)             | YES  |     | 0         |                |
+----------------------+---------------------+------+-----+-----------+----------------+

describe oc_richdocuments_assets;

+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| uid       | varchar(64)         | YES  |     | NULL    |                |
| fileid    | bigint(20)          | NO   |     | NULL    |                |
| token     | varchar(64)         | YES  | UNI | NULL    |                |
| timestamp | bigint(20) unsigned | YES  | UNI | 0       |                |
+-----------+---------------------+------+-----+---------+----------------+

describe oc_richdocuments_direct;

+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| id                   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| token                | varchar(64)         | YES  | UNI | NULL    |                |
| uid                  | varchar(64)         | YES  |     | NULL    |                |
| fileid               | bigint(20)          | NO   |     | NULL    |                |
| timestamp            | bigint(20) unsigned | YES  | MUL | 0       |                |
| template_destination | bigint(20)          | YES  |     | NULL    |                |
| template_id          | bigint(20)          | YES  |     | NULL    |                |
| share                | varchar(64)         | YES  |     | NULL    |                |
| initiator_host       | varchar(255)        | YES  |     | NULL    |                |
| initiator_token      | varchar(64)         | YES  |     | NULL    |                |
+----------------------+---------------------+------+-----+---------+----------------+

describe oc_richdocuments_template;

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| userid     | varchar(64)         | YES  | MUL | NULL    |                |
| fileid     | bigint(20)          | NO   |     | NULL    |                |
| templateid | bigint(20)          | NO   |     | NULL    |                |
| timestamp  | bigint(20) unsigned | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

Mar 17 '25 20:03 snetAT

Same for me. Works again after Downgrade to collabora/code:24.04.11.3.1. Same issue with: collabora/code:24.04.12.4.1

Mar 21 '25 19:03 michag86

Same for me. Works again after Downgrade to collabora/code:24.04.11.3.1.

Here as well, nextcloud complained about Unauthorised WOPI host [...] when trying to open an office document, while investigating I also experienced the Numeric value out of range: 1264 Out of range value for column 'fileid' at row 1 database error in the logs and without changing anything a simple downgrade of collabora/code to 24.04.11.3.1 made things just work again.

Apr 10 '25 09:04 tgurr

For me, removing the UNSIGNED attribute from the fileid field in the oc_richdocuments_wopi table fixed the issue. I can upgrade both my collabora container and my richdocuments app again without any further issues. Pity that the occ maintenance:repair --happy-to-type-some-obscure-option-if-you-think-this-shouldnt-be-done-by-default command does not fix this automatically.

Apr 10 '25 09:04 drpetersen

For me, removing the UNSIGNED attribute from the fileid field in the oc_richdocuments_wopi table fixed the issue. I can upgrade both my collabora container and my richdocuments app again without any further issues. Pity that the occ maintenance:repair --happy-to-type-some-obscure-option-if-you-think-this-shouldnt-be-done-by-default command does not fix this automatically.

I can now more or less confirm that, though I removed the richdocuments app & database tables/entries completely by:

removing the tables:
oc_richdocuments_assets
oc_richdocuments_direct
oc_richdocuments_template
oc_richdocuments_wopi

remove from oc_migrations:
apps = richdocuments

remove from oc_appconfig:
appid: richdocuments

reinstalled & reconfigured the richdocuments app and after that the latest collabora/code version 24.04.13.2 works here as well. Your approach is the least invasive I guess, granting there are no other database changes lingering around that need migration(s). Would be nice to see this fixed with a future update of the richtdocuments app doing the required migration automatically.

Apr 10 '25 10:04 tgurr

MariaDB [nextcloud]> describe  oc_richdocuments_wopi;
+----------------------+---------------------+------+-----+-----------+----------------+
| Field                | Type                | Null | Key | Default   | Extra          |
+----------------------+---------------------+------+-----+-----------+----------------+
| id                   | bigint(20) unsigned | NO   | PRI | NULL      | auto_increment |
| owner_uid            | varchar(64)         | YES  |     | NULL      |                |
| editor_uid           | varchar(64)         | YES  |     | NULL      |                |
| guest_displayname    | varchar(64)         | YES  |     | NULL      |                |
| fileid               | bigint(20) unsigned | NO   |     | NULL      |                |
| version              | bigint(20) unsigned | YES  |     | 0         |                |
| canwrite             | tinyint(1)          | YES  |     | 0         |                |
| server_host          | varchar(255)        | NO   |     | localhost |                |
| token                | varchar(32)         | YES  | UNI |           |                |
| expiry               | bigint(20) unsigned | YES  |     | NULL      |                |
| template_destination | bigint(20) unsigned | YES  |     | NULL      |                |
| hide_download        | tinyint(1)          | YES  |     | 0         |                |
| direct               | tinyint(1)          | YES  |     | 0         |                |
| remote_server        | varchar(255)        | YES  |     |           |                |
| remote_server_token  | varchar(32)         | YES  |     |           |                |
| template_id          | bigint(20) unsigned | YES  |     | NULL      |                |
| share                | varchar(64)         | YES  |     | NULL      |                |
| token_type           | int(11)             | YES  |     | 0         |                |
+----------------------+---------------------+------+-----+-----------+----------------+
18 rows in set (0.003 sec)

MariaDB [nextcloud]> alter table oc_richdocuments_wopi modify column oc_richdocuments_wopi.fileid bigint(20);
Query OK, 1 row affected (0.088 sec)               
Records: 1  Duplicates: 0  Warnings: 0

Did indeed work for me.

I'm looking forward to the next database migration which'll inevitably fail!

Apr 10 '25 11:04 PriceChild

I'm looking forward to the next database migration which'll inevitably fail!

Indeed, there's definetly more than that single difference/occurance in regards to unsigned usage, for example I have two systems with the same current nextcloud/richdocuments versions installed, but the database at least in regards to oc_richdocuments_wopi on the older installation basically looks indentical to what you posted:

vs. newer one (which looks like from comment https://github.com/nextcloud/richdocuments/issues/4597#issuecomment-2730827197):

Apr 10 '25 12:04 tgurr

I am wondering if those of you who are experiencing this have an older installation and have ever used the occ richdocuments:convert-bigint command provided. This command seems to convert columns to bigint, while making them all unsigned.

https://github.com/nextcloud/richdocuments/blob/22a29a06cc5ed8f3c7501903c61998af286e0580/lib/Command/ConvertToBigInt.php#L81-L87

Otherwise, there is no reason it should be unsigned. Could you confirm?

Apr 14 '25 21:04 elzody

I've tested running occ richdocuments:convert-bigint. It only shows All tables already up to date!

~~But after upgrading to the latest collabora version 24.04.13.3 I cannot reproduce the problem.~~ Problem still exists. Maybe it does only occur on some files.

Apr 15 '25 04:04 michag86

Thank you for the clarification, @elzody.

I am wondering if those of you who are experiencing this have an older installation and have ever used the occ richdocuments:convert-bigint command provided. This command seems to convert columns to bigint, while making them all unsigned.

(…)

Otherwise, there is no reason it should be unsigned. Could you confirm?

I can confirm that my installation is quite old (from back when nextcloud split off of owncloud), and I do not specifically remember having ever invoked occ richdocuments:convert-bigint, but it rings a bell to me (might have been quite some time ago).

Now, all my fileid columns in the oc_richdocuments_* tables are bigint unsigned, except in the oc_richdocuments_wopi table, where I manually removed the unsigned attribute. Like @michag86 wrote and your (@elzody) code shows, this no longer gets fixed by the occ invocation, once the fields are bigints.

Theoretically it might be possible that at some point I manually changed the fileid fields to bigint unsigned, but I am quite sure that I would remember having done that. So my best guess is that some earlier version of the occ richdocuments:convert-bigint code did not yet contain the $column->setUnsigned(true); line, and that is when those of us affected by this issue had invoked that command.

Anyway, I learn that it is safe to remove the unsigned attribute from all fileid fields in the oc_richdocuments_* tables, right? And also from the version, template_destination, template_id fields? Anything else?

Apr 15 '25 08:04 drpetersen

@elzody that sounds about right to me!

Apr 15 '25 08:04 PriceChild

In this case, I understand now why the issue occurs. To resolve it, instead of manually executing sketchy database queries to change the schema, we will just not use a negative fileid anymore in the user settings logic which was recently added. I will create a pull request for this at some point soon.

Apr 16 '25 20:04 elzody

@elzody agree wholeheartedly that "sketchy database queries" need reverting but...

... I fear preventing negative fileid is fixing this specific problem and leaving plenty more latent possibilities?

It seems like many installations have ended up with different schemas depending on installation date and maintenance actions performed? Isn't it surprising that occ richdocuments: commands would leave us with a broken schema? Shouldn't we consider a bigger action to bring all installations back in line?

Apr 17 '25 11:04 PriceChild

Yes, it is surprising, and yes, the schema should be validated automatically, but that sounds like a larger issue that is outside the scope of this GitHub issue. I can create another issue for the schema validation and making sure things get applied retroactively should they need it in order to promote further discussion around this and how we might accomplish it.

Apr 17 '25 18:04 elzody

I removed all unsigned attributes on the oc_richdocuments_wopi table except id and expiry:

ALTER TABLE oc_richdocuments_wopi MODIFY COLUMN fileid BIGINT(20);
ALTER TABLE oc_richdocuments_wopi MODIFY COLUMN version BIGINT(20);
ALTER TABLE oc_richdocuments_wopi MODIFY COLUMN template_destination BIGINT(20);
ALTER TABLE oc_richdocuments_wopi MODIFY COLUMN template_id BIGINT(20);

Now the table looks like a new created one mentioned here: https://github.com/nextcloud/richdocuments/issues/4597#issuecomment-2792647722

It started working after removing it on fileid, but I wanted to get the same as a new install.

Apr 19 '25 16:04 michag86

richdocuments richdocuments copied to clipboard

Numeric value out of range: 1264 Out of range value for column 'fileid' at row 1

Server details

Nextcloud log (data/nextcloud.log)

Browser log

richdocuments
richdocuments copied to clipboard