Emoji's in emails causing 500 error

Product Support
1

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

2

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;
4 Likes
3

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 :slightly_smiling_face:

  1. Start with backup, thats very important
  2. 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 :wink: