Posted inMySQL

Migrating MSSQL to MySQL: Pro Tips for Troubleshooting and Data Type Mismatches

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Start Now: A 6-Step Migration Workflow

If you need to quickly migrate a few tables for testing, here is the shortest path. Ensure you have MySQL Workbench and the ODBC Driver for SQL Server installed before starting.

  1. Open Wizard: Go to the Database menu -> Migration Wizard.
  2. Connect to Source (MSSQL): Select Microsoft SQL Server. Use an ODBC Data Source to avoid minor connection errors.
  3. Connect to Target (MySQL): Point to the MySQL instance where you want to import the data.
  4. Select Schema: Check the databases you want to “move.”
  5. Reverse Engineer: Workbench will scan the table structure. Don’t click Next too fast; stop to check the mapping here.
  6. Transfer Data: Click Next to let the tool automatically create the schema and copy the data.

Reality is rarely that simple. With production databases, the failure rate at steps 5 and 6 is high. Usually, errors stem from discrepancies between T-SQL and MySQL.

Why is MSSQL to MySQL Migration Such a Headache?

I once participated in an ERP migration project with over 200 tables and 500GB of data from SQL Server to MySQL. Initially, the team thought we could just click a button and be done. The result? We stayed up for two nights just to fix Data truncation errors and drastic query performance drops.

The main reason is the difference in storage philosophy. MSSQL is very “lenient” with flexible data types. Conversely, MySQL requires absolute precision regarding length and storage configuration. If you let the tool handle mapping automatically, you’ll likely end up with a mess of garbage data.

Detailed Compatibility Handling (The Technical Meat)

1. Configuring Connection via ODBC

Instead of entering the IP directly into Workbench, go to Control Panel -> ODBC Data Sources (64-bit). Create a System DSN pointing to SQL Server. This ensures a more stable connection, especially when migrating large datasets over a local network.

# Quick check for SQL Server access
sqlcmd -S 192.168.1.10 -U sa -P YourStrongPassword

2. Data Type Mapping: Key Considerations

This is the “hard-earned” mapping table to help you avoid data loss. During the Object Migration step, select Show Selection to manually edit:

  • DATETIME2 to DATETIME(6): MSSQL DATETIME2 has precision up to 100 nanoseconds. If you only map it to MySQL’s default DATETIME, you’ll lose the milliseconds. Use DATETIME(6) to maintain precision.
  • NVARCHAR(MAX) to VARCHAR(n): Workbench often defaults to LONGTEXT. However, if the actual data is under 4000 characters, force it to VARCHAR(4000). This allows you to use Indexes, which LONGTEXT handles poorly.
  • BIT to TINYINT(1): MySQL doesn’t have a true Boolean type; it uses TINYINT(1). Check your application logic to ensure it interprets 0/1 as True/False.
  • UNIQUEIDENTIFIER to CHAR(36): MySQL lacks a dedicated UUID type. The best approach is to use CHAR(36) and handle the UUID() function at the application layer.

3. Handling Stored Procedures and Triggers

Important note: Workbench Migration Wizard is notoriously bad at translating code logic. T-SQL uses @Variable, while MySQL uses DECLARE. MSSQL has TOP, MySQL uses LIMIT.

My advice: Skip the Routine/Trigger section in the Wizard entirely. Migrate the data first, then manually rewrite the logic to optimize it for MySQL.

Pro Tips for Handling Big Data

If your database is larger than 20GB, pushing data directly through Workbench is “suicide” due to timeout risks. Use the professional 3-step workflow:

  1. Use the Wizard for Schema only (only create the table structure).
  2. Use MSSQL’s bcp (Bulk Copy Program) tool to export data to CSV files.
  3. Use the LOAD DATA INFILE command to load the CSV into MySQL. This method can reduce migration time from 10 hours to 30 minutes.
-- Ultra-fast data loading with LOAD DATA
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Real-World Experience to Avoid Costly Mistakes

Vietnamese Font Errors: MSSQL often uses the SQL_Latin1_General_CP1_CI_AS collation. When moving to MySQL, you must choose utf8mb4 (specifically utf8mb4_0900_ai_ci on MySQL 8.0). Don’t let clients complain because their names turned into question marks.

Case Sensitivity: On Linux, MySQL table names are case-sensitive (e.g., Users is different from users). If your legacy code is inconsistent, set the lower_case_table_names=1 parameter in the my.cnf file before migrating.

Foreign Key Checks: Legacy data sometimes contains “orphan” records. MySQL’s Strict Mode will block Foreign Key creation if the data doesn’t match. Temporarily disable checks to clean up later.

SET FOREIGN_KEY_CHECKS = 0;
-- Perform data loading or fix errors here
SET FOREIGN_KEY_CHECKS = 1;

In short, migration is a battle of meticulousness. Never fully trust automated tools. Always test thoroughly on a backup before performing it on production. If you encounter any “stubborn” mapping errors, leave a comment below!

Share:
Tags: