"The database is missing some indexes." and "Some columns in the database are missing a conversion to big int." with phpmyadmin and-or CRON job (can't use occ command in shared hosting)

database
nc14
nc15
ovh

#1

Hello,

I update my server since NC13. I am on a shared hosting by OVH.

Since the update to NC15, I have new alerts in the Administration> Overview section:

The database is missing some indexes. Due to the fact that they added indexes on big tables they were not added automatically. By running “occ db: add-missing-indices” those missing indexes could be added manually while the instance kept running. Once the indexes are added, they are usually much faster.

Missing index "owner_index" in table "oc_share".
Missing index "initiator_index" in table "oc_share".

Some columns in the database are missing a conversion to big int. 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 more details read the documentation page about this.

filecache.mtime
filecache.storage_mtime

In the past, and not having access to OCC Commands, I had found a solution to execute these commands via phpmyadmin which had worked during the transition to NC14 (previous different alerts):

ALTER TABLE oc_share ADD KEYshare_with_index (share_with) USING BTREE;
ALTER TABLE oc_share ADD KEYparent_index (parent) USING BTREE;
ALTER TABLE oc_filecache ADD KEYfs_mtime (mtime) USING BTREE;

Can someone write me phpmyadmin commands to fix these new errors:

occ db: add-missing-indices
-> “owner_index” in table “oc_share”
-> “initiator_index” in table “oc_share”

occ db: convert-filecache-bigint
-> filecache.mtime
-> filecache.storage_mtime

Maybe it’s also possible with a CRON job? But I do not know the formula to execute either.

I use PHP 7.3.

And maybe it’s a problem of the update and we have to wait for a fix?

Thanks a lot in advance :slight_smile:


Problems converting indices to bigint
How-to/FAQ WIKI
Through HTTPS: Missing the customization of the login screen and the display of some icons and fields (but invisible locations are clickable) - Since my first installation from NC13 to NC15 - Reacts differently according to the shared web hosting service
Onvert-filecache-bigint nach Update auf NC15
#2

Hello,

queries to run for fixing the issues:

ALTER TABLE `oc_share` ADD KEY `owner_index` (`uid_owner`) USING BTREE;
ALTER TABLE `oc_share` ADD KEY `initiator_index` (`uid_initiator`) USING BTREE;

I updated my system to NC15 and still trying to fix some performance issues. I will write down my findings since the system is running.


Missing Indices on NC15
#3

As for performance, two of my installations running 15.0 seem to be really fast.


#4

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.


Missing Indices on NC15
#5

Awesome ! :sunglasses:
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 :heart_eyes:


Fehlende Indizes in Datenbank (kein SSH)
Concerning the index-update just a question to SQL
#6

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';

#7

I updated my DB with the lines from @ffs69 and all works fine…

BUT :sob: 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


#8

@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;

#9

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'

#10

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.


#11

@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.


#12

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?


#13

@chrismy - the following query should fix your issue at mimetypes.id:

ALTER TABLE `oc_mimetypes` MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

#14

Thanks, worked fine.


#15

Which part is the database name?

MariaDB [(none)]>

ALTER TABLE oc_filecache MODIFY mtime bigint NOT NULL DEFAULT ‘0’;

ERROR 1046 (3D000): No database selected
MariaDB [(none)]>


#16

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';

#17

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

#18

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';


#19

Steps taken to solve the problem:

Fedora 29

  1. Stop apache server:
    systemctl stop httpd

  2. 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

  1. 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

  2. Locate php.ini:
    [root@pc nextcloud]# find /etc -name php.ini
    /etc/php.ini

  3. 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

  1. Restart mariadb (I don’t know if it was necessary but i did it anyway)
    systemctl restart mariadb.service

  2. Start apache server:
    systemctl start httpd

  3. Check " Security & setup warnings" in nextcloud settings.

  4. Stop apache server:
    systemctl stop httpd

  5. Check tables:
    [root@pc nextcloud]# sudo -u apache php occ db:convert-filecache-bigint
    All tables already up to date!

  6. 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

  7. Start apache:
    systemctl start httpd

All done.


#20

did you find a solution for that? My nextcloud has also this error :frowning: