Numeric value out of range: 167 Out of range value for column 'fileid'

[eshagh]

Steps to reproduce

1. upload file

Expected behaviour

file should be saved

Actual behaviour

Error noting file cannot be saved. Issue is filecache has the maximum number of entries for the INT value in fileid column. Is it possible to change fileid from INT to BIGINT? My initial attempt failed.

Server configuration

Operating system: FreeBSD

Web server: Apache24

Database: MariaDB 10

PHP version: 5.6

Nextcloud version: 11

Updated from an older Nextcloud/ownCloud or fresh install: updated (error manifested as soon as fileid maxed out INT value)

Where did you install Nextcloud from: manual

Signing status:

Signing status

Login as admin user into your Nextcloud and access 
http://example.com/index.php/settings/integrity/failed 
paste the results here.

List of activated apps:

App list

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder

The content of config/config.php:

Config report

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

or 

Insert your config.php content here
(Without the database password, passwordsalt and secret)

Are you using external storage, if yes which one: yes, local (reason for so many file entries)

Are you using encryption: no

Are you using an external user-backend, if yes which one: ActiveDirectory

LDAP configuration (delete this part if not used)

LDAP config

With access to your command line run e.g.:
sudo -u www-data php occ ldap:show-config
from within your Nextcloud installation folder

Without access to your command line download the data/owncloud.db to your local
computer or access your SQL server remotely and run the select query:
SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap';


Eventually replace sensitive data as the name/IP-address of your LDAP server or groups.

Client configuration

Browser:

Operating system:

Logs

Web server error log

Web server error log

``` Insert your webserver log here ```

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` Insert your Nextcloud log here ```

Browser log

Browser log

``` Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...

</details>

[MorrisJobke]

@karlitschek possible change of the filecache table 🙈

[MorrisJobke]

@karlitschek possible change of the filecache table 🙈

Max value is 2.147 billion roughly

[karlitschek]

uff. let's be careful here because of the well known migration issues. 👎 for now.

[eshagh]

It appears that even attempting to remove large swaths of data from the system does not alleviate the problem. I speculate that since there is an entry right at:

MariaDB [owncloud]> select MAX(fileid) from oc_filecache;
+-------------+
| MAX(fileid) |
+-------------+
| 2147477186 |
+-------------+
1 row in set (0.26 sec)

That even if previous entries are removed, no new entries may be made since the maximum value has already been reached.

When I manually convered fileid to BIGINT, the index appeared to restart at 0 and any new data was attempting to overwrite the fileid entry at 0.

Any suggestions for how to navigate out of this situation, even if that breaks my upgrade path, short of start over?

[LukasReschke]

@karlitschek @MorrisJobke SWITCH seems to face the same problem soon over at owncloud/core#26901 – so this requires some action.

[karlitschek]

Hmm. O.K. Then let's merge it. 👍 Any ideas how the smoothen the problem that an alter table on a full file cache table takes forever is welcome.

[tessus]

@karlitschek

Any ideas how the smoothen the problem that an alter table on a full file cache table takes forever is welcome.

Depending on the database engine, an export and import/load could be faster. One would have to run a few benchmarks though.

[MorrisJobke]

Fixed with #6563

[Claonilton]

update oc_filecache set mtime='1538395718' where fileid = 581607;

mtime -> 1538395718 is one mtime valid
fileid is the id with problem

[kesselb]

update oc_filecache set mtime='1538395718' where fileid = 581607;

mtime -> 1538395718 is one mtime valid
fileid is the id with problem

@Claonilton Mind to open a new issue? https://docs.nextcloud.com/server/16/admin_manual/configuration_database/bigint_identifiers.html