"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)

[bettsr]

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

[Ralf]

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

[Maurice]

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

[vanMacG]

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'

[Schmu]

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:

MySql can't make column auto_increment

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.

[Maurice]

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

[chrismy]

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?

[Maurice]

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

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

[chrismy]

Thanks, worked fine.

[jonathonp]

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)]>

[Schmu]

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

[jonathonp]

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

[Schmu]

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

[jonathonp]

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

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

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

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

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

7. Start apache server:
   systemctl start httpd

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

9. Stop apache server:
   systemctl stop httpd

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

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

12. Start apache:
    systemctl start httpd

All done.

[Micha-Btz]

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

[Micha-Btz]

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
it

maintenance:mimetype:update-db [–repair-filecache]
occ $

I have only occ web or database access via phpadmin. Can help anyone?

Micha

[gsz]

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

[ffs69]

Hello everybody,
First of all I wish you all very happy celebrations!

I turn to you regarding the new alerts in my configuration since the update from 17.0.1 to 17.0.2:
“Some columns in the database are missing a conversion to big int. […]”
mounts.storage_id
mounts.root_id
mounts.mount_id

I don’t know how to identify the characteristics to make the necessary sql commands myself, if someone can help me?

I know it should look something like this:

ALTER TABLE `???` MODIFY `mounts.storage_id` bigint ???;
ALTER TABLE `???` MODIFY `mounts.root_id` bigint ???;
ALTER TABLE `???` MODIFY `mounts.mount_id` bigint ???;

But I don’t know how to identify in which table (oc_filecache, oc_mimetypes, oc_storages, …) and what value to configure for each column (NOT NULL DEFAULT ‘0’ or NOT NULL AUTO_INCREMENT)?

In advance thank you for your help and all wishes of happiness and good health to all

[ffs69]

Warning regarding need Bigint after 17.01 to 17.02 upgrade

Hello, I run nextcloud in a shared webhosting environment and have no ssh access to the server. Is there a way to trigger the conversion from php or SQL? edit: I just found the occweb app, but this seems to be not compatible with 17.0.2. It shows a warning. Are there other ways to get the columns converted? edit2: I was brave and used occweb Worked like a charm. Warnings are gone. Best, Lars

[kesselb]

@ffs69 https://github.com/nextcloud/server/issues/13704#issuecomment-567572073