DB missing index on NC 14

ℹ️ Support
, ,
1

I’ve updated to NC14 and I need to run

occ db:add-missing-index

but I am on Synology NAS and don’t have the ability to run occ command

How can I do ?

How can I add those indexes with SQL request ?

Index "share_with_index" missing on table "share"
Index "parent_index" missing on table "share"
Index "fs_mtime" missing on table "filecache"

Thanks

Regards,

Delor3an

2

If you have access to your database via cli or phpMyAdmin:

ALTER TABLE __nextcloud__.oc_share ADD INDEX share_with_index (share_with) USING BTREE;
ALTER TABLE __nextcloud__.oc_share ADD INDEX parent_index (parent) USING BTREE;

Replace __nextcloud__ with your database schema name.
I don’t have an index of fs_mtime in table fillecache.

1 Like
3

Hi timm2k,

What do you mean by database schema name ?

if my DB name is ‘test’ and table prefix is empty ‘’.

Is that correct ?

ALTER TABLE test.share ADD INDEX share_with_index (share_with) USING BTREE;
ALTER TABLE test.share ADD INDEX parent_index (parent) USING BTREE;

Regards,

Delor3an

4

correct :slight_smile:

5

Perfect!
For fs_mtime is:

My tables NOT use prefix_!!

ALTER TABLE oc_filecache ADD INDEX fs_mtime (mtime) USING BTREE;

(Not to my belly!
I have just installed nc 14 in a single installer and checked that I can not …)

6

I’ve just updated my Synology NC 13.0.7 to 14.0.3.
I got the same security warning to update my DB index and run that command. This is what I did to run it.

  • Login to your Synology CLI
  • go to the directory of you nextcloud web folder
  • run this command there: sudo -u http php70 occ db:add-missing-indices

Note1: If you don’t run the command in the correct folder it will give an error: “Could not open input file: occ”
Note2: This depends on you Synoligy setup, but DSM uses php 5.6.11 as default from terminal when using php cmd. NC14 require php7.0 (hens the php70 command).
Note3: If you have a task running for the cron job, you might want to change that to php70 as well.

7

Hi FinnTheHuman,

This command doesn’t work on DSM 6.2.1-23824

Regards

8

I’m running same version as you. What’s not working?
My setup:

  • Synology NAS with DSM 6.2.1-23824.
  • PHP 7.0.30 and MariaDB 10.3.7-0051

I made a post about my NC upgrade from 13.07 to 14.0.3 her:

Have a look and see if it can help

9

I’m also having trouble executing the command you posted.
The error message from shell:

PHP Warning:  PHP Startup: Unable to load dynamic library '/usr/local/lib/php70/modules/libsodium.so' - /usr/local/lib/php70/modules/libsodium.so: cannot open shared object file: No such file or directory in Unknown on line 0
An unhandled exception has been thrown:
Doctrine\DBAL\DBALException: Failed to connect to the database: An exception occured in driver: could not find driver in /volume1/web/nextcloud/lib/private/DB/Connection.php:64

System is DSM 6.2.1-23824, Nextcloud is configured to run with PHP7.0 since v13.x. Do you have an idea?

10

Hi,

@sascha224 I’ve got exactly the same error message !

@FinnTheHuman May you please let us know what did you do to make it works ?

It was OK to execute occ on DSM 6.0/6.1 with php56 but now it’s not working.

Regards

11

Well, I’m no expert on the matter, but this sounds like a PHP 7.0 configuration issue. And I have to be honest, the PHP configuration of the synology is hard to understand. But I’ll try to point you in some directions.

  • On Synology running PHP from CLI uses different php.ini files than PHP ran from web page
  • WebStation PHP settings can be set in the WebStation DSM web GUI, and PHP settings for CLI needs to be addressed from CLI.
  • It’s important that both php configurations point the extention_dir to the correct directory:
    Mine is set to:
    /volume3/@appstore/PHP7.0/usr/local/lib/php70/modules
    But yours could be on a different volume depending on your volume for @appstore.

To see your PHP configrations

  • running: php70 -ini from CLI will tell you where and what ini files are loaded when running php70 from CLI.
  • The same information for webstation can be obtained by loading a xxx.php file in the browser containing phpinfo() command
    ‘<?php phpinfo(); ?>’
    Of cause the settings from the configuration files is also available in DSM Web GUI for the WebStation.

I do see some other solutions to solve the php.ini issues. like using the -c command to direct php to a different path for php.ini file. It’s also possible, but I’ve not tried it.

  • It’s also important that mysqli and pdo_mysql are enabled for the PHP7 configurations. Though I would assume this is ok in your setup.

Hope it helps you a little bit on the way to find solutions.

12

Thanks for your help. I changed the path in php.ini of CLI, but then I got error messages that the module builds don’t match to the PHP build. But with the other way, parameter -c, I were successful. The profiles of DSM-PHP are located here:

/var/packages/WebStation/etc/php_profile

The only problem is to find out the correct profile you use for your Nextcloud setup, I would suggest to inspect the file /conf.d/user_settings.ini, here you can try to identify your needed PHP-profile via activated extensions. Then I had to execute the following command:

sudo -u http /usr/local/bin/php70 -c /var/packages/WebStation/etc/php_profile/<yourPHPprofileID>/conf.d/user_settings.ini -f /volume1/web/nextcloud/occ db:add-missing-indices

That worked for me and created the missing indices.

1 Like
13

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.