Delicious Brains

Documentation

Data contains characters which are invalid in the target database

The table table-name contains characters which are invalid in the target database. See our documentation for more information.

WordPress 4.1 added new safeguards against inserting data into the database which is invalid for the column that it would be inserted into. If you’re migrating between two databases that have incompatible character-encodings or if you’re migrating data for the first time since upgrading to 4.1, it’s possible that some characters in your database may not be valid within the character set and encoding specified for the target column. WordPress will prevent that data from being inserted and WP Migrate DB Pro will show the above error.

Often, the invalid text that’s found in WordPress databases has been introduced by copy/pasting from a rich text editor like Microsoft Word which uses some special characters for formatting purposes. In this case it is safe to strip those characters from your WordPress database and the resulting sanitized data will be indistinguishable from the original. If you believe this to be the case, then you can add the following define to your wp-config.php file on the site that will be the destination for your migrated data (the current site in a pull, or the remote site in a push):

define( 'WPMDB_STRIP_INVALID_TEXT', true );

If your site incorporates languages that require extended character sets or otherwise utilizes features of a specific character encoding such as emoji which require multibyte character support, you should ensure that your destination database is fully compatible with the character encoding being utilized by your source database before allowing WP Migrate DB Pro to strip text from your migrated data.

Imports

If you are receiving the Data contains characters which are invalid in the target schema error when importing a file that was exported with WP Migrate DB Pro and your site is using a version of MySQL greater than 5.5.3, you should ensure that the Compatible with older versions of MySQL (pre 5.5) setting under Advanced Options is unchecked and run your export again. This option will convert tables and fields that use a utf8mb4 encodings to use utf8 encodings without altering the data in those tables or fields which can cause errors when importing if you have used multibyte characters such as emoji.

If you have an export that was created by WP Migrate DB Pro with the Compatible with older versions of MySQL (pre 5.5) setting that you cannot re-export you may be able to fix the export by opening the sql file in a text editor and replacing all instances of CHARSET=utf8 with CHARSET=utf8mb4 and COLLATE=utf8 with COLLATE=utf8mb4 before attempting the import again.