Database Migration Pitfalls: Handling Character Sets, Stored Procedures, and Permissions

On the surface, database migration is just “export + import,” but successful import does not equal successful migration. Three types of issues will not throw errors during import; they only surface after running for a while—character sets, stored procedures, and permissions.

Pitfall 1: Character Sets (Most Common & Easily Overlooked)

How the Problem Occurs

The utf8 in MySQL is not complete UTF-8; it supports a maximum of 3 bytes. However, emojis and some rare characters require 4 bytes. Therefore:

• Old DB is utf8, new DB is utf8mb4 → Migration works fine (more permissive)
• Old DB is utf8mb4, new DB is utf8 → 4-byte characters cannot be stored or are truncated to question marks

# Check database character set
mysql -e "SELECT schema_name, default_character_set_name, default_collation_name
          FROM information_schema.schemata;" 

# Check table character set
mysql -e "SELECT table_name, table_collation FROM information_schema.tables
          WHERE table_schema='dbname';"

# Check column-level settings (column-level overrides table-level)
mysql -e "SELECT table_name, column_name, character_set_name, collation_name
          FROM information_schema.columns
          WHERE table_schema='dbname' AND data_type LIKE '%char%';"

Correct Approach

# Explicitly specify character set during export
mysqldump -u root -p --default-character-set=utf8mb4 
  --single-transaction --routines --triggers --events dbname > db.sql

# Confirm target database character set before import
mysql -e "CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4
          COLLATE utf8mb4_unicode_ci;"

# Specify character set during import as well
mysql --default-character-set=utf8mb4 dbname < db.sql

# Post-migration verification: Spot-check content with special characters
mysql -e "SELECT id, title FROM dbname.posts
          WHERE title LIKE '%emoji%' LIMIT 5;"

This verification step cannot be skipped. No error during import does not guarantee that 4-byte content is intact.

Pitfall 2: Stored Procedures, Functions, and Events

mysqldump does not export stored procedures and functions by default; you need to explicitly add --routines. The event scheduler requires --events.

# Full export (recommended as standard practice)
mysqldump -u root -p --default-character-set=utf8mb4 
  --single-transaction --routines --triggers --events 
  --set-gtid-purged=OFF dbname > db.sql

# Compare object counts before and after migration (simplest validation)
mysql -Nse "SELECT COUNT(*) FROM information_schema.routines
            WHERE routine_schema='dbname';"
mysql -Nse "SELECT COUNT(*) FROM information_schema.events
            WHERE event_schema='dbname';"
mysql -Nse "SELECT COUNT(*) FROM information_schema.triggers
            WHERE trigger_schema='dbname';"

Comparing object counts before and after migration is the most hassle-free validation method. A successful import message doesn’t mean everything migrated correctly; counting objects reveals the truth immediately.

Additionally: The event scheduler is disabled by default on the new database, so even if events are imported successfully, they will not execute.

# Check if event scheduler is enabled
mysql -e "SHOW VARIABLES LIKE 'event_scheduler';"

# Enable it (persist via config file)
# my.cnf: event_scheduler=ON

Pitfall 3: Permissions

You are importing data, but user accounts do not migrate with it. Common symptoms: Import succeeds, but the application cannot connect, reporting access denied.

# Export users and privileges (Method 1: Query and recreate manually)
mysql -Nse "SELECT CONCAT('SHOW CREATE USER ''',user,'''@''',host,''';')
            FROM mysql.user;" | mysql -N | sed 's/$/;/' > users.sql
mysql -Nse "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')
            FROM mysql.user;" | mysql -N | sed 's/$/;/' >> users.sql

# Method 2: Create user and grant privileges directly (More common)
mysql -e "CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';"
mysql -e "GRANT ALL PRIVILEGES ON dbname.* TO 'appuser'@'localhost';"
mysql -e "FLUSH PRIVILEGES;"

# Verify
mysql -u appuser -p -e "USE dbname; SELECT COUNT(*) FROM main_table;"

After import, you must verify connectivity using the application account (not root). Passing tests with root but failing with the application account is the most typical awkward scenario post-migration.

Standard Migration Workflow

# ① Record pre-migration object counts (validation baseline)
mysql -Nse "SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema='dbname';"
mysql -Nse "SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema='dbname';"

# ② Full export
mysqldump -u root -p --default-character-set=utf8mb4 
  --single-transaction --routines --triggers --events dbname > db.sql

# ③ Create database (specify character set)
mysql -e "CREATE DATABASE dbname DEFAULT CHARACTER SET utf8mb4
          COLLATE utf8mb4_unicode_ci;"

# ④ Import
mysql --default-character-set=utf8mb4 dbname < db.sql

# ⑤ Create account and grant privileges
# ⑥ Validation: Compare object counts + row counts + spot-check special characters
mysql -Nse "SELECT COUNT(*) FROM dbname.main_table;"

Decision Tree

Abnormalities after database migration
├─ Special characters turn into '?' / Errors
│  └─ Character set mismatch → Specify utf8mb4 for both export/import, use utf8mb4 for target DB
├─ Scheduled tasks not executing
│  ├─ Events not exported    → Add --events
│  └─ event_scheduler is off → Enable it
├─ Stored procedures not found
│  └─ Missing --routines → Re-export
├─ Application cannot connect
│  ├─ User does not exist  → Create user and grant privileges
│  └─ Root connects but app fails → Privileges not granted to app account
└─ Syntax errors during import
   └─ Version differences (reserved words, etc.) → Check specific error line

Scenario Walkthrough: Some Content Turns into Question Marks After Migration

Symptom: After database migration, most site content is normal,
but text in certain fields displays as question marks or garbled boxes, affecting only part of the content.

1. Determine if it’s a display issue or storage issue → Query these fields directly in the database.
   If it’s still garbled there, the data was incorrect at the storage stage, not a page rendering issue.
2. Compare character set settings on both ends → Inconsistencies between source and target at the database, table, and connection levels.
   If any layer mismatches, conversion occurs during write operations.
3. Understand why “part” is affected → Only characters outside the old character set’s range cause errors
   (e.g., emojis, rare characters). Ordinary Chinese characters look normal, delaying discovery of the problem.
4. Confirm which layers need unification → Server settings, database/table definitions, and connection declarations must all match.
5. Unify to complete character set and convert → Convert the character set and collation of the database and related tables uniformly.
6. Verify with samples containing special characters → Specifically re-write and read back those previously problematic characters,
   rather than just checking ordinary pages.

# Check character sets layer by layer (Server / DB / Table / Connection)
mysql -e "SHOW VARIABLES LIKE 'character_set%';"
mysql -e "SELECT @@character_set_database, @@collation_database;"
mysql -e "SHOW CREATE TABLE table_nameG" | grep -i charset

# Uniform conversion (backup first)
mysqldump --default-character-set=utf8mb4 dbname > bak.sql
# ALTER DATABASE dbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Verification: Use previously problematic characters for read/write tests
mysql -e "SELECT HEX(column_name) FROM table_name LIMIT 1;"

Root Cause: During migration, the character set underwent implicit conversion at one layer,
irreversibly replacing out-of-range characters. The reason “part” of the content is affected
is that ordinary Chinese characters are unaffected; only special characters expose the issue.

Two key points to remember:

• Check three layers for character sets: Server, Database/Table structure, and Connection declaration.
  Changing only one layer is insufficient; inconsistency among the three still causes conversion.
• Verify using characters that would fail, not ordinary Chinese characters.
  Passing with plain text doesn’t prove correct conversion; out-of-range characters are the true test samples.

8 Common Pitfalls

1. Creating database without specifying character set: Using defaults leads to loss of 4-byte characters.
2. Not specifying character set during export/import: Issues arise when both ends differ.
3. Using default mysqldump: Stored procedures and events are not exported.
4. Forgetting event_scheduler: Events are imported but do not execute.
5. Importing data but not accounts: Application cannot connect.
6. Considering success if root passes: Must test with the application account.
7. Not comparing object counts: Missing items go unnoticed.
8. Deleting old DB immediately after successful import: No rollback possible if issues are found.

Real Customer Feedback (From Cloud Market Reviews & Inquiries for Similar Products)

The following excerpts are from real buyer reviews and inquiries for similar O&M services on Alibaba Cloud / Tencent Cloud / Huawei Cloud markets (providers include our store and peers, provided for reference only, not fictional tickets):

10***02 2024-07-31 5.0 Secure & Reliable | Comprehensive Features | Affordable Timely handling, migration successful 10***31 2022-06-22 5.0 Affordable | Skilled Tech | Perfect After-sales Good job, helped me migrate an old site spanning several years to a new server.

10***31 2022-11-17 5.0 On-time Delivery | Timely Response | Affordable System migration successful 10***31 2022-11-17 5.0 Skilled Tech | Secure & Reliable | Affordable Migration successful

1****n Rating: 5.0 Points 2018-01-04 18:04