When I am in the admin panel, I get this error when clicking on  CJforum | Topics ( /administrator/index.php?option=com_cjforum&view=topics )

 

An error has occurred.

500 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '=' Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

 

We are using  utf8mb4_general_ci collation for almost all of Joomla tables. ( This is recommended by many vendors...)

but after CJforum update, those tables get changed  back to utf8mb4_unicode_ci - and thus we get this conflict.

Is there a way to solve this, so I don't have to mess around after every update??

Do you run your entire Joomla installation as utf8mb4_unicode_ci ??  We don't want to try to change collation on our whole site ( we have some fair-sized tables, our db is currently half a gigabyte... )

I am not getting collation mismatch error messages from any other components. Some ( like community builder) still use a mix of utf8mb4_general_ci and utf8_general_ci... 

Like it on Facebook, Tweet it or share this topic on other bookmarking websites.
  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    I am not sure where you got this information but the default collation used by Joomla is utf8mb4_unicode_ci.

    https://github.com/joomla/joomla-cms/blob/staging/installation/sql/mysql/joomla.sql 

    btw.. the future updates won't change the collation of tables.


    Follow me on twitter: https://twitter.com/corejoomla
    If you use our extensions, please post a rating and a review at the Joomla! Extensions Directory.
    SurveysCjForum | Polls | Answers | Quizzes | Quotes | GPS Tools | Sociable


    Thank you said by: MICHAEL WALKER

  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    Ah, I understand now that it was self-inflicted!

    I had used utf8mb4_general_ci as the offered default 'tables upgrade' from AdminTools a long time ago - but at the time I didn't realize I could have used the same tool to change to unicode_ci sorting instead...

    Have it sorted now! 😀


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    OK, now I have a big problem.

     

    I set all the tables to  utf8mb4_unicode_ci

    and it seemed to be fine... BUT !!!

     ... now Im getting an error posting new forum posts, or deleting old ones

     

    Error

    Save failed with the following error: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

    the posts are going in but the page is stuck with the error message on top ( so it looks like they didnt post)

    I see this in the PHP error log ( maybe unrelated?)

     

     

    [Wed Mar 31 17:22:49.099845 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/lib/api.php on line 128

    [Wed Mar 31 17:22:49.099944 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/lib/api.php on line 128

    [Wed Mar 31 17:22:49.099982 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 90

    [Wed Mar 31 17:22:49.100012 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 93

    [Wed Mar 31 17:22:49.100038 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 97

    [Wed Mar 31 17:22:49.103374 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/lib/api.php on line 128

    [Wed Mar 31 17:22:49.103430 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/lib/api.php on line 128

    [Wed Mar 31 17:22:49.103466 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 90

    [Wed Mar 31 17:22:49.103494 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 93

    [Wed Mar 31 17:22:49.103520 2021] [php7:notice] [pid 9609] [client 13.66.139.72:60608] PHP Notice:  Trying to access array offset on value of type bool in /var/www/html/SiteName/html/components/com_cjforum/layouts/default/topic/reply.php on line 97

     

     


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    I have checked all of the "cjforum" tables, and they all have the utf8mb4_unicode_ci collation, and any of their fields that have collations also have the utf8mb4_unicode_ci collation.

    So is it hitting some standard joomla table during a new post, or trash operation?

     I do not get this error during a forum post reply, a post edit/ update, or a 'permanently delete"...

     

    This is a real serious glitch for us ( my bad for not testing it thoroughly on the dev server...)

     


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    More info... I can unpublish, trash, or permanently delete a REPLY without triggering the error....

    Editing a TOPIC does not trigger the error...

    Unpublishing or Publishing a TOPIC gives the collation error.

    Trashing a TOPIC gives the error.  

    Permanently deleting a TOPIC does NOT give the error...

     

     

     


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    any assistance is gratefully appreciated

     

    Another hint: I can create a new Topic in the backend without provoking the error message, but I do get the error if I TRASH it...

    Error

    Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
    And the same error, if I publish it again... or trash it . 'Permanently delete' succeeds with no error message...
     

  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    And it LOOKS, like this error might be prevented by modifying the query in question with a SPECIFIC collation :

    ( just would need to figure out where the query is, do do a workaround?)

    ...as an error "Illegal mix of collations (utf8mb4_general_ci, COERCIBLE) and (utf8mb4_unicode_ci, COERCIBLE) for operation '='". This is because the value of different COLLATE COLLATE to be judged to be the cause of the query field. This requires manually specify COLLATE at query time. E.g:

    WHERE 'A' COLLATE utf8mb4_unicode_ci = 'A' COLLATE utf8mb4_unicode_ci

    or:

    SELECT * FROM table ORDER BY key COLLATE utf8mb4_unicode_ci;

    Reference website:

    https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql

     


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    Since all your tables are changed to generali_ci collation, edit cjforum tables and change collation to same. Sometimes individual columns collation might be changed, check and change them too using phpMyAdmin.


    Follow me on twitter: https://twitter.com/corejoomla
    If you use our extensions, please post a rating and a review at the Joomla! Extensions Directory.
    SurveysCjForum | Polls | Answers | Quizzes | Quotes | GPS Tools | Sociable


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    The thing is, the whole Joomla database AND all the cjforum tables are now utf8mb4_unicode_ci

    and, I have checked every cjforum column collation: they are set to utf8mb4_unicode_ci

    And the cjforum backend is fine now, except as noted above ( https://corejoomla.com/forum/support/cjforum/17802-cjforum-collation-clash-backend.html#p34187 )

    But users in the front end still get the error posting a new topic...


  • Re: [SOLVED] CJforum collation clash (backend) - after update

    by » 3 months ago


    Also, I looked in topic.php and it looks like the only 'non-cjforum' tables being interacted with there are #__users and  #categories ...  and both of those are proper unicode ci ( including their column collations). 

    Maybe I am missing something from an include or plugin? We are using CB_users, not the joomla or cjforum user...

     

    	Line 70: 				$query->from('#__cjforum_topics AS a');
    	Line 75: 				    ->join('LEFT', '#__cjforum_users AS ua ON ua.id = a.created_by');
    	Line 80: 					->join('LEFT', '#__categories AS c on c.id = a.catid');
    	Line 85: 					->join('LEFT', '#__users AS u on u.id = a.created_by');
    	Line 107: 					$query->select('(select count(*) from #__cjforum_subscriptions where item_id = '.$pk.' and item_type = 1 and user_id = '.$user->id.') as subscribed');
    	Line 108: 					$query->select('(select count(*) from #__cjforum_subscriptions where item_id = a.catid and item_type = 2 and user_id = '.$user->id.') as subscribed_category');
    	Line 114: 					->join('LEFT', '#__categories as parent ON parent.id = c.parent_id')
    	Line 129: 				$subquery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ';
    	Line 129: 				$subquery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ';
    	Line 263: 				    ->from('#__cjforum_moderators AS a')
    	Line 285: 						->from('#__cjforum_favorites')
    	Line 296: 					->from('#__cjforum_attachments AS a')
    	Line 354: 				->from('#__cjforum_user_ratings')
    	Line 363: 				if(!$db->updateObject('#__cjforum_user_ratings', $rating, array('item_id', 'user_id', 'item_type')))
    	Line 371: 				if(!$db->insertObject('#__cjforum_user_ratings', $rating))
    	Line 379: 						#__cjforum_topics
    	Line 386: 									#__cjforum_user_ratings 
    	Line 397: 									#__cjforum_user_ratings 
    	Line 435: 				->from('#__cjforum_user_ratings')
    	Line 476: 				->from('#__cjforum_thankyou AS a')
    	Line 477: 				->join('left', '#__users AS u1 ON u1.id = a.created_by')
    	Line 478: 				->join('left', '#__users AS u2 ON u2.id = a.assigned_to')
    	Line 518: 				->from('#__cjforum_attachments')
    	Line 555: 				->from('#__cjforum_replies')
    	Line 568: 				->from('#__cjforum_replies')
    	Line 620: 	        ->from('#__cjforum_approval')
    	Line 631:     	            ->update('#__cjforum_topics')
    	Line 639:     	            ->update('#__cjforum_approval')

You do not have permissions to reply to this topic.
Powered by CjForum