How to migrate MsSql database to MySql
There are so many reason when we've to migrate our base-end database from MsSql to MySql. But I think the most one is the price. Since MySql is NOT free if you do not use it to develop a software under the GPL, but the cost of using MySql is much lower than MsSql. This is the same situation I've just met.
There are many diffrences between MsSql and MySql so we need spend more time to investigate, research and trail migration.
Database Creation SQL Script Modification
Modify the database creation SQL script, so that can create the database (including tables, columns, keys, triggers and stored procedures on MySQL).
Be careful that insert a delimiter (default ‘;’) after each statement. Since MSSQL does not need the delimiter but in MySQL it’s necessary.
Delete Constraints and Indexes
It’s not necessary to drop the indexes before drop the table in MySQL. So comment the statements related with indexes dropping. But you’ve to drop the foreign keys first.
Example:
[Before]
alter table t_server_pair
drop constraint FK_SERVER_P_REFERENCE_T_SERVER_P
go
[After]
alter table t_server_pair drop foreign key FK_SERVER_P_REFERENCE_T_SERVER_P;
Drop Tables
If the table is not existed, do not drop it. Or there will be an error raised. To fix it, insert ‘if exists’ keyword before table name in drop statement to determine weather the table is existed or not.
Example:
[Before]
if exists (select 1
from sysobjects
where id = object_id('t_server_pair')
and type = 'U')
drop table t_server_pair
go
[After]
drop table if exists t_server_pair;
Create Tables
For the simple create table script, just copy them from MSSQL to MySQL and try to run it. It might works well.
But there does still something can not be run in MySQL directly.
If your column has a default value which set to a function, you have to remove it. Because MySQL does not supply set a function value to a column’s default value. Column default value must be a constraint string, number, date, etc.
If a column is defined with identity in MSSQL, you must translate them into auto_increment.
Example:
[Before]
create table t_account (
account_id int identity (2,1),
account varchar(255) not null,
primary_server_id smallint not null,
secondary_server_id smallint not null,
constraint PK_T_ACCOUNT primary key (account_id)
)
[After]
create table t_account (
account_id int auto_increment,
account varchar(255) not null,
primary_server_id smallint not null,
secondary_server_id smallint not null,
constraint PK_T_ACCOUNT primary key (account_id)
);
To start with an AUTO_INCREMENT value other than 1, we can only implement to in stored procedure. Although we can use ‘AUTO_INCREMENT = n’ to specify the starting value but it is only available in MyISAM database engine. MyISAM engine does not supply foreign key, so we’ve to simulate this feature in stored procedure.
PS: These is only one table uses auto increment starting over than 1 in Zafeweb database.
Create Indexes
Just copy the MSSQL index creation script. But the sort order feature is not allowed in MySQL. You can ignore them. All indexes will be in Normal Type.
Create Foreign Keys and Constraints
Just copy the SQL script from MSSQL and run at MySQL.
Check constraint does not support in MySql, so you have to remove it and implment to on your client-side code.
Drop Stored Procedures
As the foreign keys, you must drop the stored procedure before create. To drop the stored procedure, just use ‘if exist’ keyword like how to drop table.
Example:
[Before]
if exists (select 1 from sysobjects
where name = 'p_create_account' and type = 'P')
drop proc p_create_account
go
[After]
drop procedure if exists p_create_account;
Stored Procedures Migration Manual
There’re so many difference between MySQL and MSSQL in stored procedures.
Announcement
Example:
[Before]
create proc
[After]
create procedure
Delimiter
Stored procedure does not need delimiter, or we can say the limiter is return key. But in MySQL, after one statement we should add ‘;’ to delimit it.
Variant
Variants defined in MSSQL must be started with ‘@’, but in MySQL it means global variant. Modify the entire variants name. For example, change ‘@[VARIANT_NAME]’ to ‘var_[VARIANT_NAME]’.
And if you want to set a new value to a varint, you must use ‘set’ keyword just like QBasic.
Example:
[Before]
@primary_server_id = 0
[After]
set var_primary_server_id = 0;
Parameters
MSSQL defines the in/out property of parameters after the type define, but in MySQL you should specify the in/out property first.
Example:
[Before]
create proc p_create_account (
@account varchar(255),
@account_id int output,
@primary_server_url nvarchar(1024) output,
@secondary_server_url nvarchar(1024) output
)
[After]
create procedure p_create_account (
in var_account varchar(255),
out var_account_id int,
out var_primary_server_url varchar(1024),
out var_secondary_server_url varchar(1024),
out RETURN_VALUE int
)
Return Value
In MySQL, only function can return a value to the calling client. That means, to migrate from MSSQL, we should implement the return value manually. Add a out property, int type parameter named RETURN_VALUE.
Add a entire lable around the whole stored procedure so that when we need to return, we can jump to the end of the stored procedure.
And then, when the code try to execute likes ‘return [num]’, we must change it to ‘set RETURN_VALUE = [num];’ and ‘leave [stored_procedure_entire_label]’.
Example:
[Before]
create proc p_create_account (
@account varchar(255),
@account_id int output,
@primary_server_url varchar(1024) output,
@secondary_server_url varchar(1024) output
) as
Begin
……
if @@rowcount = 0
return -100
……
end
go
[After]
create procedure p_create_account (
in var_account varchar(255),
out var_account_id int,
out var_primary_server_url varchar(1024),
out var_secondary_server_url varchar(1024),
out RETURN_VALUE int
)
p_create_account_main:
begin
……
if found_rows() = 0 then
begin
set RETURN_VALUE = -100;
leave p_create_account_main;
end;
end if;
……
end p_create_account_main;
if exists (select …) Statements
‘exists’ statement just can be used after ‘where’ in MySQL. So we need to change these codes. We need one more local variant to save the row counts so that we can use it later.
Example:
[Before]
if exists (select 1 from t_account where account = @account)
[After]
declare var_rowcount int;
select count(*) into var_rowcount from t_account where account = var_account;
if var_rowcount > 0 then
Fetch the Data into a Variant
[Before]
select @primary_server_id = primary_server_id, @secondary_server_id = secondary_server_id
from t_server_pair
where status = 'A'
[After]
select primary_server_id, secondary_server_id into var_primary_server_id, var_secondary_server_id
from t_server_pair
where status = 'A';
Concatenating Strings
In MSSQL, we can use ‘+’ to connect strings into one string. But it’s not porssible in MySQL. We’ve to use ‘concat’ function to implement it.
Example:
[Before]
select @primary_server_url = 'http://'+rtrim(host_address)+':'+rtrim(port)+'/'+rtrim(application_name), @account_id = account_id
from t_account, t_server
where account = @account
and server_id = primary_server_id
[After]
select concat('http://', rtrim(host_address), ':', rtrim(port), '/', rtrim(application_name)),
account_id into var_primary_server_url, var_account_id
from t_account, t_server
where account = var_account
and server_id = primary_server_id;
Get the Executing Row Count
@@rowcount is not avaliable in MySQL. For the SELECT statement, use found_rows() to get the count of found rows; for DELETE, INSERT and UPDATE, use row_count() function to get the affected row count.
PS: If no row affected, row_count() returns -1 (not zero), so please use ‘if row_count() <= 0’ instead of ‘if row_count() = 0’.
Get the Last Increment Column Value
[Before]
select @account_id = scope_identity()
[After]
set var_account_id = last_insert_id();
Optional Parameters
Optional parameters are not allowed in MySQL. To simnulate this feature, we’ve to add some logic. If the calling client sets this parameter to NULL, we use the default value; otherwise uses directly.
Example:
[Before]
create proc p_register_server (
@host_name nvarchar(255),
@host_address nvarchar(255),
@port char(10),
@application_name char(32) = 'ZafeServer'
)as
[After]
create procedure p_register_server (
in var_host_name varchar(255),
in var_host_address varchar(255),
in var_port char(10),
in var_application_name char(32),
out RETURN_VALUE int
)
……
if isnull(var_application_name) or trim(var_application_name) = "" then
set var_application_name = 'ZafeServer';
end if;
Data Type of Parameters
Although in MySQL 5.0 or later, nvarchar data type is avaliable in stored procedures, we still need to convert them to varchar because it’s not recongnized by the calling client ADO.NET driver.
Trigger Migration
Trigger Creation
The creation statements is difference bewteen MSSQL and MySQL. In MySQL, trigger can be launched when rows are inserted, deleted and updated. There’re tow more trigger time, before and after. So in migration, we need to conver the creatin statements in to MySQL style.
Example:
[Before]
create trigger g_upd_setting on t_setting for update as
begin
[After]
create trigger g_upd_setting before update on t_setting for each row
begin
Trigger Statements
The statements in trigger are same as in stored procedures just except some additional keywords. We can use ‘new’ and ‘old’ keyword to specifiy the column value befor or after triggr occurs.
Example:
[Before]
where t_setting.account_id = inserted.account_id
[After]
where t_setting.account_id = new.account_id
Column Modification Trigger
In MSSQL, we can use Update() function to determinde weather this column is modified or not. So that we can use this to control our triggrs. But in MySQL, functions like these are not supportted. So that we need to add some statements to simulate it.
Example:
[Before]
if not update(config_time) begin
[After]
if new.config_time = old.config_time then
Loop Trigger
That means you can not update the table itself in the “before update” trigger. Because it will cause a trigger loop. You can use “after trigger” intead of.
ADO.Net Program
Parameter Name
In MySql ADO.Net driver, the parameter name must be prefixed with “?”. That means if you parameter in stroed procedure or fuunction is “param1”, you have to name it as “?param1” on client-side code.
Return Value Parameter
Although MySql does not support return value on stored procedure, you can also set your return parameter direction as ReturnValue. It might be auto converted to output parameter. You can also set it as Output parameter.
MySqlDataReader.GetBytes Method
Use MySqlDataReader.Getbytes method can get the byte array from the data reader. But if the field is defined as “binary” or “varbinary”, you can not do it. Because in MySql, “binary” and “varbinary” field will be regarded as the string field, just can contain those Non-ASCII chars.
If you want to use the pure binary data, define those fields as “tinyblob”, “blob”, “middleblob” or “largeblob”.
Continually Using ExecuteReader and ExecuteNonQuery
I’m not sure but I know it might be lead to the magical exceptions and error if you continually using ExecuteReader and ExecuteNonQuery method. Especially the ExecuteReader method. So, you might have to add some kind of delay procedure befre you use ExecuteNonQuery method if you’ve just used ExecuteReader. Otherwise, you may probably run into some thing like “DataReader does not close”, “Connection unexpected terminated” or “Expected the end of packet” error. And you don’t know when, which code and why it occurs.
