Incorrect index name 'card_value_index'

Aiko · November 12, 2019, 2: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): 16.0.6
Operating system and version (eg, Ubuntu 17.04): Centos 7
Apache or nginx version (eg, Apache 2.4.25): Apache/2.4.6
PHP version (eg, 7.1): PHP 7.2.20

The issue you are facing:
After running “occ db:add-missing-indices” it returns

Check indices of the share table.
Check indices of the filecache table.
Check indices of the twofactor_providers table.
Check indices of the login_flow_v2 table.
Check indices of the whats_new table.
Check indices of the cards table.
Check indices of the cards_properties table.
Adding cards_prop_abid index to the cards_properties table, this can take some time...

In AbstractMySQLDriver.php line 125:

  An exception occurred while executing 'DROP INDEX idx_811f5cfa8b26c2e9 ON oc_cards_properties':

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


In PDOConnection.php line 109:

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


In PDOConnection.php line 107:

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


db:add-missing-indices

Is this the first time you’ve seen this error? (Y/N):
No, it was there in the 16.0.5 version as well, since it didn’t cause much trouble I waited for the next update.

I already tried to truncate the oc_cards and oc_cards_properties tables, this did not help. Any suggestions?

EDIT: In case it might be relevant, I’m trying to run “occ db:add-missing-indices”
because cards_prop_abid seems to be missing.

FIX: Incorrect index name 'card_value_index'

j-ed · November 12, 2019, 2:35pm

... Incorrect index name 'card_value_index'

Most likely the index already exists for the database, so I wouldn’t classify this as an essential error, as long as all other things are ok. See

It might help to see how a working table should look like (taken from my server):

> describe oc_cards_properties;
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| addressbookid | bigint(20)          | NO   | MUL | 0       |                |
| cardid        | bigint(20) unsigned | NO   | MUL | 0       |                |
| name          | varchar(64)         | YES  | MUL | NULL    |                |
| value         | varchar(255)        | YES  | MUL | NULL    |                |
| preferred     | int(11)             | NO   |     | 1       |                |
+---------------+---------------------+------+-----+---------+----------------+

The indexes should look like this:

> show indexes from oc_cards_properties;
+---------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name             | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nc_cards_properties |          0 | PRIMARY              |            1 | id            | A         |        8117 |     NULL | NULL   |      | BTREE      |         |               |
| nc_cards_properties |          1 | card_contactid_index |            1 | cardid        | A         |        2705 |     NULL | NULL   |      | BTREE      |         |               |
| nc_cards_properties |          1 | card_name_index      |            1 | name          | A         |          30 |     NULL | NULL   | YES  | BTREE      |         |               |
| nc_cards_properties |          1 | card_value_index     |            1 | value         | A         |        8117 |     NULL | NULL   | YES  | BTREE      |         |               |
| nc_cards_properties |          1 | cards_prop_abid      |            1 | addressbookid | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+----------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Aiko · November 12, 2019, 3:16pm

Thank you for your insight! I’ve been looking into this for weeks. After your example I understood what went wrong, so I tried to drop the index:

MariaDB [******]>  DROP INDEX idx_811f5cfa8b26c2e9 ON oc_cards_properties;

Which gave me this error:

ERROR 1280 (42000): Incorrect index name 'card_value_index'

So I dropped that one as well:

DROP INDEX card_value_index ON oc_cards_properties;

And there it went:

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

With me finally being able to drop the other one:

MariaDB [******]> DROP INDEX idx_811f5cfa8b26c2e9 ON oc_cards_properties;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

After which
occ db:add-missing-indices
ran with no trouble at all:

Check indices of the share table.
Check indices of the filecache table.
Check indices of the twofactor_providers table.
Check indices of the login_flow_v2 table.
Check indices of the whats_new table.
Check indices of the cards table.
Check indices of the cards_properties table.
Adding cards_prop_abid index to the cards_properties table, this can take some time...
cards_properties table updated successfully.

Thank you so much!
(also thanks for not hand feeding me a solution)

For future reference:
I also initiated a filescan afterwards with

occ files:scan --all

Aiko · March 16, 2020, 11:45am

Real glad I figured this out, with the most recent update I had to run:

DROP INDEX calendarobject_value_index ON oc_calendarobjects_props;

because of:

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’