Victor Font replied 2 years ago

While performing routine maintenance for one of our care plan subscriber sites, we discovered the server error log was enormous and growing exponentially. The error was a failed SQL Query that was simply querying two fields in wp_posts to check for spam. The query failed every time with a "fatal database error" triggered by a "Illegal mix of collations". We didn't even know what this meant until we dug deep.

Turns out that the age of the database and Internet Internationalization standards for data storage that were applied through routine software maintenance updates as they evolved caused the database to become incompatible with itself. Unbelievable but true! The age of the database had caught up with itself and was the root cause of the fatal error our client's site experienced.

The error you describe may not be related to Formidable at all, but the root cause may actually be traceable to the character sets and collations used by your database.

The standard character set and collation for WordPress since the 4.2 release in 2019 is utf8mb4 and utf8mb4_unicode_520_ci. Even the wp-config-sample.php delivered with every WordPress download, still shows the old utf8 standard. UTF-8 defaults to a 2-bit character collation.

Prior to 2019, database character sets and collations were a wild west show. Depending on the age of your database, you might have a variety of database collations and character sets applied to different tables, maybe even at the field level, that combine latin1_swedish, and 2-bit, 3-bit, or 4-bit Unicode. A mismatch of collations and character sets will cause the error you're experiencing.

The supplied wp-config is incorrect and there are pull requests in the repository with the corrected utf8mb4 value. The WordPress core team doesn't seem anxious to correct wp-config however. They've already included code that forces wpdb to use the utf8mb4 standard when new databases are created. The WordPress wpdb class that initiates the db tables on install, is hard coded to set the database character set and collation to the modern Unicode 4-bit standard.

There's a lot to unpack to understand how this all impacts WordPress, but from what you describe about not being able to save specific characters in a paragraph field sounds suspiciously like a database collation issue and not anything Formidable is doing. When entry data is stored in a database, every value is stored as text regardless of the field type chosen to display the content on the from end form. If there's a problem at the database error with character sets and collations, you would not know from a WordPress admin perspective. This is a DB admin issue and needs to be corrected at the database level, if this is the true root cause.

How do you diagnose? To find out if your database has a mixed bag of character sets and collations, open the database in PHPMyAdmin and view its structure. There are a few key references to check.

1. What is the default database character set and collation for the database?
2. What is the character set and collation for every table and every varchar() field?
3. What's the database engine for each table? The MySQL default storage engine changed from MyISAM to InnoDB in 2010. InnoDB is a transactional engine and the best choice for eCommerce. All db tables should use the same storage engine.

Because we had so many tables in the client's db that had old storage engines and a mix of all default character sets and collations known to the WordPress universe, the only fix that worked was a complete export / db rebuild / import of the data. Everything in that database has been reconfigured to a pristine modern WordPress structure and its performance has gone off the charts.

If your database only has a mix of Unicode values for character sets and collations, you may be able to correct the problems without rebuilding the database just through the PHPMyAdmin interface. After changing the tables manually, run the Analyze Tables process on the entire database to rebuild the indexes as a last step. I have to warn you though, if you have any latin1_swedish character sets and collations, you'll need to rebuild the database.

Victor Font replied 2 years ago

You'll know your database is up to modern standards and future proof in terms of internationalization if every table uses the InnoDB engine and is set to the utf8mb4 character set and the utf8mb4_unicode_520_ci collation. As an FYI, we inspected every database for every site we manage for our care plan subscribers. All of them, except the newest, need a one-time maintenance database modernization project to future proof their databases. We are developing the marketing materials to present this opportunity to our clients.

None of this is anyone's fault by intent. It is the result of international standards and database storage maturation. As maintenance updates are applied to sites and hosts upgrade databases, the newest ratified standards are implemented, but nothing fixes or updates tables built with deprecated standards retroactively. Fixing the past is a manual process.

Victor Font replied 2 years ago

There's one other thing. You said you're using MariaDB 10.4.27 The latest version is 10.11.2. I know the latest version supports the newest Unicode standards because we use it for our business sites, but your version may not and could still be using latin1_swedish as the default. Consult the MariaDB documentation for details.