[SOLVED] Illegal mix of collations with J3.5, Unable to load any FC Administration screen

yopyop001
Topic Author
Offline
Moderator

9 years 10 months ago - 9 years 10 months ago #60688 by yopyop001

[SOLVED] Illegal mix of collations with J3.5, Unable to load any FC Administration screen was created by yopyop001

Hi,

I have updated the Joomla version to the test version 3.5 RC4, PHP to PHP 7.0.2, FC to 3.0.14 beta1

When i try to access FC Admin Panel, i have this error :

Code:

1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '<>' SQL=SELECT COUNT(*) FROM #__content as i JOIN #__flexicontent_items_ext as ie ON i.id=ie.item_id WHERE i.language<>ie.language LIMIT 1

It seems that #__content table and fc table don't use the same charset. I don't know if it's because of my configuration.
(i have the same message with PHP 5.6)

No problem in front-end

Regards

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

yopyop001
Topic Author
Offline
Moderator

9 years 10 months ago - 9 years 10 months ago #60689 by yopyop001

Replied by yopyop001 on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

After downgrading to Joomla 3.4.8 , no more problem
In Mysql : It seems that the tables in Joomla 3.4.8 are all 'utf8_general_ci' and in Joomla 3.5 the tables are 'utf8_unicode_ci'

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 10 months ago - 9 years 10 months ago #60695 by ggppdk

Replied by ggppdk on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Hi

it is not PHP7 it is mySQL version (maybe ALL mySQL)
i should have fixed this sooner,

i was lately using MariaDB and it does convert these collations silently
- thus it gives no error

this fix should be last thing to add in v3.0.14
- at least v3.0.13 should not have this problem in frontend

will make v3.0.14 RC with fix during weekend

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star review. Thanks!

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 10 months ago - 9 years 10 months ago #60698 by ggppdk

Replied by ggppdk on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Which is your MySQL version number ?

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 10 months ago - 9 years 10 months ago #60701 by ggppdk

Replied by ggppdk on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Hello

please test with:
v3.0.14-rc
github.com/FLEXIcontent/flexicontent-cck/releases

also please edit / save an item/type/field/template layout in backend
and test frontend item / category views

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

Natwork
Offline
Senior Member

9 years 10 months ago - 9 years 10 months ago #60730 by Natwork

Replied by Natwork on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

HI,

I have the same issue with j3.5 & FC3.0.14RC2; i'm using php 5.6.17 & MySQL 5.5.46...
I cleared backend & frontend cache, and repaired database with joomla integrated tool, with no success.
Everything is OK in frontend.

Code:

1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT COUNT(*) AS total1, ( SELECT COUNT(*) FROM `#__flexicontent_items_ext` AS ie JOIN `#__content` AS i ON ie.item_id=i.id WHERE 1 ) AS total2 FROM `#__content` AS i JOIN `#__flexicontent_items_tmp` AS ca ON i.id=ca.id JOIN `#__flexicontent_items_ext` AS ie ON ie.item_id=i.id WHERE 1 AND i.`title`=ca.`title` AND i.`state`=ca.`state` AND i.`catid`=ca.`catid` AND i.`created`=ca.`created` AND i.`created_by`=ca.`created_by` AND i.`modified`=ca.`modified` AND i.`modified_by`=ca.`modified_by` AND i.`publish_up`=ca.`publish_up` AND i.`publish_down`=ca.`publish_down` AND i.`version`=ca.`version` AND i.`ordering`=ca.`ordering` AND i.`access`=ca.`access` AND i.`featured`=ca.`featured` AND i.`language`=ca.`language` AND ie.`type_id`=ca.`type_id` AND ie.`lang_parent_id`=ca.`lang_parent_id`

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

yopyop001
Topic Author
Offline
Moderator

9 years 10 months ago - 9 years 10 months ago #60731 by yopyop001

Replied by yopyop001 on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Hi,

Sorry for the late answer.

The mysql version is 5.1.31
I can't upgrade now, but i will try soon.

Regards

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

kath
Offline
Platinum Member

9 years 10 months ago - 9 years 10 months ago #60734 by kath

Replied by kath on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

hi there,

same Problem here.
Joomla 3.5.0
flexicontent-cck-3.0.14-rc2
sql 5.1.73
php 5.3.29 (this is very low i know, but it's only a stage server..)

Error was above error before FC-upgrade.
Error is now:

Code:

An error has occurred.
    1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=SELECT COUNT(*) AS total1, ( SELECT COUNT(*) FROM `#__flexicontent_items_ext` AS ie JOIN `#__content` AS i ON ie.item_id=i.id WHERE 1 ) AS total2 FROM `#__content` AS i JOIN `#__flexicontent_items_tmp` AS ca ON i.id=ca.id JOIN `#__flexicontent_items_ext` AS ie ON ie.item_id=i.id WHERE 1 AND i.`title`=ca.`title` AND i.`state`=ca.`state` AND i.`catid`=ca.`catid` AND i.`created`=ca.`created` AND i.`created_by`=ca.`created_by` AND i.`modified`=ca.`modified` AND i.`modified_by`=ca.`modified_by` AND i.`publish_up`=ca.`publish_up` AND i.`publish_down`=ca.`publish_down` AND i.`version`=ca.`version` AND i.`ordering`=ca.`ordering` AND i.`access`=ca.`access` AND i.`featured`=ca.`featured` AND i.`language`=ca.`language` AND ie.`type_id`=ca.`type_id` AND ie.`lang_parent_id`=ca.`lang_parent_id`

EDIT:
- i cleared browser cache and cookies
- i cleared joomla caches (fe, be)
- i logged out, logged in

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 10 months ago - 9 years 10 months ago #60736 by ggppdk

Replied by ggppdk on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Hello

so v3.0.14-rc2 does not include ALL cases

i will test with older MySQL version my

my 2 servers have later MySQL DB or Maria DB which makes an implicit (automatic) conversion for these character sets

- fixed version will be available tomorrow

Last edit: 9 years 10 months ago by ggppdk.

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

9 years 10 months ago #60737 by ggppdk

Replied by ggppdk on topic Illegal mix of collations with J3.5, Unable to load any FC Administration screen

Hello

problem is not PHP version
but it is SQL version

i looked i don't have any server old MySQL version
- i will install locally

@yopyop001
@kath

you can make a fresh (new) Joomla 3.5 installation on your server (in a subfolder or in a new server account),
and send me Access to confirm fixes

Please Log in or Create an account to join the conversation.