Pull Request for Issues #9509 and #9526 .

Replaced and extends PR #9510 .

Could also help with the cause for Issue #9511 .

Summary of Changes

This PR handles 4 things:
1. Add missing column and index changes as reported in issue #9509 to utf8mb4 conversion scripts so after update these columns and indexes are same as they are after new installation.
2. Change column user_id of table user_keys to length of 150 for new installation and update so it fits to column username of table users.
3. Change order of processing in the 2nd conversion script so adding back the indexes takes place after all conversions and default character set and collation changes.
4. Adapt old schema update sql scripts to limited index sizings of the utf8mb4 conversion, so if for some reason a database problem of an old sql script is to be fixed but the database is already converted to utf8mb4, it will not fail with the "1071 Specified key was too long; max key length is 767 bytes" error.

The changes mentioned with point 1 are:

• Changed column length of column name in table #__users to 400.
• Changed follwoing indexes (keys) to include column only with 1st 100 chars:
  • TABLE #__menu, KEY idx_path
  • TABLE #__users, KEY idx_name

Column path of table #__menu has already 1024 and was not enlarged in 3.5.0's joomla.sql, and so it is also not enlarged with this PR.

Note: This PR does not solve the problem that the utf8mb4 or utf8 combersion has to be executed again after an update e.g. from 3.5.0 to the 3.5.x in which this PR will be included. This can be manually done by the first SQL statement provided in testing instructions below.

Testing Instructions

@lal12 If you want to help with testing this and still have saved backup (file systema and database) of the system for which your pdate failed (issue #9526 ), you do not need to execute the folowing test but can try to update again (if the backup is from before update) and then, if it fails again in the same way, apply the patch of this PR e.g. with the patchtester, refresh the "Extensions -> Manage -> Database" page and then use the "Fix" button. This should then result in dastabase being ok and converted. If this works for you, mark the test result of this PR in the issue tracker as success please.

Prerequisite: Use a current staging or 3.5.0 on a database which supports utf8mb4 and is already converted to utf8mb4.

Step 1: In phpMyAdmin, execute the following two SQL statements, replacing #__ by your database prefix:

UPDATE #__utf8_conversion SET converted = 0;

ALTER TABLE #__menu DROP KEY idx_client_id_parent_id_alias_language;

The first statement simulates an undone utf8mb4 (or utf8) conversion, the second statement forces a schema error to be detected.

Step 2: Go to "Extensions -> Manage -> Database".

Result:

Warning: Database is not up to date!
2 Database Problems Found.

• Table #__menu does not have index 'idx_client_id_parent_id_alias_language'. (From file 2.5.0-2011-12-24.sql.)
• The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).

Step 3: Click the "Fix" button.

Result:

SQL Error "1071 Specified key was too long; max key length is 767 bytes", SQL=ALTER TABLE #__menu ADD UNIQUE idx_client_id_parent_id_alias_language ( client_id , parent_id , alias , language );

Step 4: Apply the patch for this PR, e.g. with patchtester.

Step 5: Reload the "Extensions -> Manage -> Database" page.

Step 6: Click the "Fix" button.

Result:

Database table structure is up to date.
Other Information

• Database schema version (in #__schemas): 3.5.1-2016-03-22.
• Update version (in #__extensions): 3.5.0.
• Database driver: mysqli.
• 94 database changes were checked successfully.
• 145 database changes did not alter table structure and were skipped.

Step 7: Check with phpMyadmin if the changes mentioned with points 1 and 2 in the Summary of Changes above have been applied.

Result: Changes have been applied.