How to keep your email logs with manual v1.12.0 Database Upgrade?

In version 1.12.0, we’ve applied changes to the database structure to provide a better experience for our users:

1. Support non-UTF characters like emojis (??????).
2. Faster email logs search.

These changes are automatically included for new installation.

For existing users who are upgrading from earlier versions, you can easily run this upgrade by navigating to the plugin’s Settings page and clicking the Upgrade button.

However, please be reminded that upgrading through the Settings page (as shown in the screenshot below) will delete all your existing email logs.

https://a.supportally.com/i/kSOkep

The reason for this is because the process of altering the database table with existing logs takes more time and resources than most web hosting providers allow, so it might cause issues for some users, especially for those with lots of email logs.

If you do not need the improvements mentioned above, you can ignore this Database Upgrade and keep using the plugin as is. You can also dismiss the upgrade banner in the Settings page and it will not show up anymore.

This guide is for users who need the improvements mentioned above and also wish to preserve their email logs. That’s why this database upgrade has to be performed manually.

Note: If you are having difficulties following this guide, please ask your web hosting support for help.

Note: Before continuing with this guide, please create a backup of your database.

Note: In this guide you will be editing our plugin’s DB table only. In our case the full DB table name is: wp_wpml_mails, but that might be different depending on the $table_prefix defined in your wp-config.php. By default it’s wp_, but please confirm and change wp_wpml_mails to {$table_prefix}_wpml_mails in all of the below SQL commands, if necessary. For example if your $table_prefix in wp-config.php is wpvs_, then you would use wpvs_wpml_mails instead of wp_wpml_mails in the SQL commands below.

1. Access your site’s database. Most web hosting providers allow you to access your database via phpMyAdmin.

2. Run this SQL command to add support for non-UTF characters like emojis.

ALTER TABLE wp_wpml_mails

    MODIFY host VARCHAR(200) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT "0",

MODIFY receiver VARCHAR(200) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT "0",

MODIFY subject VARCHAR(200) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT "0",

MODIFY message TEXT CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci',

MODIFY headers TEXT CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci',

MODIFY attachments VARCHAR(800) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT "0",

MODIFY error VARCHAR(400) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NULL DEFAULT '',

MODIFY plugin_version VARCHAR(200) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT "0";

(result example: https://a.supportally.com/i/ht87vM)

3. Confirm that the SQL command above was successful, by checking the DB table’s “Collation” was changed. The collation should be utf8mb4_general_ci for all the modified table columns used in the above SQL command.

(example: https://a.supportally.com/i/hwNvoF)

4. Run this next SQL command to improve the performance of email log searching by message. This command can take a lot of time to execute, especially if you have a lot of email logs. It’s advisable to delete and lower the number of email logs (to about 100-500 email logs) in order to speed up this process.

ALTER TABLE wp_wpml_mails ADD FULLTEXT INDEX idx_message (message);

(result example: https://a.supportally.com/i/MZYGl3)

5. Confirm that the SQL command above was successful. Notice that “FULLTEXT | message” was added in the Indexes.

(example: https://a.supportally.com/i/EGrYF3)

6. And the last step is to go to the WP Mail Logging settings page and dismiss the “Database Upgrade” banner, by clicking on the X icon in the top right corner. Our plugin will do the rest.

That’s it, you performed a manual DB upgrade and added support for non-UTF characters, like emojis and faster email log search, plus kept your email logs ??

• This reply was modified 1 year, 5 months ago by Gregor Capuder.

Hi Gregor,

Should the collation of the wp_wpml_mails table itself also be changed to utf8mb4_general_ci? (the collation of the table can be found under the Structure tab, scrolling to the Information section)

Or should this keep using <dfn title=”Unicode, case-insensitive”>utf8_general_ci</dfn>?

Hello Mezzomedia,

It is not required to change the collation of the table itself. Doing so will just make all new future columns of the table default to utf8mb4_general_ci. Hope this helps!

only takes 5 seconds on 9000 records for me. I’d say let it be automatic if there’s less than 1000 records and have a button for it to let admins risk it after a backup.