Error creating Archive Table

Hi @thecrazy1, Matomo for WordPress should use the same charset/collation that WordPress is using. Do you set the DB_CHARSET and DB_COLLATE constants in your wp-config.php? If so, can you tell me what they’re set to?

Thank you for your response.

Yes, in wp-config.php, I have set the following due to compatibility needs with my WordPress theme and plugins, which depend on latin1 for serialized data compatibility:

define('DB_CHARSET', 'latin1');
define('DB_COLLATE', 'latin1_swedish_ci');
The issue I’m encountering is that Matomo’s archive tables seem to require utf8mb4 collation. When the WordPress configuration specifies latin1, I get the following error on Matomo’s monthly archive cron jobs:

WP DB Error: [1253] COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'
SQL: CREATE TABLE wp_matomo_archive_numeric_2024_11 ( … ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=utf8mb4_general_ci
Since my WordPress setup must remain in latin1 to preserve serialized data, is there an option to configure Matomo to use utf8mb4 independently of the WordPress DB_CHARSET setting? This would allow me to avoid potential conflicts without altering the database settings for the entire WordPress installation.

Alternatively, if there are any recommended workarounds to maintain Matomo’s functionality under the current latin1 setting, I’d appreciate any guidance you can offer.

Thank you again for your assistance.

@thecrazy1

The issue I’m encountering is that Matomo’s archive tables seem to require utf8mb4 collation.

It shouldn’t require that, it should default to what is specified by WordPress, and if not specified, it defaults to utf8mb4_general_ci. It looks like the charset is being detected properly (as otherwise it would try to default the charset to utf8), but for some reason the collation isn’t being detected properly.

Are you using the latest Matomo for WordPress version (5.1.5)?

• This reply was modified 2 weeks, 6 days ago by dizzyatinnocraft.

@dizzyatinnocraft I’m using the latest version. Is there an option within Matomo’s configuration to override the charset and collation specifically for its archive tables, perhaps something in config.ini.php?

@thecrazy1 In the core product it is possible to set the charset/collation (this affects all Matomo tables not just the archive tables). In Matomo for WordPress that setting is overwritten and set to $wpdb->charset and $wpdb->collate, so in your case setting it wouldn’t have an effect. This is done in /path/to/wordpress/wp-content/plugins/matomo/config/config.php.

In the /path/to/wordpress/wp-content/plugins/matomo/classes/WpMatomo/Installer.php on line 349 there should be this:

$collation = $wpdb->collate ? $wpdb->collate : 'utf8mb4_general_ci';

Would you be able to check if it is there?

If it is there, can you replace the Installer.php file mentioned above with this: https://raw.githubusercontent.com/matomo-org/matomo-for-wordpress/dea1f826bc1e734846fc59d12117e9933d25117f/classes/WpMatomo/Installer.php and see if it changes anything for you?

Apologies for the overly involved steps in diagnosing the problem here, I hope it’s not too inconvenient for you.

Hello @dizzyatinnocraft,

Thank you for the guidance and for sharing the updated Installer.php file. I reviewed and implemented the suggested changes to specify the charset and collation within the file. Specifically, I modified the code to use latin1 as the charset and latin1_swedish_ci as the collation, ensuring compatibility with my WordPress setup.

These adjustments have successfully resolved the issue, and the archiving process is now functioning as expected. I appreciate the detailed support and the troubleshooting steps you provided to work through the configuration.

Thanks once again for your patience and assistance!

@thecrazy1 Did you say you modified the Installer.php file to hardcode the charset/collation you need? If so, that change will be undone when you update the plugin. Or did you overwrite the Installer.php file with the contents of the link I posted?

@dizzyatinnocraft

I did replaced the Installer.php with the version from the link you shared. After doing that, I applied the charset/collation settings as per my WordPress configuration as shown below, which has restored functionality without further issues.

$charset = $wpdb->charset ? $wpdb->charset : 'latin1';
$collation = $wpdb->collate ? $wpdb->collate : 'latin1_swedish_ci';

Could you suggest a way to retain these settings through plugin updates, or do I need to redo this method after every update?

Thanks again for your support on this!

@thecrazy1 Ideally we would find out why the collation is not being detected properly, rather than hardcoding these values in the plugin’s code.

If you revert the latin1/latin1_swedish_ci changes you made, but keep the updated Installer.php file (I only added $wpdb->init_charset(); right above those lines), does it still fail?

@dizzyatinnocraft

Thank you for the continued support.

I’ve discovered that DB_COLLATE is actually set to an empty value in my wp-config.php. I hadn’t realized this until now, and it seems to be the reason Matomo defaults to utf8mb4_general_ci for the archive tables.

I’m concerned about explicitly setting DB_COLLATE as serialized data on the site might break. From reviewing the code in Matomo’s Installer.php, it looks like leaving DB_COLLATE empty causes $collation to default to utf8mb4_general_ci, which may be contributing to this issue.

Would you recommend any way to handle this without a global DB_COLLATE change? Any additional insights would be greatly appreciated.

Thank you again for helping me through this!

@thecrazy1 Ok, let’s go back to hard-coding it in the Installer.php file for now, but with one extra change:

In your wp-config.php, add:

define('MATOMO_DB_CHARSET', 'latin1');
define('MATOMO_DB_COLLATE', 'latin1_swedish_ci');

These constants will have no effect right now, but we’ll make sure in the next version that they will be used, if they are defined.

This way, the hardcoded values will make it work for now, then when the plugin is updated, the constants added to wp-config.php will take effect, and there should be no interruption.

• This reply was modified 2 weeks, 6 days ago by dizzyatinnocraft.

Thank you so much for this solution and the outstanding support! It’s impressive how adaptable and proactive your team is.

I’ll go ahead and add the constants to my wp-config.php now, so everything is ready for the next update. This will make it so much simpler to maintain compatibility with my site’s setup without having to hardcode adjustments after every update.

Your team has been fantastic, and this experience really highlights how customer-focused Matomo’s support is. Thanks again for the quick turnaround and thoughtful resolution!

I’m glad we could help, and please don’t hesitate to reach out if you need anymore help or have feedback for us!

+1 user affected, WordPress 6.7 and last version of matomo plugin : same issue !

i guess we are a lot to endure this, and you dont see it until check reports..

I added this to wp-config :

define('MATOMO_DB_CHARSET', 'latin1');
define('MATOMO_DB_COLLATE', 'latin1_swedish_ci');

Error seems gone..

@kinkmobile thanks for letting us know you are also affected by this specific issue. We will include a better error message, earlier on so affected users can apply the above fix early without having to reach out to support.