Data base error and mix of storage engine

No problem, I can help you with that!Collations

First, let’s address the collation issue. Collation determines how string comparison is handled in your database. Mixing different collations can cause errors, as you’ve encountered.Steps to Change Collation

1. Identify the Problematic Collations:
   • You have utf8mb3_general_ci, utf8mb4_unicode_520_ci, utf8mb4_unicode_ci, and latin1_swedish_ci.
2. Choose a Consistent Collation:
   • Since you have utf8mb4_unicode_520_ci and utf8mb4_unicode_ci, it’s best to choose one of these for consistency.
   • utf8mb4_unicode_ci is more widely used and compatible.
3. Change the Collations:
   • Change utf8mb3_general_ci and latin1_swedish_ci to utf8mb4_unicode_ci.
   • You can use the following SQL commands to change the collation of your tables and columns.

SQL Commands to Change Collation

First, for your tables:

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Next, for specific columns:

ALTER TABLE your_table_name CHANGE column_name column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Storage Engines

You have a mix of MyISAM and InnoDB storage engines. InnoDB is generally preferred for its support for transactions, foreign keys, and better crash recovery. It’s a good practice to standardize on InnoDB unless you have a specific reason to use MyISAM.Steps to Change Storage Engine

1. Identify Tables Using MyISAM:
   • You can run the following query to list tables using MyISAM:

SELECT table_name
FROM information_schema.tables
WHERE engine = ‘MyISAM’;

Change Storage Engine to InnoDB:

• You can change the storage engine for each table using the following command:
• ALTER TABLE your_table_name ENGINE=InnoDB;

Summary

1. Change Collation to utf8mb4_unicode_ci:

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE your_table_name CHANGE column_name column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Change Storage Engine to InnoDB:

ALTER TABLE your_table_name ENGINE=InnoDB;

By standardizing the collation and storage engine, you should resolve the collation mix error and ensure better performance and reliability for your database. If you have any specific tables or columns in question, I can help you craft the exact commands.

Thank you so much, IO do appreciate very much your help!

Hello,

I don’t understand, but even that I have changed all tables to utf8mb4_unicode_ci as you have indicated and now they all look correctcly, I still have the same issue, this is the error I find:

[31-Jul-2024 16:19:04 UTC] WordPress database error Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation ‘like’ for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts LEFT JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT(‘post_’, wp_posts.post_type)
WHERE 1=1 AND (((wp_posts.post_title LIKE ‘%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? https://www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%’) OR (wp_posts.post_excerpt LIKE ‘%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? https://www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%’) OR (wp_posts.post_content LIKE ‘%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? https://www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%’))) AND (wp_posts.post_password = ”) AND ((wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’)) OR (wp_posts.post_type = ‘page’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’)) OR (wp_posts.post_type = ‘attachment’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’)) OR (wp_posts.post_type = ‘project’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’)) OR (wp_posts.post_type = ‘profile’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’)) OR (wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘tribe-ea-success’ OR wp_posts.post_status = ‘tribe-ea-failed’ OR wp_posts.post_status = ‘tribe-ea-schedule’ OR wp_posts.post_status = ‘tribe-ea-pending’ OR wp_posts.post_status = ‘tribe-ea-draft’))) AND ( ( ( wpml_translations.language_code = ‘ca’ OR 0 ) AND wp_posts.post_type IN (‘post’,’page’,’attachment’,’wp_block’,’wp_template’,’wp_template_part’,’wp_navigation’,’project’,’profile’,’tribe_venue’,’tribe_organizer’,’tribe_events’ ) ) OR wp_posts.post_type NOT IN (‘post’,’page’,’attachment’,’wp_block’,’wp_template’,’wp_template_part’,’wp_navigation’,’project’,’profile’,’tribe_venue’,’tribe_organizer’,’tribe_events’ ) )

                 ORDER BY (CASE WHEN wp_posts.post_title LIKE '%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%' THEN 1 WHEN wp_posts.post_title LIKE '%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%' THEN 2 WHEN wp_posts.post_excerpt LIKE '%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%' THEN 4 WHEN wp_posts.post_content LIKE '%OGEA-103合格対策 ?? OGEA-103対応資料 ?? OGEA-103実際試験 ?? ? www.goshiken.com ??で使える無料オンライン版? OGEA-103 ?? の試験問題OGEA-103試験概要%' THEN 5 ELSE 6 END), wp_posts.post_date DESC
                 LIMIT 0, 10 made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, W3TC\DbCache_WpdbNew->query, W3TC\DbCache_WpdbInjection_QueryCaching->query, W3TC\_CallUnderlying->query, W3TC\DbCache_WpdbNew->query, W3TC\DbCache_WpdbInjection->query, W3TC\DbCache_WpdbNew->default_query

There is a message regarding cache, but I have installed it yesterday after changing the database.

I will appreciate if you have any ideas.

Thank you

Eva