a. Automatic Table Detection

• Scans all tables - Identifies current character set and collation for each table

• Excludes BLOB tables - Automatically skips media* and doc* tables (contains binary data)

• Smart status indicators - Shows which tables are already utf8mb4 vs. those needing conversion

• Custom index detection - Identifies tables with custom indexes that need special handling

b. Selective Conversion

• Individual table selection - Choose specific tables to convert

• "Select All" option - Convert all convertible tables at once

• Prevents redundant conversion - Already converted tables are disabled (grayed out)

• Batch processing - Convert multiple tables in a single operation

c. Smart Index Handling

• Index-aware conversion - Detects custom indexes (non-PRIMARY, non-id)

• ROW_FORMAT=DYNAMIC - Automatically applies when custom indexes exist

• Why ROW_FORMAT=DYNAMIC? - Required for large indexes on utf8mb4 tables

d. Safety-First Design

• Backup confirmation required - Admin must confirm database backup exists

• Read-only preview - Review tables before conversion

• Detailed logging - See exactly what was converted (or failed)

• No automatic rollback - Conversion is one-way; backup is mandatory

e. User Experience

• Intuitive interface - Clear table status indicators (✅ Already utf8mb4, ⏳ Can convert)

• Real-time feedback - Immediate status updates during conversion

• Detailed results - Success and error logs with table-by-table details

• Responsive design - Works on desktop and mobile devices

Technical Details

What the Tool Does

1. Analyzes each table's current character set and collation

2. Identifies tables not yet using utf8mb4

3. Generates ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci statements

4. Executes the conversion safely with error handling

5. Reports success and failure for each table

The Conversion Process

-- Standard conversion
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- With ROW_FORMAT=DYNAMIC (for tables with custom indexes)
ALTER TABLE `table_name` ROW_FORMAT=DYNAMIC;
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

What Happens During Conversion

• Data is re-encoded - All text data is converted to utf8mb4

• Binary data is preserved - BLOB columns are not modified (but tool excludes BLOB tables)

• Indexes are rebuilt - MySQL automatically rebuilds indexes for the new character set

• Collation is updated - All columns get the new collation

• Table structure is preserved - No changes to column definitions other than charset/collation

Excluded Tables (media* and doc*)

• media* - Media Centre module (images, videos, audio files as BLOB)

• doc* - Document Centre module (PDFs, Word documents as BLOB)

Why exclude BLOB tables?

• BLOB (Binary Large Object) data is not character-encoded

• BLOB columns are not affected by character set conversion

• Conversion would be unnecessary and wasteful

Collation Choices

utf8mb4_unicode_ci

• Unicode (UCA 4.0.0) collation - Based on Unicode Collation Algorithm

• Case-insensitive ( _ci = case-insensitive)

• Accent-insensitive - Treats å, ä, à, a as the same

• Best for multilingual applications

• Recommended for most use cases

Other utf8mb4 Collations

• utf8mb4_general_ci - Simpler, faster, but less accurate for sorting

• utf8mb4_bin - Binary comparison (case-sensitive)

• utf8mb4_0900_ai_ci - Newer Unicode 9.0 collation (MySQL 8.0+)

Why utf8mb4_unicode_ci?

• Best balance of accuracy and performance

• Handles all languages correctly

• Standard recommendation from MySQL and the community

• Backward compatible with existing data

Security Considerations

Backup Requirement

• Mandatory backup confirmation - Admin must confirm backup exists

• Intentional friction - Prevents accidental data loss

• Immediate action required - No scheduled/convenience backups

Why Backup is Required

• Conversion is irreversible (would require backup restore to revert)

• Potential for unexpected issues (though rare)

• Best practice - Always backup before structural changes

• Chain of trust - Admin takes responsibility for data safety

Risk Factors

• Data corruption - Extremely rare with ALTER TABLE CONVERT

• Index rebuild failure - Possible with very large indexes (ROW_FORMAT=DYNAMIC solves this)

• Disk space - ALTER TABLE requires temporary table space

• Timeout - Large tables may require extended time

What the Tool Does Not Do

1. Change column types - Data types remain the same

2. Modify BLOB data - BLOB columns are binary; no character set applies

3. Convert media files - media* and doc* tables are excluded

4. Alter application logic - No code changes required

5. Update connection strings - Must be configured separately

Post-Conversion Actions

Immediate

1. Test the applicationt horoughly

2. Verify special characters (emojis, åäö, etc.)

3. Check database performance after conversion

Long-term

1. Monitor performance - Indexes may need optimisation

2. Update development environments - Match production charset

3. Document changes - Record conversion date and tables affected

Important Notes

• No application code changes required - The conversion only affects the database

• No connection string changes needed - The MySQL Connector automatically handles charset negotiation

• >Existing data is preserved - All data is re-encoded to utf8mb4

Configuration Updates

1. Update connection strings - Ensure charset=utf8mb4 is set

2. Update application settings - Check for hardcoded charset values

3. Review web.config - Add ?charset=utf8mb4 if needed

Long-term

1. Monitor performance - Indexes may need optimisation

2. Update development environments - Match production charset

3. Document changes - Record conversion date and tables affected

Troubleshooting

Error: "Specified key was too long"

• Cause: Index key length exceeds 767 bytes

• Solution: Enable ROW_FORMAT=DYNAMIC checkbox

• Prevention: Always use DYNAMIC for utf8mb4 tables

Error: "Timeout during ALTER TABLE"

• Cause: Table is very large

• Solution: Convert during low-traffic periods

• Alternative: Consider table-by-table conversion

Error: "Disk space full"

• Cause: ALTER TABLE creates temporary table space

• Solution: Free up disk space before conversion

• Prevention: Check available space before starting

Error: "Table lock timeout"

• Cause: Long-running queries blocking ALTER TABLE

• Solution: Convert during maintenance window

• Alternative: Use pt-online-schema-change (advanced)

Performance Impact

During Conversion

• Table locks - Tables are locked during ALTER TABLE

• Index rebuild - All indexes are rebuilt (takes time)

• Disk I/O - Temporary tables require disk space

• CPU usage - Character conversion is CPU-intensive

After Conversion

• Slightly larger indexes - utf8mb4 uses 4 bytes vs. 3 bytes

• Storage increase - 0-33% larger for text columns

• Same query performance - Well-indexed tables unaffected

• Better sorting - Unicode collation handles all languages

Best Practices

1. Always backup before conversion - Mandatory for safety

2. Convert during maintenance windows - Minimise user impact

3. Enable ROW_FORMAT=DYNAMIC - Prevents index errors

4. Convert in batches - Large tables one at a time

5. Test after conversion - Verify application functionality

6. Update connection strings - Use charset=utf8mb4

7. Monitor for errors - Check error logs during conversion

8. Keep old backup - Don't delete backup immediately

---

This tool is part of the n-gen.net CMS admin suite, version 1.3 or higher.

How to use the n-gen.net CMS UTF8mb4
character set converter Database Tool

Secure your database by backing it up (Export)

The n-gen.net CMS Database Character Conversion Tool can be accessed from the back side of the Admin Cube or from the Admin Ribbon (right most) gear icon => Tools, Database => Convert Char set.

When you open the DB tool the page will look like this:

Remarks

When you insert an emoji directly as a copy/paste operation into a non-UTF8MB4 table the emoji will only display a "?" (question mark) in most browser even if the WYSIWYG editor display the emoji correct when you paste the emoji into your text. But as an i.e. Latin_1 table cannot store the emoji the result is a ? when you retrieve it from the database for display on your page.

Note: The tool is only directed towards the MySql database family (MySql, Percona and MariaDB) and will not run on a Microsoft SQL server installation.