TranslatePress Database issue (Collation)

  • Resolved contextquestion

    (@contextquestion)


    4 months, 2 weeks ago

    Hi ?? I’m using TranslatePress on my website to translate English to French. Lately it’s struggling to properly show search results in the translated version of my site, and as I looked into updating and optimizing the database (hoping this would fix my search problem), I’ve run into an issue. It throws a database error due to the collation ‘utf8mb4_bin’ not being suitable for the character set ‘latin1’. I’ve looked at the database table it states, and the collation shown there is the latin1 (latin1_swedish_ci), not utf8mb4_bin. My WP-config also states latin1 as the database character set. I don’t known where the utf8mb4_bin comes from, and thus how I can change it to fix the error. My TranslatePress is at Version 2.8.1, WooCommerce 9.1.2, WordPress 6.6 (since this morning, but the error showed well before that). Any and all help is welcome!

    Here’s the full error:

    WordPress database error: [COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1']
    DELETE     b FROM wp_trp_gettext_fr_fr AS a, wp_trp_gettext_fr_fr AS b WHERE -- IMPORTANT: Ensures one version remains a.ID < 0 AND b.ID < 0 AND a.ID < b.ID -- Check for all duplicates. Binary ensure case sensitive comparison AND (a.original COLLATE utf8mb4_bin = b.original OR a.original IS NULL AND b.original IS NULL) AND (a.translated COLLATE utf8mb4_bin = b.translated OR a.translated IS NULL AND b.translated IS NULL) AND (a.status = b.status OR a.status IS NULL AND b.status IS NULL)AND (a.domain = b.domain OR a.domain IS NULL AND b.domain IS NULL);

    WordPress database error: [COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1']
    DELETE b FROM wp_trp_gettext_fr_fr AS a, wp_trp_gettext_fr_fr AS b WHERE -- IMPORTANT: Ensures one version remains a.ID < 10000 AND b.ID < 10000 AND a.ID < b.ID -- Check for all duplicates. Binary ensure case sensitive comparison AND (a.original COLLATE utf8mb4_bin = b.original OR a.original IS NULL AND b.original IS NULL) AND (a.translated COLLATE utf8mb4_bin = b.translated OR a.translated IS NULL AND b.translated IS NULL) AND (a.status = b.status OR a.status IS NULL AND b.status IS NULL)AND (a.domain = b.domain OR a.domain IS NULL AND b.domain IS NULL);

    {"action":"trp_update_database","trp_updb_action":"remove_duplicate_gettext_rows","trp_updb_lang":"en_GB","trp_updb_batch":0,"trp_updb_nonce":"aa67654269","trp_update_completed":"no","progress_message":"
    SQL Error:<\/strong> COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'latin1'<\/p>
    Removing duplicated gettext strings for language fr_FR... done.<\/br><\/br>Removing duplicated gettext strings for language en_GB..."}
Viewing 3 replies - 1 through 3 (of 3 total)

  • Hello. Same problem here:

    DELETE b FROM wp_trp_gettext_it_it AS a, wp_trp_gettext_it_it AS b WHERE -- IMPORTANT: Ensures one version remains a.ID < 0 AND b.ID < 0 AND a.ID < b.ID -- Check for all duplicates. Binary ensure case sensitive comparison AND (a.original COLLATE utf8mb4_bin = b.original OR a.original IS NULL AND b.original IS NULL) AND (a.translated COLLATE utf8mb4_bin = b.translated OR a.translated IS NULL AND b.translated IS NULL) AND (a.status = b.status OR a.status IS NULL AND b.status IS NULL)AND (a.domain = b.domain OR a.domain IS NULL AND b.domain IS NULL);

    DELETE b FROM wp_trp_gettext_it_it AS a, wp_trp_gettext_it_it AS b WHERE -- IMPORTANT: Ensures one version remains a.ID < 10000 AND b.ID < 10000 AND a.ID < b.ID -- Check for all duplicates. Binary ensure case sensitive comparison AND (a.original COLLATE utf8mb4_bin = b.original OR a.original IS NULL AND b.original IS NULL) AND (a.translated COLLATE utf8mb4_bin = b.translated OR a.translated IS NULL AND b.translated IS NULL) AND (a.status = b.status OR a.status IS NULL AND b.status IS NULL)AND (a.domain = b.domain OR a.domain IS NULL AND b.domain IS NULL);

    Thread Starter contextquestion

    (@contextquestion)

    Hey @robertorefresh,
    I’ve changed all wp_trp* tables to the utf8mb4_general_ci collation, which fixed the issue for me. Maybe it’ll fix the issue for you too. (No guarantee.) Do make sure you backup your database before editing it (alternatively, you could also copy the translatepress database tables, adding -copy to the end of the copies and then change the collation of the original tables).
    I’m not sure if this is the proper way to change it, but I can optimize my databases again without error ??

    I’ve changed collation too: IT WORKS!! ??

    Thanks man, you help me a lot.

    Cheers

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.