MSSQL to MySQL Data migration using MySQL workbench 6.3

MSSQL to MySQL Data migration using MySQL workbench 6.3
MSSQL to MySQL Data migration using MySQL workbench 6.3

Recently I was testing data migration from MSSQL to MySQL using MySQL Workbench. My aim was to include data with all datatype available in MSSQL for migration. In this following blog post will see data migration prerequisites, migration steps and few common errors.

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, Data migration and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

The MySQL Workbench Migration Wizard uses ODBC to connect to a source database, except for MySQL. You will need the ODBC driver installed that corresponds to the database you want to migrate from.

To be able to migrate from Microsoft SQL Server, ensure the following:

DATA SOURCE ODBC Configuration

Add new System data source.

If proper permission not given then at the migration it will throw warning as follow,

Sample table and data with different MS-SQL datatype

-- integer data types CREATE TABLE dbo.int_table ( MyBigIntColumn bigint ,MyIntColumn int ,MySmallIntColumn smallint ,MyTinyIntColumn tinyint ); ALter table int_table add MyBitColumn bit; ALter table int_table add CONSTRAINT bit_def default 1 for MyBitColumn; INSERT INTO dbo.int_table VALUES (9223372036854775807, 214483647,32767,255); update int_table SET MyBitColumn=1; Alter table int_table alter column MyBitColumn bit not null; -- decimal and numeric Data datatypes CREATE TABLE dbo.num_table ( MyDecimalColumn decimal(5,2) ,MyNumericColumn numeric(10,5) ); INSERT INTO dbo.num_table VALUES (123, 12345.12); Alter table num_table add MyMoneycolumn money; Alter table num_table add MysmallMoneycolumn smallmoney; INSERT INTO num_table (MyMoneyColumn,MysmallMoneycolumn) values(20.098,45.68); alter table num_table add c_real real,c_float float (32); INSERT INTO num_table (c_real,c_float) values(2.0,43.67897); -- datetime datatype create table date_table( ID VARCHAR(4) NOT NULL, First_Name VARCHAR(20), Last_Name VARCHAR(20), Start_Date DATE, End_Date DATE, c_time Time, Salary Money, City VARCHAR(20), Description VARCHAR(80), c_datetime datetime, cs_smalldatetime smalldatetime, c_datetime2 datetime2 ) ALTER TABLE date_table ADD CONSTRAINT cc_datetime DEFAULT GETDATE() FOR c_datetime; ALTER TABLE date_table ADD CONSTRAINT cc_time DEFAULT convert(time, getdate()) FOR c_time; ALTER TABLE date_table ADD CONSTRAINT cc_startdate DEFAULT convert(date,getdate()) FOR start_date; ALTER TABLE date_table ADD CONSTRAINT cc_enddate DEFAULT convert(date,getdate()) FOR end_date; -- prepare data insert into date_table(ID, First_Name, Last_Name,Salary, City, Description,cs_smalldatetime,c_datetime2) values ('01','Jason', 'Martin', 1234.56, 'Toronto', 'Programmer','2007-05-08 12:35:00','2007-05-08 12:35:29. 1234567'); insert into date_table(ID, First_Name, Last_Name,Salary, City, Description,cs_smalldatetime,c_datetime2) values('02','Alison', 'Mathews', 2234.78, 'Vancouver','Tester','2016-07-08 12:36:00','2006-07-08 12:36:29. 1234567'); -- char,varchar,nvarchar,tinyint,int,text datatypes CREATE TABLE [dbo].[Employee_2]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](150) NULL, [empid] [int] NOT NULL, [age] [tinyint] NOT NULL, [gender] VARCHAR(10) NOT NULL CHECK (gender IN('male', 'Female', 'Unknown')) ); Alter table Employee_2 add primary key (Id); -- Create a nonclustered index on a table or view CREATE INDEX i1 ON dbo.Employee_2 (Name); --Create a clustered index on a table and use a 3-part name for the table CREATE CLUSTERED INDEX ci1 ON Employee_2([Id]); CREATE UNIQUE INDEX pk1 ON dbo.Employee_2 (Id); -- Create a nonclustered index with a unique constraint on 3 columns and specify the sort order for each column CREATE UNIQUE INDEX ui1 ON dbo.Employee_2 (name DESC, empid ASC, age DESC); INSERT INTO Employee_2 (Name,empid,age,gender) values ('lalit',268981,27,'male'); INSERT INTO Employee_2 (Name,empid,age,gender) values ('harsh',268982,28,'male'); INSERT INTO Employee_2 (Name,empid,age,gender) values ('jina',268983,27,'female'); INSERT INTO Employee_2 (Name,empid,age,gender) values ('xyz',268984,32,'Unknown'); ALTER table employee_2 add emp_notes2 text; update employee_2 SET emp_notes2='test data Migration from mssql- mysql'; CREATE TABLE Persons ( P_Id int NOT NULL, Lastname varchar(40), Firstname varchar(40) NOT NULL, Address varchar(100), City char(50), PRIMARY KEY (P_Id) ); INSERT INTO Persons values(1,'c','lalit','IT park','PUNE'); CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); INsert INTO orders values(1,2234,1); create table binary_table (c_binary binary , c_varbinary varbinary (max) ,c_image image); INSERT INTO binary_table (c_varbinary) values (convert(VARBINARY(max),44));

CREATE TABLE [dbo].[CUSTOMERS]( [ID] [int] NOT NULL, [NAME] [varchar](20) NOT NULL, [AGE] [int] NOT NULL, [ADDRESS] [char](25) NULL, [SALARY] [decimal](18, 2) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

CREATE TABLE [dbo].[Products]( [ProductID] [int] NOT NULL, [ProductName] [varchar](25) NOT NULL, [Price] [money] NULL, [ProductDescription] [text] NULL, [c_time] [datetime] NOT NULL, PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_YourTable] DEFAULT (getdate()) FOR [c_time] GO

Note: Insert appropriate data in above tables for respective datatypes.

Migration using MySQL Workbench

CREATE TRIGGER before_insert_date_table before INSERT ON date_table FOR EACH ROW SET new.Start_Date = curdate() , new.End_Date= curdate() , new.c_time= curtime();

Bulk data transfer:

------------------------------------------------------------------------------------ MySQL Workbench Migration Wizard Report Source: Microsoft SQL Server 12.0.4100 Target: MySQL 5.7.15 ------------------------------------------------------------------------------------ -- Migration -- Summary Number of migrated schemas: 1 -- mysql_migration Source Schema: mysql_migration - Tables: 11 - Triggers: 0 - Views: 4 - Stored Procedures: 3 - Functions: 0 -- Migration Issues - mysql_migration note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - CUSTOMERS note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - NAME note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - ADDRESS note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Products note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - ProductName note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - ProductDescription note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - c_time note Default value is getdate(), so type was changed from DATETIME to TIMESTAMP - Employee note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Name note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Employee_2 note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Name note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - gender note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - emp_notes note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - emp_notes2 note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Persons note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Lastname note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Firstname note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Address note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - City note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Orders note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - int_table note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - MyBitColumn note Source column type BIT was migrated to TINYINT(1) - num_table note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - date_table note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - ID note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - First_Name note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Last_Name note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Start_Date warning Default value CONVERT([date],getdate(),0) is not supported - End_Date warning Default value CONVERT([date],getdate(),0) is not supported - c_time warning Default value CONVERT([time],getdate(),0) is not supported - City note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Description note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - c_datetime note Default value is getdate(), so type was changed from DATETIME to TIMESTAMP - binary_table note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Altiris, Inc_$Item note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - No_ note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Description note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Description 2 note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci - Tax Group Code note Collation SQL_Latin1_General_CP1_CI_AS migrated to utf8_general_ci -- Object Creation Issues -- Migration Details 4.1. Table mysql_migration.CUSTOMERS (CUSTOMERS) Columns: - ID INT - NAME VARCHAR(20) - AGE INT - ADDRESS CHAR(25) - SALARY DECIMAL(18,2) Foreign Keys: Indices: - PRIMARY (ID) 4.2. Table mysql_migration.Products (Products) Columns: - ProductID INT - ProductName VARCHAR(25) - Price DECIMAL(19,4) - ProductDescription LONGTEXT - c_time TIMESTAMP CURRENT_TIMESTAMP Foreign Keys: Indices: - PRIMARY (ProductID) 4.3. Table mysql_migration.Employee (Employee) Columns: - Id INT - Name VARCHAR(150) Foreign Keys: Indices: - PRIMARY (Id) 4.4. Table mysql_migration.Employee_2 (Employee_2) Columns: - Id INT - Name VARCHAR(150) - empid INT - age TINYINT UNSIGNED - gender VARCHAR(10) - emp_notes LONGTEXT - emp_notes2 LONGTEXT Foreign Keys: Indices: - PRIMARY (Id) - ci1 (Id) - i1 (Name) - pk1 (Id) - ui1 (Name, empid, age) 4.5. Table mysql_migration.Persons (Persons) Columns: - P_Id INT - Lastname VARCHAR(40) - Firstname VARCHAR(40) - Address VARCHAR(100) - City CHAR(50) Foreign Keys: Indices: - PRIMARY (P_Id) 4.6. Table mysql_migration.Orders (Orders) Columns: - O_Id INT - OrderNo INT - P_Id INT Foreign Keys: - fk_PerOrders (P_Id) ON Persons (P_Id) Indices: - PRIMARY (O_Id) 4.7. Table mysql_migration.int_table (int_table) Columns: - MyBigIntColumn BIGINT - MyIntColumn INT - MySmallIntColumn SMALLINT - MyTinyIntColumn TINYINT UNSIGNED - MyBitColumn TINYINT(1) 1 Foreign Keys: Indices: 4.8. Table mysql_migration.num_table (num_table) Columns: - MyDecimalColumn DECIMAL(5,2) - MyNumericColumn DECIMAL(10,5) - MyMoneycolumn DECIMAL(19,4) - MysmallMoneycolumn DECIMAL(10,4) - c_real FLOAT(24,0) - c_float DOUBLE Foreign Keys: Indices: 4.9. Table mysql_migration.date_table (date_table) Columns: - ID VARCHAR(4) - First_Name VARCHAR(20) - Last_Name VARCHAR(20) - Start_Date DATE - End_Date DATE - c_time TIME(6) - Salary DECIMAL(19,4) - City VARCHAR(20) - Description VARCHAR(80) - c_datetime TIMESTAMP CURRENT_TIMESTAMP - cs_smalldatetime DATETIME - c_datetime2 DATETIME(6) Foreign Keys: Indices: 4.10. Table mysql_migration.binary_table (binary_table) Columns: - c_binary BINARY(1) - c_varbinary LONGBLOB - c_image LONGBLOB Foreign Keys: Indices: 4.11. Table mysql_migration.Altiris, Inc_$Item (Altiris, Inc_$Item) Columns: - No_ VARCHAR(20) - Description VARCHAR(100) - Description 2 VARCHAR(50) - Blocked TINYINT UNSIGNED - Last Date Modified DATETIME(6) - Inactive TINYINT UNSIGNED - Fixed Node TINYINT UNSIGNED - Minimum Nodes INT - Maximum Nodes INT - Tax Group Code VARCHAR(10) - Current Price List TINYINT UNSIGNED - PrimeKey INT Foreign Keys: Indices: - PRIMARY (PrimeKey) -- Data Copy - `mysql_migration`.`Employee` Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Employee] - `mysql_migration`.`binary_table` Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[binary_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[binary_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[binary_table] - `mysql_migration`.`num_table` Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[num_table] - `mysql_migration`.`Products` error `mysql_migration`.`Products`:malloc(1073741824) failed for blob transfer buffer error `mysql_migration`.`Products`:Failed copying 2 rows Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Products] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Products] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Products] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[Products] - `mysql_migration`.`int_table` Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[int_table] - `mysql_migration`.`Orders` Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Orders] - `mysql_migration`.`Altiris, Inc_$Item` Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] Succeeded : copied 20686 of 20686 rows from [mysql_migration].[dbo].[Altiris, Inc_$Item] - `mysql_migration`.`Employee_2` Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[Employee_2] error `mysql_migration`.`Employee_2`:Could not allocate 1073741824 bytes for row buffer column of emp_notes2 ntext 252 error `mysql_migration`.`Employee_2`:Failed copying 4 rows error `mysql_migration`.`Employee_2`:Could not allocate 1073741824 bytes for row buffer column of emp_notes2 ntext 252 error `mysql_migration`.`Employee_2`:Failed copying 4 rows Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[Employee_2] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[Employee_2] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[Employee_2] Succeeded : copied 4 of 4 rows from [mysql_migration].[dbo].[Employee_2] - `mysql_migration`.`CUSTOMERS` Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] Succeeded : copied 6 of 6 rows from [mysql_migration].[dbo].[CUSTOMERS] - `mysql_migration`.`date_table` Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] Succeeded : copied 2 of 2 rows from [mysql_migration].[dbo].[date_table] - `mysql_migration`.`Persons` Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons] Succeeded : copied 1 of 1 rows from [mysql_migration].[dbo].[Persons]

Validate data in MySQL database and All set !!