Emoji's in emails causing 500 error

robm · April 12, 2023, 4:38pm

Your software
My Mautic version is: 4.4.5
My PHP version is:7.4.3
My Database type and version is: Maria DB 10.3.34

Your problem
If I create an email with emoji’s I get a 500 internal server error on saving. I have tried both with the grapejs builder and in code mode with the same result.

I have look at my DB and the tables show a mix of utf8_unicode_ci and utf8mb4_unicode_ci for the tables.

The bundle_grapesjsbuilder table is utf8mb4_unicode_ci

The suggested fixes found on this forum say the DB should be utf8mb4 and I when I check it says it the DB is utf8mb4 although it seems that is not the case for all tables.

These errors are showing in the log: none

Steps I have tried to fix the problem: hunting this forum

patryk.gruszka · May 15, 2023, 2:05pm

You are probably using a database that was created a long time ago, before the default charset was changed to utf8mb4. Have you tried converting the email table to utf8mb4? If not, make sure you have a backup of the database in case something goes wrong. Then run this update:

ALTER TABLE `emails`
    CONVERT TO CHARACTER SET utf8mb4
        COLLATE utf8mb4_unicode_ci;

luk4s · May 26, 2025, 7:59am

Hi,
I had same issue in my upgraded mautic5 instance. Original there was encoding utf8mb3, so I create SQL script which convert ALL tables (and database).

Let me share it here, maybe it will be useful for someone

Start with backup, thats very important
Run SQL which convert database and EVERY table within

backup:

mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASSWORD}" "${MYSQL_DATABASE}" |gzip> ./${MYSQL_DATABASE}_$(date +%F-%H_%M).sql.gz

convert script:

-- Mautic5 Production Database Charset Conversion to utf8mb4
-- Execute this script to convert all tables from utf8mb3 to utf8mb4
-- WARNING: This operation may take a long time for large tables
-- RECOMMENDATION: Run during maintenance window and backup database first

-- Set session variables for better performance
SET SESSION innodb_lock_wait_timeout = 300;
SET SESSION lock_wait_timeout = 300;

-- IMPORTANT: Disable foreign key checks during conversion
-- This prevents FK compatibility errors during charset conversion
SET FOREIGN_KEY_CHECKS = 0;

-- Convert database charset
SELECT 'Starting database charset conversion...' as status;
ALTER DATABASE `mautic5_production` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT 'Database charset converted successfully' as status;

-- Convert all tables
SELECT 'Starting table conversions...' as status;

SET @start_time = NOW();
ALTER TABLE `asset_downloads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: asset_downloads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `assets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: assets - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `audit_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: audit_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `bundle_grapesjsbuilder` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: bundle_grapesjsbuilder - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `cache_items` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: cache_items - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_events` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_events - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_form_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_form_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_lead_event_failed_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_lead_event_failed_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_lead_event_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_lead_event_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_leadlist_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_leadlist_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_leads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_leads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaign_summary` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaign_summary - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `campaigns` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: campaigns - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: categories - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `channel_url_trackables` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: channel_url_trackables - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `companies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: companies - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `companies_leads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: companies_leads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `contact_export_scheduler` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: contact_export_scheduler - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `contact_merge_records` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: contact_merge_records - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `dynamic_content` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: dynamic_content - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `dynamic_content_lead_data` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: dynamic_content_lead_data - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `dynamic_content_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: dynamic_content_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_assets_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_assets_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_copies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_copies - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_list_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_list_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_stat_replies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_stat_replies - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `email_stats_devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: email_stats_devices - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `emails` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: emails - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `focus` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: focus - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `focus_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: focus_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `form_actions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: form_actions - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `form_fields` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: form_fields - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `form_submissions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: form_submissions - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `forms` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: forms - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `imports` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: imports - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `integration_entity` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: integration_entity - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `ip_addresses` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: ip_addresses - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_categories` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_categories - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_companies_change_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_companies_change_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_devices - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_donotcontact` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_donotcontact - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_event_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_event_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_fields` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_fields - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_frequencyrules` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_frequencyrules - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_ips_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_ips_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_lists` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_lists - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_lists_leads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_lists_leads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_notes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_notes - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_points_change_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_points_change_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_stages_change_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_stages_change_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_tags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_tags - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_tags_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_tags_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `lead_utmtags` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: lead_utmtags - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `leads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: leads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `message_channels` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: message_channels - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `message_queue` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: message_queue - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: messages - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `messenger_messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: messenger_messages - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: migrations - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `monitor_post_count` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: monitor_post_count - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `monitoring` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: monitoring - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `monitoring_leads` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: monitoring_leads - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `notifications` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: notifications - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `oauth2_accesstokens` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: oauth2_accesstokens - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `oauth2_authcodes` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: oauth2_authcodes - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `oauth2_clients` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: oauth2_clients - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `oauth2_refreshtokens` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: oauth2_refreshtokens - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `oauth2_user_client_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: oauth2_user_client_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `page_hits` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: page_hits - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `page_redirects` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: page_redirects - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `pages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: pages - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `permissions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: permissions - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `plugin_citrix_events` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: plugin_citrix_events - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `plugin_integration_settings` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: plugin_integration_settings - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `plugins` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: plugins - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_group_contact_score` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_group_contact_score - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_groups - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_lead_action_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_lead_action_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_lead_event_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_lead_event_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_trigger_events` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_trigger_events - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `point_triggers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: point_triggers - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `points` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: points - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `push_ids` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: push_ids - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `push_notification_list_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: push_notification_list_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `push_notification_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: push_notification_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `push_notifications` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: push_notifications - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `reports` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: reports - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `reports_schedulers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: reports_schedulers - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `roles` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: roles - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `saml_id_entry` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: saml_id_entry - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `sms_message_list_xref` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: sms_message_list_xref - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `sms_message_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: sms_message_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `sms_messages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: sms_messages - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `stage_lead_action_log` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: stage_lead_action_log - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `stages` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: stages - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `sync_object_field_change_report` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: sync_object_field_change_report - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `sync_object_mapping` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: sync_object_mapping - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `tweet_stats` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: tweet_stats - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `tweets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: tweets - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `user_tokens` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: user_tokens - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: users - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `video_hits` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: video_hits - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `webhook_events` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: webhook_events - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `webhook_logs` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: webhook_logs - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `webhook_queue` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: webhook_queue - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `webhooks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: webhooks - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

SET @start_time = NOW();
ALTER TABLE `widgets` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SELECT CONCAT('✓ Completed: widgets - Duration: ', TIMEDIFF(NOW(), @start_time)) as status;

-- Final verification
SELECT 'All tables converted successfully!' as final_status;
SELECT 'Verifying database charset...' as verification;

-- Check database charset
SELECT
    SCHEMA_NAME as database_name,
    DEFAULT_CHARACTER_SET_NAME as charset,
    DEFAULT_COLLATION_NAME as collation
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mautic5_production';

-- Count tables with utf8mb4
SELECT
    'Tables with utf8mb4:' as summary,
    COUNT(*) as count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mautic5_production'
    AND TABLE_COLLATION LIKE 'utf8mb4%';

-- Count remaining non-utf8mb4 tables (should be 0)
SELECT
    'Tables still NOT utf8mb4:' as summary,
    COUNT(*) as count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mautic5_production'
    AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

SELECT 'Charset conversion completed! 🎉' as final_message;%

note:

In script I am using database mautic5_production. You will probably need to rename it to YOUR database name. Feel free to use CMD+R

Topic		Replies	Views
Emoji in subject line differs when using campaigns Product Support	2	250	September 18, 2023
500 Internall Server on saving email Product Support	6	254	August 25, 2024
Mautic 5.0.3 - Characters in certain button labels and config values are being encoded Mautic 5 Install/Upgrade Support mautic-5	2	182	February 15, 2024
SELF HOSTED \| Error \| 500 error after adding emoji to subject line General Discussion	6	883	February 2, 2020
Database structure and emoticon problems due to collation // utf8 vs utf8mb4 Product Support mautic-4	0	214	November 10, 2023

Emoji's in emails causing 500 error

Related topics