Joomla ошибка 1267

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»

Facing the same problem (I have to admit, I didn’t have the latest github version installed). Currently have no time to dig deeper, but I have found this issue discussion for joomla: https://github.com/joomla/joomla-cms/issues/9423

hi
Please back up your db then try to run this query in php myadmin

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

replace ‘#_’ with your db prefix

I take it it’s not safe to upgrade yet?

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).

At this stage I would recommend testing it with a testdev installation.

shouldn’t it be

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.

Nice to hear. Have you other tables with collation utf8_general_ci in your joomla database?

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’.

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.

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

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

is it safe to upgrade to 3.5 now? i see i’m being prompted to upgrade my fabrik install now too.