error 1267 with joomla 3.5 update

V

vincent79

New Member
Hello,

I've got this error on my form page since i've done the joomla 3.5 update :
1267
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT *, e.name AS name, e.id AS id, e.published AS published, e.label AS label,e.plugin, e.params AS params, e.access AS access, e.ordering AS ordering FROM #__fabrik_elements AS e INNER JOIN #__extensions AS p ON p.element = e.plugin WHERE group_id IN (4) AND p.folder = "fabrik_element" AND e.published != -2 ORDER BY group_id, e.ordering
1267

Could you help me?

Best regards,

Vincent
 
Same problem:
Error: 1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT *, e.name AS name, e.id AS id, e.published AS published, e.label AS label,e.plugin, e.params AS params, e.access AS access, e.ordering AS ordering FROM #__fabrik_elements AS e INNER JOIN #__extensions AS p ON p.element = e.plugin WHERE group_id IN (12,14,15) AND p.folder = "fabrik_element" AND e.published != -2 ORDER BY group_id, e.ordering

Latest GitHub installed..
 
Same problem here:
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT *, e.name AS name, e.id AS id, e.published AS published, e.label AS label,e.plugin, e.params AS params, e.access AS access, e.ordering AS ordering FROM #__fabrik_elements AS e INNER JOIN #__extensions AS p ON p.element = e.plugin WHERE group_id IN (3) AND p.folder = "fabrik_element" AND e.published != -2 ORDER BY group_id, e.ordering
 
I assume this is when trying to display the element list?
I can't replicate.
What are your exact system settings?
Exact Fabrik version?
Can you post your "System informations"
 
hi
Please back up your db then try to run this query in php myadmin

Code: 
ALTER TABLE  #__fabrik_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

replace '#_' with your db prefix
 
Never without a complete backup;)

This issue seems to be related to your existing DB colloation and/or to your MySQL version. I couldn't replicate on my test site...

It's not a security update so for a live site I would do it in a sandbox first(and/or wait a bit).
 
rob said:
Code: 
ALTER TABLE  #__fabrik_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Click to expand...
shouldn't it be
Code: 
ALTER TABLE  #__extensions CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

as utf8_general_ci is the standard for mysql? May be the installation is on that utf8_unicode_ci collation.
 
This fixed the problem for me. Thanks for your advise Rob.
rob said:
hi
Please back up your db then try to run this query in php myadmin

Code: 
ALTER TABLE  #__fabrik_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

replace '#_' with your db prefix
Click to expand...
 
The problem is that Joomla3.5 is converting all Joomla core tables (only Joomla, not other extensions) to utf8mb4_unicode_ci.
Which may (or may not) break joins to these tables.

I couldn't replicate the error with Joomla tables set to utf8mb4_unicode_ci.

The reported error says "utf8_unicode_ci", so may be some MySQL versions don't know utf8mb4_unicode_ci and are setting utf8_unicode_ci instead (with #__extensions = utf8_unicode_ci and #__fabrik_elements = utf8_general_ci the error is replicatable.
 
You got it troester - and what a mess.
I feel bad for any Joomla 3rd party developer today. What were 'the powers that be' at Joomla thinking - allowing this update to be included as if it was just another routine mundane update? (I fell for the 'Update Now' click bait and have been struggling all day to make my site work again.)

I think everyone is forgetting that the collation/charset is set at many levels: server, database, table and column.

The error (I'm getting it too) is coming at the column level - because Joomla tables are now all modified so that both the tables and columns are using utf8mb4_unicode_ci collation, while none of the 3rd party tables are. The Fabrik github update does not address the collation/charset at the column level - nor do any of the suggestions made in this thread. There were a LOT of Joomla updates today from 3rd party developers - but I have yet to come across any which properly addressed this column-level issue - and that includes developers, besides Fabrik, who I have always found to be the most 'on top' of things, like NoNumber and Akeeba.

Someone needs to dissect the code used by Joomla and write some php code that repeats that process for all Fabrik lists/tables. I have always said that Fabrik should have some sort of 'maintenance' script for handling problems like this - where, when needed, it is part of a github update and gets run once, verified as successful, then deleted. It seems that would be less trouble than always writing extra code that dances around changes and adds more complexity to the code, because of worries of 'backward compatibility'.
 
ALTER TABLE #__fabrik_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Click to expand...
This is really setting table AND column collations (I didn't know before, too, but you can just try).

A GitHub update won't do any database modification, you have to do an update via Joomla updater after the new Fabrik version is out (or do it manually, if you have own joins in you custom code).
But as I've said:
As far as I can see on my site the Joomla collation change is no problem if your DB is accepting utf8mb4_unicode_ci.
 
For me, the Joomla update failed initially when it came across a table that had ROW_FORMAT set to COMPACT. (The same problem discussed here... http://forum.joomla.org/viewtopic.php?f=706&p=3378863)

In order for this change to utf8mb4_unicode_ci to work, there are 3 prerequisites, as explained here... http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

So I had to manually set all that first - then add a few lines to my.cnf file of mySql.

But the Joomla updater still doesn't fix the issue that causes the error raised in this thread.
Here is the final results shown once I finally got the Joomla 'Fix' to complete in the Manage - Database tab.
  • Database schema version (in #__schemas): 3.5.0-2016-03-01.
  • Update version (in #__extensions): 3.5.0.
  • Database driver: mysqli.
  • 93 database changes were checked successfully.
  • 145 database changes did not alter table structure and were skipped.
Note the last line.

The 'Illegal mix of collations' error will still exist on any tables where a query is being made on any joined tables with records that include a mix of non-compatible collation types.

As far as I was able to determine, after researching this all day yesterday, there is no easy way to change the column-level collation in every table of a database.

So I wrote some php code that loops through all tables and changes the collation at the column level (for all columns in all tables that have non-null settings for collation). I have no idea if that was the thing to do, or if I broke anything elsewhere, but at least now my Fabrik lists with joined data seems to work again.

This error, and the reason for it, was discussed in this thread at joomla.org last week - yet they released it anyhow...
https://issues.joomla.org/tracker/joomla-cms/9423

I'm very disappointed in the Joomla development team for not thinking this one through.

Addendum: I'm just wondering if this is only happening for users using the InnoDB storage engine - maybe explaining why you see no problem - are you using MyISAM?
 
Last edited:
Not sure what the Manage/Database is doing. This last line can be seen always.

I didn't read the thread in details (@wezetel mentioned it already) but I think their main argument is "don't join to J! tables...":(

As I've said the collation change seems to be no problem IF your server is accepting utf8bm4 AND your database (+ existing tables...) has somethink like utf8_general_ci

If J! can't change to utf8bm4_unicode_ci they "downgrade" to utf8_unicode_ci and this is conflicting with any (old J! standard) utf8_general_ci
 
I didn't read the thread in details (@wezetel mentioned it already) but I think their main argument is "don't join to J! tables...":(
Click to expand...

Well, the issue is only if you join on text fields. Which isn't a common thing to do, as obviously most joins are on integer PK's. But we did do it in one place, where we fetch all the element plugins used in a form, where we were joining the #__extensions row with our #_fabrik_elements table, and the only way to do it was to join on the 'element' field.

The good news is that I'm pretty sure we didn't really need to join the #__extensions table anyway. So I've committed a fix to github that simply removes that join:

https://github.com/Fabrik/fabrik/commit/16e20303d883e61cc8f5c23a1eb9c5ef0e2c02ba

So hopefully we don't have to worry about the collation changes.

If people could test that fix, I'd appreciate it, as I need to get a 3.4.3 build out ASAP. For this build, I've branched github just prior to the "modal merge" on March 9th which has introduced a fair number of instabilities which are going to take a little while to iron out. To that branch I've applied the necessary fixes for 3.5:

https://github.com/Fabrik/fabrik/tree/build343

So if anyone want to test that branch, that would be extra helpful.

-- hugh
 
OK, just to close this one out, looks like rewriting the query worked, no need to worry about collation issues.


Sent from my HTC One using Tapatalk
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 106 (members: 3, guests: 103)
    Back
    Top