Missing indexes after upgrade to NC18, db:add-missing-indices failing

hobbymaster001 · February 6, 2020, 7:22pm

Support intro

Sorry to hear you’re facing problems

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can

Nextcloud version (eg, 12.0.2): 18.0.0
Operating system and version (eg, Ubuntu 17.04): Centos 7
Apache or nginx version (eg, Apache 2.4.25): 2.4.6
PHP version (eg, 7.1): 7.3.14

The issue you are facing:
After upgrading to Nextcloud 18 I am getting the following message in the server status panel

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

Missing index "calendarobject_calid_index" in table "oc_calendarobjects_props".
Missing index "schedulobj_principuri_index" in table "oc_schedulingobjects"."

When running the db:add-missing-indices function it fails with the following:

Adding calendarobject_calid_index index to the calendarobjects_props table, this can take some time...

In AbstractMySQLDriver.php line 106:

  An exception occurred while executing 'CREATE INDEX calendarobject_calid_index ON oc_calendarobjects_props (calendarid, calendartype)':

  SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'calendarobject_value_index'


In PDOConnection.php line 90:

  SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'calendarobject_value_index'


In PDOConnection.php line 88:

  SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name 'calendarobject_value_index'

Is this the first time you’ve seen this error? (Y/N): Yes

Steps to replicate it:

Log in and check the server status.
Try to repair the issue "sudo -u apache php occ db:add-missing-indices
Try to manually sort out the issue, roll the DB back, and then ask for help…

The output of your Nextcloud log in Admin > Logging:

Absolutely nothing relevant here at all during the occ command run.

Does anyone know what indices I should have on this table and what the table structure should look like when this is all said and done? I can easily get it to that point if I have something to model it off of.

Thanks!

CFelix · February 6, 2020, 8:35pm

Can you try to connect to your database with the user and password in your config.php and execute the statement manually and see what happens?

rakekniven · February 6, 2020, 8:52pm

Just a basic question.

You run your occ command as webserver user?

hobbymaster001 · February 6, 2020, 8:53pm

That was one of the first troubleshooting steps I tried to be honest… exact same error to a tee.

hobbymaster001 · February 6, 2020, 8:54pm

Yup! Under steps to reproduce I have the command as “sudo -u apache php occ db:add-missing-indices” and my web server runs as the user apache. Wish it was that easy!

rakekniven · February 6, 2020, 8:58pm

One more basic question and I am sure you checked it already but sometimes it is better to do it twice :
Does your db user as sufficient rights to add/modify indices?

hobbymaster001 · February 6, 2020, 8:59pm

Yup it does! When I installed the calendar app it needed to build some indices and they completed just fine Always worth double checking though!

hobbymaster001 · February 6, 2020, 9:16pm

Just for reference and commands, could someone who doesn’t have this issue running NC18 let me know what their indexes look like please?

hobbymaster001 · February 6, 2020, 9:25pm

For additional information here… here is the current permission list and the schema for the table as well. Does this differ from anyone’s working instance?

hobbymaster001 · February 11, 2020, 4:06pm

For anyone else who is having this issue, I have solved it finally. As it turns out the calendarobject_value_index is no longer a required index, and it doesn’t seem to have been one for a VERY long time. I have been upgrading this instance since OwnCloud 5.x release so I have fully manually cleaned up the database. I purged the failing index, (also some legacy procedures, and a trigger) which completed successfully, so both value_index entries shown in my post above were gone.

I was then able to re-run the add-missing-indexes command and it completed successfully.

rakekniven · February 11, 2020, 5:54pm

Good catch. My install is from OC3 and my db is full of dead entries
But it works well

chrisnc · February 13, 2020, 3:34pm

New installation of

Nextcloud v18.0.0
PHP Version: 7.4.2
Database Type: mysql Version: 5.7.28

warnings

Missing index “calendarobject_calid_index” in table “dDzvw_calendarobjects_props”.
Missing index “schedulobj_principuri_index” in table “dDzvw_schedulingobjects”.

On shared hosting I have access to phpMyAdmin. Following Query:

ALTER TABLE dDzvw_calendarobjects_props DROP INDEX calendarobject_calid_index;

did give an error

#1091 - Can't DROP 'calendarobject_calid_index'; check that column/key exists

Sorry, I have no SQL knowledge so I can’t come up with own queries etc. Help appreciated.

CFelix · February 14, 2020, 7:38am

Hi chrisnc

Since the warning/error message says, that the index is missing, a ALTER TABLE…DROP would not work.

If you have SSH access, you can use the occ commands. If you don’t have SSH access, there is an app, occ-web I think it’s called, which you can use from your Nextcloud instance.

This is the link to the occ-web app: https://apps.nextcloud.com/apps/occweb (can be installed from within your admin account on Nextcloud)

This is the link to the occ commands documentation: https://docs.nextcloud.com/server/15/admin_manual/configuration_server/occ_command.html#add-missing-indices

adrielairaldo · April 28, 2020, 12:46pm

Hi, can anyone give me the SQL queries to manually add the missing indexes?

I am renting a server where I am not able to execute CLI commands.

Thanks in advance.

CFelix · April 28, 2020, 2:55pm

Try installing the occ-web App as mentioned in my previous post. This allows you to execute CLI commands without having to have access to SSH.

rakekniven · April 28, 2020, 6:58pm

You do not rent a server, or? Otherwise you should have shell access.

See How to use occ commands on a Shared Hosting without SSH access

adrielairaldo · April 28, 2020, 7:52pm

Thanks, Felix! It worked wonderfully!

adrielairaldo · April 28, 2020, 7:59pm

Hi @rakekniven .

The host where I’m hosting my NC version is private, and I don’t have shell access on it.

Thanks!

PS: Sorry, surely I’m using some uncommon verbs to express the situation, and that is why you don’t understand me (spanish native here).

PS2: Problem solved using @CFelix solution. Thanks!

DesertCookie · August 20, 2020, 9:25am

How do you do this with docker since OCC-Web does not support Nextcloud 19? I’ve attached to my docker container and run this command, but it doesn’t recognize it as a valid bash command.

CFelix · August 20, 2020, 9:39am

If you are using docker, I presume you have Shell access? If that is the case, you could just connect to your docker container and execute the OCC command from within the container.

Connect to docker container:

docker exec -it CONTAINER_NAME /bin/bash