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

Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

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 :heart:

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:

  1. Log in and check the server status.
  2. Try to repair the issue "sudo -u apache php occ db:add-missing-indices
  3. 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!

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?

Just a basic question.

You run your occ command as webserver user?

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

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!

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

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

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

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?

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.

2 Likes

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

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.

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

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.

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.

2 Likes

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

Thanks, Felix! It worked wonderfully!

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!