[#9467] - Fix index lengths errors on new installation with PDO driver in strict mode
- Fixed in Code Base
- 18 Mar 2016
- Medium
- Build: staging
- # 9467
- Diff
- richard67:utf8mb4-forgotten-column-changes-1
- Success continuous-integration/travis-ci/pr The Travis CI build passed Details
- Success JTracker/HumanTestResults Human Test Results: 2 Successful 0 Failed. Details
User tests: Successful: Unsuccessful:
Pull Request for Issue #9465 .
Summary of Changes
It seems there were some index and column modifications forgotten for the utf8mb4 conversion, which fail on create table then when pdomysql (or mysql in general, any driver) is in strict mode but not failed without strict mode.
Testing Instructions
Test 1: Check if a new installation works without running into the errors described in issue #9465, chosing the PDO driver for mysql on installation and using the branch of this patch as installation source: https://github.com/richard67/joomla-cms/archive/utf8mb4-forgotten-column-changes-1.zip.
To enforce strict mode, hack the pdomysql.php as described in PR #9461 before starting the installation.
Test 2: Test also if the utf8(mb4) conversion still works on the Joomla! just installed with Test 1 before. To enforce the conversion to be performed again, enter following SQL statement in phpMyAdmin:
UPDATE
#__utf8_conversionSETconverted= 0;
(replace #__ by your db prefix).
Then go to "Extensions -> Manage -> Update".
You should see the conversion to be done reported as open problem.
Click the "Fix" button.
Result: The conversion is performed without errors, after this database is up to date without problems.
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Labels |
Added:
?
|
||
@wilsonge Can you check with Eli or trigger him to check if maybe for the one or other column the index is not needed or columns can be downsized to 191 so these changes are not necesary in this way for those columns? The auto-complete of the "at" mentioning does not find him and I am not sure about spelling of his nick name.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
| Labels |
Added:
?
|
||
Just ping him in skype in that group :) Btw this branch apparently has conflicts?
Ummm it shows fine here as well now. i don't understand...... just ignore me
@wilsonge If you used patchtester, maybe this shows conflicts when you try to apply my patch because it not knows about recent commits and so sees conflicts? I had the same when I wanted to test your PR, had to patch manually.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
i was looking in the github ui - so I really dunno - merge button was grey and said conflicts - then when i came back 2 hours later was green and happy.....
I have tested this item 
unsuccessfully on c21a981
Step 1, 2 and 3 ok, but when installing aditional languages i got:
"SQLSTATE[HY000]: General error: 1364 Field 'data' doesn't have a default valueHY000"
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
@andrepereiradasilva This issue is not related to this PR. Please install without installing additional languages. Additional languages installation has nothing to do with testing changes in joomla.sql!!! And mark your test result accordingly.
ok i understand now, sorry :)
I have tested this item 
successfully on c21a981
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
brianteeman
- | Labels | |||
| Category | ⇒ | SQL |
| Category | SQL | ⇒ |
Added 2nd test for conversion still working.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
@andrepereiradasilva Sorry, I had to add a 2nd test (Test 2) because this PR not changes joomla.sql but also the utf8(mb4) conversion scripts.
Can you set your test result to "not tested", and then test again and then mark result if ok or not?
Thanks in advance, and sorry for the inconvenience.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
Can you set your test result to "not tested", and then test again and then mark result if ok or not?
no need, second test done.
it took a while to do the conversion but tested successfully also.
Thanks in advance, and sorry for the inconvenience.
No problem you can buy me a 
one day 
Thanks for testing, Andre.
More testers please.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
| Milestone |
Added: |
||
I have tested this item successfully on c21a981
Successful installed after applying patch - errors before applying patch.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.
Have now - also with success :)
| Status | Pending | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-18 23:42:30 |
| Closed_By | ⇒ | wilsonge |
| Labels |
Removed:
?
|
||
Thanks for testing and merging.
@richard67 @wilsonge
How about this changes: https://github.com/joomla/joomla-cms/pull/9467/files#diff-135c6f58583408312a709459b19594c7R166
This changes will be apply only for freshly installed Joomla.
But how about those site that update their Joomla?
These #__banner_clients > metakey_prefix field will still have a value of 255.
On sites which update their Joomla! they will get it via the utf8mb4 conversions scripts (Step 2.2. in utf8mb4-conversion-02.sql). I made the conversion be forced to run again by a special schema update when updating a 3.5.0 to 3.5.1 or later, and on pre-3.5.0 it will run anyway, so if all runs without errors the changes should have been applied with any update method.
The reason why I did not put those statements into a regular update script was that the database check and fix would not properly handle e.g. dropping an index for later adding it back with the same name, and so it would always report open problems.
This kind of abusing the utf8mb4 conversion for structural change was a fast kind of an intermediate solution to make things work somehow, but it is not a good thing and can cause unsolvable database problem in case the utf8mb4 conversion is aborted somewhere in the middle because of e.g. max_execution_time of php being exceeded on large data.
I am developing a bit on how it could be made better as long as we support this "copy or unzip the files and then run the db fix" update method, which we have to support as long as not everybody has a 3.6.0 or later with the new Joomla! Update features. But I have not found the right solution yet, and I am not sure if I can in time for 3.6. I am also waiting on feedback from @wilsonge about some of my ideas.
Is a 3.5.0-blocker from my point of view.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9467.