MySQL refuses to UNION with Messenger

At least one of our comment tables (bx_*_cmts tables) has a different character set / collation than the others, so MySQL refuses to UNION them. It appears related to Messenger under cmts-administration.

  • View
    49
  • More
Replies (1)

    • Given that this is happening in UNA CMS involving the Messenger module and comment tables, probably the simplest and safest fix is to:

      • Identify which bx_*_cmts table(s) have the “wrong” collation.
      • Use ALTER TABLE to set them to the same collations as the others.
      • Clear any cache in the UNA Studio / Dashboard after making database schema changes.

      ⚠️ Before making any modifications, always create a full database backup.

      mysqldump -u USERNAME -pPASSWORD DATABASE_NAME > backup.sql

      Here are a few ways to fix or work around this issue:

      Align Collations / Character Sets

      Login into MySQL

      mysql -u USERNAME -pPASSWORD DATABASE_NAME 

      Check the character set and collation of all comment tables (bx_*_cmts):

      SHOW FULL COLUMNS FROM bx_table_name;

      If they differ, alter the tables so they use the same character set and collation:

      ALTER TABLE bx_table_name MODIFY column_name VARCHAR(…) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

      Make sure the main text columns used in the UNION operation match exactly.

      Login or Join to comment.