As for performance, two of my installations running 15.0 seem to be really fast.
Hello,
with the following queries you can modify the database field type from int(11) to bigint(20):
ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';
This will solve the āocc db: convert-filecache-bigintā part.
Awesome !
So to synthesize sql requests via phpmyadmin and equivalent to OCC commands it gives:
Up to NC14:
ALTER TABLE `oc_share` ADD KEY `share_with_index` (`share_with`) USING BTREE; ALTER TABLE `oc_share` ADD KEY `parent_index` (`parent`) USING BTREE; ALTER TABLE `oc_filecache` ADD KEY `fs_mtime` (`mtime`) USING BTREE;
Up to NC15:
ALTER TABLE `oc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE; ALTER TABLE `oc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE; ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0'; ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';
Thank you very much to the community
Thanks a Million I had to do all of these.
Edit by @Schmu: Careful! The following queries are not correct and may corrupt your database. Donāt use them! Rather use the queries by @Maurice down below and only use them if you really cannot use the Nextcloud occ commands!
ALTER TABLE `oc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE;
ALTER TABLE `oc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE;
ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `storage` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `parent` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimetype` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimepart` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity` MODIFY `activity_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity` MODIFY `object_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity_mq` MODIFY `mail_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_mimetypes` MODIFY `id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_storages` MODIFY `numeric_id` bigint NOT NULL DEFAULT '0';
I updated my DB with the lines from @ffs69 and all works fineā¦
BUT After update my DB with the code from @bettsr down the line āfileIDā i get now a āInternal Server Errorā !!!
OOOHHH F****
And in the nextcloud.log File the entries explode with the following values
{āreqIdā:āXB4f3@3JfHdDuVtupSp21gAAABMā,ālevelā:3,ātimeā:ā2018-12-22T11:28:31+00:00ā,āremoteAddrā:ā217.231.174.201ā,āuserā:āWebmasterā,āappā:āindexā,āmethodā:āGETā,āurlā:"/index.php/apps/theming/styles?v=9",āmessageā:{āExceptionā:āRuntimeExceptionā,āMessageā:āFile entry could not be inserted but could also not be selected with getId() in order to perform an update. Please try again.ā,āCodeā:0,āTraceā:[{āfileā:"/www/htdocs/xxxx/nextcloud/lib/private/Files/Cache/Cache.php",ālineā:232,āfunctionā:āinsertā,āclassā:āOC\Files\Cache\Cacheā,ātypeā:"->",āargsā:[āappdata_ocw0h5ldt441/css/theming/ca9f-aecb-theming.css.depsā,{āmimetypeā:āapplication/octet-streamā,āmtimeā:1545476254,āsizeā:0,āetagā:ā18df4e41056878e105ef482d40afd805ā,āstorage_mtimeā:1545476254,āpermissionsā:27,āparentā:211,āchecksumā:"",āpathā:āappdata_ocw0h5ldt441/css/theming/ca9f-aecb-theming.css.depsā,ānameā:āca9f-aecb-theming.css.depsā}]},ā¦
It could be the same problem as here https://help.nextcloud.com/t/internal-server-error-after-update-nc14-to-nc-15/43701
So if anybody could help, this would be wonderful.
Thanks Ralf
@Ralf setting of fileID should look like this:
Try to fix the issue with the following query:
ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_filecache` MODIFY `storage` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `parent` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimetype` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mimepart` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_filecache` MODIFY `storage_mtime` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity` MODIFY `activity_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_activity` MODIFY `object_id` bigint NOT NULL DEFAULT '0';
ALTER TABLE `oc_activity_mq` MODIFY `mail_id` bigint NOT NULL AUTO_INCREMENT;
ALTER TABLE `oc_storages` MODIFY `numeric_id` bigint NOT NULL AUTO_INCREMENT;
I also used the lines from bettsr and nextcloud didnāt work anymore.
I tried you suggestion but phpmyadmin gave the following error:
Error
SQL query:
ALTER TABLE `oc_filecache` MODIFY `fileid` bigint NOT NULL AUTO_INCREMENT
MySQL said: Documentation
#1062 - ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
Hi @vanMacG
Well this is bad, sorry to hear that. We really need to take our time and check everything, before we suggest or make any changes to our databases. We can really mess things up.
This is actually a job for a database expert then or you take your time and read into this issue a bit more. I hope you have a backup of your database already.
I suggest you set your NC server in maintenance until this DB issue is fixed. When you set your server in maintenance create an additional backup right afterwards ā¦ before you perform any new changes.
I searched the net a bit and this here seem to be the best suggestions to solve your issue:
Pick one of the two highest voted solutions and try them. Personally Iād pick the answer with 31 upvotes first.
But perform some select statements before and get an idea of the tables content first. Is there an entry with fileid=0? Are there more than one entries with fileid=0 which need to be manually fixed afterwards?
By the way:
I read a post here in the forum, where a user of a hosted NC server mentioned the ability to configure cron jobs via the web gui (of his provider). He used this option to configure occ as cron job for a few minutes in the future, waited until the command ran and removed the cron job again.
Just mentioning this in case you have this option too and want to avoid messing with the DB manually next time.
@vanMacG - If you have value 0 on the column where you want to set AUTO_INCREMENT then you will get the following error:
ERROR 1062 (23000): ALTER TABLE causes AUTO_INCREMENT resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
Cause: It is not possible to set AUTO_INCREMENT on a column where value started with 0 because AUTO_INCREMENT starts from 1.
Solution: Update the row where FILEID is 0.
Thanks a lot. It worked fine with me. There ist one query missing for mimetypes.id. Could you please add the correct query for this field?
@chrismy - the following query should fix your issue at mimetypes.id:
ALTER TABLE `oc_mimetypes` MODIFY `id` bigint NOT NULL AUTO_INCREMENT;
Thanks, worked fine.
Which part is the database name?
MariaDB [(none)]>
ALTER TABLE
oc_filecache
MODIFYmtime
bigint NOT NULL DEFAULT ā0ā;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]>
Hi @jonathonp
The line you quoted, doesnāt contain any database name.
You can connect to the correct database for example during login already:
mysql -u myname -p <DATABASENAME>
Or can also connect to the database after your current way to login:
connect <DATABASENAME>
and then run the āalter tableā query.
Example:
> mysql -u oc_ncadmin -p
mysql> connect nextcloud_db
mysql> ALTER TABLE oc_filecache MODIFY mtime bigint NOT NULL DEFAULT '0';
I received the following error:
MariaDB [(none)]> connect nextcloud
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Connection id: 4084
Current database: nextcloud
MariaDB [nextcloud]> ALTER TABLE oc_filecache MODIFY mtime bigint NOT NULL DEFAULT ā0ā;
ERROR 1054 (42S22): Unknown column āā0āā in āDEFAULTā
MariaDB [nextcloud]>
It feels a bit risky running database commands that might result in a broken database and thus taking down my nextcloud server. My manual installation of nextcloud in fedora 29 is working well at the moment. I think it would be much safer if i create another database to test it out. What is the unknown column?
The problem that i am trying to solve:
- Some columns in the database are missing a conversion to big int. Due to the fact that changing column types on big tables could take some time they were not changed automatically. By running āocc db:convert-filecache-bigintā those pending changes could be applied manually. This operation needs to be made while the instance is offline. For further details read the documentation page about this.
- filecache.mtime
- filecache.storage_mtime
Definitely true. If you have the chance to use the occ commands, use that instead of running mysql queries.
I believe the unknown column error is caused by a copy and paste issue. With copy and pasting ā0ā you might get different characters than actually single ticks '0'
Because actually the column which is intended to be changed here is mtime
.
As suggested use occ command rather, if possible, run the occ web app (https://apps.nextcloud.com/apps/occweb) or try to copy the command from here:
ALTER TABLE oc_filecache MODIFY mtime bigint NOT NULL DEFAULT '0';
Steps taken to solve the problem:
Fedora 29
-
Stop apache server:
systemctl stop httpd -
Change directory and run nextcloud database conversion to big int:
[root@pc jonathon]# cd /var/www/html/nextcloud/
[root@pc nextcloud]# sudo -u apache php occ db:convert-filecache-bigint
The current PHP memory limit is below the recommended value of 512MB.
Following columns will be updated:
- filecache.mtime
- filecache.storage_mtime
This can take up to hours, depending on the number of files in your instance!
Continue with the conversion (y/n)? [n] y
-
Check php memory:
[root@pc nextcloud]# php -i | grep memory_limit
memory_limit => 128M => 128M
[root@pc nextcloud]# sudo -u apache php -i | grep memory_limit
memory_limit => 128M => 128M
[root@pc nextcloud]# php -i | grep memory_limit
memory_limit => 128M => 128M -
Locate php.ini:
[root@pc nextcloud]# find /etc -name php.ini
/etc/php.ini -
Edit php.ini:
vim /etc/php.ini
; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
Change
memory_limit = 128M
to
memory_limit = 512M
-
Restart mariadb (I donāt know if it was necessary but i did it anyway)
systemctl restart mariadb.service -
Start apache server:
systemctl start httpd -
Check " Security & setup warnings" in nextcloud settings.
-
Stop apache server:
systemctl stop httpd -
Check tables:
[root@pc nextcloud]# sudo -u apache php occ db:convert-filecache-bigint
All tables already up to date! -
Check that php memory has been updated:
[root@pc-bedroom nextcloud]# php -i | grep memory_limit
memory_limit => 512M => 512M
[root@pc-bedroom nextcloud]#
[root@pc nextcloud]# sudo -u apache php -i | grep memory_limit
memory_limit => 512M => 512M -
Start apache:
systemctl start httpd
All done.
did you find a solution for that? My nextcloud has also this error
mostly I got it back, one error left
occ $ maintenance:mimetype:update-db
In Loader.php line 130:Failed to get mimetype id for application/illustrator after trying to store
itmaintenance:mimetype:update-db [ārepair-filecache]
occ $
I have only occ web or database access via phpadmin. Can help anyone?
Micha
For those using PostgreSQL:
ALTER TABLE oc_filecache
ALTER COLUMN mtime SET DATA TYPE bigint,
ALTER COLUMN storage_mtime SET DATA TYPE bigint;
CREATE INDEX initiator_index ON oc_share USING btree (uid_initiator);
CREATE INDEX owner_index ON oc_share USING btree (uid_owner);