Repository navigation
fileid of oc_filecache in danger of hitting max value #26901
Description
Database
5 node galera cluster with mariadb.
maxscale with read/write splitter.
Expected behaviour
virtually unlimited lifetime of oc service
Actual behaviour
the primary key of the filecache table is in danger of overflowing.
At that point our oc instance will refuse any addition of new files.
Problem description
MariaDB [owncloud]> show create table oc_filecache\G
*************************** 1. row ***************************
Table: oc_filecache
Create Table: CREATE TABLE `oc_filecache` (
`fileid` int(11) NOT NULL AUTO_INCREMENT,
`storage` int(11) NOT NULL DEFAULT '0',
`path` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
`path_hash` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`parent` int(11) NOT NULL DEFAULT '0',
`name` varchar(250) COLLATE utf8_bin DEFAULT NULL,
`mimetype` int(11) NOT NULL DEFAULT '0',
`mimepart` int(11) NOT NULL DEFAULT '0',
`size` bigint(20) NOT NULL DEFAULT '0',
`mtime` int(11) NOT NULL DEFAULT '0',
`storage_mtime` int(11) NOT NULL DEFAULT '0',
`encrypted` int(11) NOT NULL DEFAULT '0',
`unencrypted_size` bigint(20) NOT NULL DEFAULT '0',
`etag` varchar(40) COLLATE utf8_bin DEFAULT NULL,
`permissions` int(11) DEFAULT '0',
PRIMARY KEY (`fileid`),
UNIQUE KEY `fs_storage_path_hash` (`storage`,`path_hash`),
KEY `fs_parent_name_hash` (`parent`,`name`),
KEY `fs_storage_mimetype` (`storage`,`mimetype`),
KEY `fs_storage_mimepart` (`storage`,`mimepart`),
KEY `fs_storage_size` (`storage`,`size`,`fileid`)
) ENGINE=InnoDB AUTO_INCREMENT=252069614 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
The primary key is a signed integer with max value of 2147483647.
MariaDB [owncloud]> select max(fileid), count(fileid) from oc_filecache;
+-------------+---------------+
| max(fileid) | count(fileid) |
+-------------+---------------+
| 252065705 | 60772002 |
+-------------+---------------+
We have 60 mio files and the fileid is currently at 250 mio.
4 months ago that counter was at 146 mio. During the last 4 months we had a 3 node galera cluster.
That means 100 Mio increase in 4 months; with our new 5 node cluster instead of a old 3 nodes cluster that would have been a: 100/3*5 = 170 mio increase. Therefore we'll hit the 2147483647 boundary in about 3.5 years with the current user base and setup. However, given the growth rate of our service, I'd estimate that to be more something like 2 years at best.
Considering, that enterprise customers (at least those I know of) usually are one year behind the current release, I think it is about time to fix this in the db.
Please consider changing the fileid to BIGINT. Or at the very least make it unsigned.
More of a hack than a workaround would be to change the auto_increment settings.
MariaDB [owncloud]> show variables like '%auto_increment%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
| wsrep_auto_increment_control | ON |
+------------------------------+-------+
But of course, that would give me tons of other head aches.
Activity
PVince81 commentedon Jan 9, 2017
Uh oh...
@butonic @DeepDiver1975 @PhilippSchaffrath
PVince81 commentedon Jan 9, 2017
@butonic this is the wrong ticket to discuss this, it will likely get lost. Maybe post here instead: #12487 (or find or create a matching ticket).
Yes for using bigint for fileid if that can fix the issue in this one ticket.
Helios07 commentedon Jan 9, 2017
It is even worse for us:
(and we split our users to different instances already)
PVince81 commentedon Jan 9, 2017
We usually don't change DB schemas in a minor version, but might need to do an exception here... @felixboehm @DeepDiver1975 @butonic
I suggest to provide a PR for a quickfix that changes the column type to bigint (not sure about performance impact here during upgrade...) and address @butonic's other points separately.
davidjericho commentedon Jan 20, 2017
This one hadn't crossed my mind, but it's definitely a problem for us too.
It'll be a huge problem with our planned growth this year.
butonic commentedon Jan 31, 2017
Um ... this will require us to alter the sharing table and probably other tables that reference the fileid ... foreign keys anyone?
PVince81 commentedon Jan 31, 2017
Ref: foreign keys #13143
Indeed, not only the oc_filecache will be affected.
Other tables that come to mind:
PVince81 commentedon Feb 10, 2017
Need to also evaluate the impact of bigger values on the clients especially Javascript.
PVince81 commentedon Feb 10, 2017
Or use strings for ids: #22330. Not sure about the performance impact with indices though.
butonic commentedon Apr 24, 2017
just using bigint in dhe db is not enuogh. we neet to check if the code does cast to int, eg: https://github.com/owncloud/core/blob/master/lib/private/Files/Cache/Cache.php#L145
+1 for strings for ids and get this right.
36 remaining items
PVince81 commentedon Aug 23, 2017
@VicDeo are we done with phase 1?
VicDeo commentedon Aug 23, 2017
@PVince81 in general yes, but richdocuments and music should be ported to migrations for completion.
And probably branching is needed to keep non-bigintified versions that are compatible with OC below 10.0.3
PVince81 commentedon Aug 30, 2017
moving to "planned" for second phase.
ckamm commentedon Sep 5, 2017
@PVince81 What's the effect on the full id of this change? Currently 'getFileId()' in
apps/dav/lib/Connector/Sabre/Node.phpstill does$id = sprintf('%08d', $this->info->getId());- so I assume the zero-padding to 8 digits continues, but full ids will now increase in width when the numeric id exceeds 8 digits.This works for the desktop client (takes all digits until first non-numeric character).
PVince81 commentedon Sep 5, 2017
@ckamm we should probably get rid of that weird compound id with padding and move to "oc:id" and have the instance id part exposed through the capabilities API ? (separate ticket)
phil-davis commentedon Sep 5, 2017
8 digits is only 99,999,999 = 100 million minus 1. 32 bit numbers anyway go to around 2,000,000,000 (signed) or 4,000,000,000 (unsigned) and thus would already be using 9 or 10 digits anyway (for installs with > 99,999,999 ids). So the 8-digit thing should be an existing "feature" regardless of 64-bit changes.
ckamm commentedon Sep 5, 2017
@phil-davis Good point.
@PVince81 Currently oc:id is the compound id and oc:fileid is the numeric one (opposite to the code in Node.php). We could start deprecating oc:id, the cost would be significant migration work in the client. I agree about moving this discussion to a separate ticket.
The bigint change looks good from the desktop client's point of view.
PVince81 commentedon Nov 3, 2017
Raised #29437 to follow up with phase 2 and other ids.
All the main ids (fileids and activity table ids) have been adjusted already, closing.
lock commentedon Jul 31, 2019
This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.