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


    PS: my joomla  configuration is 

    Database Type mysql

    Database Version 5.7.33

    Database Collation utf8mb4_unicode_ci

    Database Connection Collation utf8mb4_general_ci

    PHP Version 7.4.16

     


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

    by » 3 months ago


    I have a feeling this is at least partly due to Mysql Database Version 5.7.33, somehow.

    I did a couple of AdminTool repair/optimizes to our DEV server, and now the issue is gone there.

    But DEV server is running Mysql 5.7.30, somehow...  All I can see is that there is something messed up in the 0000-00-00 date defaults now ( for numerous tables).. as I get an error message if I try to manually redo collation on any tables that have this zero DATETIME defaults. It seems that AdminTools just silently ignores those.

    Its really weird though as PHPMyAdmin shows all the tables looking just fine, and all with the same collations.

    What is your default db version, and do you do anything with the SQL_MODE settings??

    ( I have tried a bunch of things but I still get the errors...)

     

    see https://stackoverflow.com/questions/43589048/mysql-need-to-allow-0000-00-00-000000-dates

     


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

    by » 3 months ago


    and.... I did a search for all tables with column collations of the offending mismatch, and this is all that I found:

    #___docman_nodes type utf8mb4_general_ci
    #___finder_terms term utf8mb4_general_ci
    #___finder_terms stem utf8mb4_general_ci
    #___finder_terms soundex utf8mb4_general_ci
    #___finder_terms language utf8mb4_general_ci

    I didn't find that the cjForum 'create new post' code is interacting with either of these tables... so why the error? IT'S A MYSTERY... gonna have to bite the bullet and roll back to yesterday's previous site backup!


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

    by » 3 months ago


    If the issue with zero dates which is causing  the issue, then its your mysql configuration that is messed up. Joomla uses zero dates and if you don't allow them, it will fail. Add below configuration in your /etc/mysql/mysql.conf.d/mysqld.conf or ask your host for right file.

    sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    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 » one month ago


    Thank you! changing the ZERO config as above , makes it succeed ( no more backend error) after I do the collation change to unicode.

    At least, it works on our DEV server running 5.7.30  ...

     

    I'm gonna rebuild that DEV server with the SAME mysql version as production (5.7.33) and confirm that it still works!


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

    by » one month ago


    disaster... DEV server built to 7.33 , and no problems. So, change collation and SQL mode on LIVE.

    But same process on LIVE server: THE SAME error posting again.

     

    BUT NO TABLES are utf8mb4_general_ci now!  W.T.H. ??


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

    by » one month ago


    REALLY would like to know what is triggering this!!

    I applied the SQL_MODE ( and verified it was applied) and then changed all the tables to utf8mb4_unicode_ci

    This fixed the back-end problem. BUT then it kills new posts, just as described earlier!

     

    MIRACULOUSLY I was able to repair this by changing all tables back to utf8mb4_general_ci


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

    by » one month ago


    Rolling back to utf8mb4_general_ci has at least temporarily restored forum posting on our live server!

    But trying to get to the bottom of this on our DEV server... (with all tables set to utf8mb4_unicode_ci)

     

    I make a new post  - then I get the error:

     

    Error

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

     

    Also, I get this other ( previously un-noticed) error in the back-end,  if I TRASH a few items:

     

    Error

    Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
     
     
    The REALLY STRANGE THING is that there are not any "utf8mb4_general_ci" tables left in this database. As per your advice, I converted them all to utf8mb4_unicode_ci... ( and conversion appeared  to be successful, with the recommended db_mode settings)
     
    There are numerous other extensions installed in this joomla - but none of them complain about collation mixes! Only CJforum...
     
    Any advice would be greatly appreciated. What is going on when making a new post, that can be pulling a wrong  utf8mb4_general_ci,IMPLICIT collation mix "out of thin air"?
     
     

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

    by » one month ago


    And.. a small update: I notice now that on our LIVE server, with all table collations set back to "utf8mb4_general_ci", suddenly the CJforum BACKEND is working fine, too. I assume this has something to do with the recent CJforum update?

    Previously, (and the whole reason I even noticed the collation settings) - when I used the "Topic Manager : Topics" (/administrator/index.php?option=com_cjforum&view=topics) in the backend, it gave me the collation mix error. And NOW it doesn't...

     

    running  CjForum 3.4.3

     

    Latest Version: 3.4.3
    Latest Version Released On: 2021-03-25
    CjLib Version 3.0.13

     


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

    by » one month ago


    There is no such change done relating to collations in the recent past. CjForum uses Joomla APIs for storing the topics. That means it uses some of the Joomla core tables such as xxx_assets, xxx_categories, xxx_content_types, xxx_fields*, xxx_users. Besides this Joomla fires several queries inside. If you are able to post a Joomla article, you should be able to post the topic as well provided cjforum tables are good.

    Do you see any stack trace in your apache log files which may give us some idea where exactly the error is. Also did you compare your dev server and live server for any differences?


    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


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