Summary of Changes

The database schema manager ("Extensions -> Manage -> Database" in backend) ignores for MySQL (or derivates, e.g. MariaDB) valid SQL statements to add or drop indexes if they use "KEY" instead of "INDEX" and does not recognize the valid optional keywords "KEY" or "INDEX" after "UNIQUE".

All this is valid syntax for adding a key:

1. "ALTER TABLE #__table ADD INDEX keyname ..."
2. "ALTER TABLE #__table ADD KEY keyname ..."
3. "ALTER TABLE #__table ADD UNIQUE keyname ..."
4. "ALTER TABLE #__table ADD UNIQUE INDEX keyname ..."
5. "ALTER TABLE #__table ADD UNIQUE KEY keyname ..."

but only 1. and 3. are recognized by the schema manager's schema item for mysql.

For dropping a key, "DROP INDEX" and "DROP KEY" are valid, but only the first one is recognized by the schema manager.

Unfortunately these statements with "KEY" already were used in old updates SQL files, it seems just nobody noticed up to yet that when updating a 2.5.something or 3.1.0 to e.g. a 3.4.8 some missing indexes not were reported as database problems and so recreated. I am sure this has lead several times to problems. I've changed these 2 old files in a recent PR but this PR here solves the problem also in future.

The danger is big that in future people use "KEY" or "UNIQUE KEY", because 1. it is valid syntax and 2. also used in joomla.sql, which might be the template for people writing update sqls.

This PR extends the schema item object for mysql by handling all valid syntax alternatives mentioned above.

Testing Instructions

On a latest staging, add following statements

ALTER TABLE #__redirect_links ADD INDEX idx_test_1 (old_url(100));
ALTER TABLE #__redirect_links ADD KEY idx_test_2 (old_url(100));
ALTER TABLE #__redirect_links ADD UNIQUE idx_test_3 (old_url(100));
ALTER TABLE #__redirect_links ADD UNIQUE INDEX idx_test_4 (old_url(100));
ALTER TABLE #__redirect_links ADD UNIQUE KEY idx_test_5 (old_url(100));
ALTER TABLE #__tags DROP INDEX idx_alias;
ALTER TABLE #__ucm_content DROP KEY idx_alias;

to the sql file with youngest time stamp in the mysql update files folder, this should be on latest staging

administrator/components/com_admin/sql/updates/mysql/3.5.0-2016-03-01.sql

Then go in backend to "Extensions -> Manage -> Database" and see following database problems shown:

Table 'j3ux0_redirect_links' does not have index 'idx_test_1'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'idx_test_3'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'INDEX'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'KEY'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_tags' should not have index 'idx_alias'. (From file 3.5.0-2016-03-01.sql.)

As you can see, the index names are not correctly recognized when optional keyword after "UNIQUE", and the statements with "KEY" alone are ignored at all.

Do NOT fix the database problems.

Now replace following file by the one from this PR:

libraries/cms/schema/changeitem/mysql.php

and refresh the page with the database problem display.

You should see the problems correctly reported now:

Table 'j3ux0_redirect_links' does not have index 'idx_test_1'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'idx_test_2'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'idx_test_3'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'idx_test_4'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_redirect_links' does not have index 'idx_test_5'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_tags' should not have index 'idx_alias'. (From file 3.5.0-2016-03-01.sql.)
Table 'j3ux0_ucm_content' should not have index 'idx_alias'. (From file 3.5.0-2016-03-01.sql.)

Do NOT fix the database problems.

Change back the modified update sql to its original state and refresh the page.

Result: No database problems, database is up to date.