Pull Request for Issue #9526 .

Could also help with the cause for Issue #9511 .

Replaces PR #9590 , and as this it also replaces PRs #9510 and #9549 .

Summary of Changes

This PR handles 5 things:
1. 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.
2. Run the conversion only if the database schema does not have unfixed problems.
3. Adapt one old schema update sql to fix the 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 );".
4. Force a re-run of the conversion with a schema update SQL script when updating from a 3.5.0 to make sure the changes from commit 3540293 are applied (which would not be the case on a 3.5.0 obtained from updating a pre-3.5.0).
5. Add the forgotten "ALTER TABLE #__users ADD KEY idx_name (name(100));" to the conversion script, this has been forgotten with commit 3540293.

Points 1 and 2 will help to avoid to come into the situation which causes the SQL error mentioned with point 3.

Point 4 will work only if using Joomla! Update component to do the update from 3.5.0 to the version where this PR will be included in. After having updated from 3.5.0 with the "copy files and run db fixer" method, the conversion rerun has to be forced by SQL update as in the new SQL script 3.5.1-2016-03-29.sql added with this PR.

Point 5: @wilsonge please check and confirm.

Testing Instructions

Pre-requisites

The test have to be performed on a database supporting utf8mb4 and being already converted, i.e. at least of version 3.5.0 and having been installed or updated without error.

Test 1: Update from 3.5.0 to latest staging + this PR

Step 1: Update from a clean 3.5.0 (clean means unpatched, without this PR applied) to latest staging + this PR, using following custom URL for the Joomla! Update component: http://test5.richard-fath.de/list_test4.xml

Result: Update finishs with success.

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

Result: Success, no open database problems.

Step 3: Check with phpMyadmin if index idx_name of table #__users contains the name column with a length of 100.

Result: Index idx_name of table #__users is OK.

Test 2: DB schema fix

Either use latest staging + this PR applied (e.g. with patchtester), or use the updated Joomla! from Test 1.

Step 1: Simulate a corrupt schema, e.g. by not applied old schema update statement by executing following SQL statement in phpMyAdmin:

ALTER TABLE #__menu DROP KEY idx_client_id_parent_id_alias_language;

Step 2: Go to "Extensions -> Manage -> Database" or if still at that page, reload the page.

Result:

Warning: Database is not up to date!
1 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.)

Step 3: Click the "Fix" button.

Result: Success, no open database problems.

Step 4: Check with phpMyadmin if index idx_name of table #__users contains the name column with a length of 100.

Result: Index idx_name of table #__users is OK.

Test 3 (optional)

If you also want to replicate the SQL error fixed by this PR (beside others) on an unpatched 3.5.0 (or 3.5.1 RC), repeat Test 2 with an unpatched 3.5.0.

You will get SQL error "1071 Specified key was too long; max key length is 767 bytes" after having clicked the "Fix" button in Step 3.

The only way out will be to edit the 2.5.0-2011-12-24.sql and remove (or comment out) the SQL statement reported with the error, or modify as done with this PR, and then use again the "Fix" button.