Migrate 10 Gb database from MSSQL to MYSQL

Question

Migrate 10 Gb database from MSSQL to MYSQL

November 14, 2022 at 8:50 am

This was removed by the editor as SPAM

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Saran Right there with Babe Points: 753 More actions · Answer 1

Thank for your valuable time frederico_fonseca for testing it out.

I have tried earlier the other two methods as you said. It is bit complicated with create schema should match with source and target and collation etc. Following is the error, I just tried to load single table, I have 100+ tables, not sure How long it would take to load each tables one by one with fixing error.

Also, other important thing I have noticed - Data is mismatching after a load between MSSQL and MYSQL.

Ex: I picked random row - let's says where ID= 100, in MYSQL ID 100 in MYSQL having lot of missing data, since I have given data type as longtext.

20:15:41 LOAD DATA INFILE 'db_xx_#####_import/xxx.csv' INTO TABLE xxx

FIELDS TERMINATED BY ',' ENCLOSED BY '' Error Code: 29. File

'G:\ProgramData\MySQL\MySQL Server 8.0\Data\dxxx.csv'

not found (OS errno 2 - No such file or directory) 0.000 sec

20:25:22 CREATE TABLE IF NOT EXISTS (xxDetail_IDBIGINT NOT NULL,xx_Master_ID` BIGINT NOT NULL,

xxIE_ID BIGINT NULL, xxFU_ID BIGINT NULL, MCODE VARCHAR(32) CHARACTER SET 'utf8mb4' NULL, Requested DATETIME(6) NULL,

Scheduled DATETIME(6) NULL, Executed DATETIME(6) NULL, BodyPart VARCHAR(32) CHARACTER SET 'utf8mb4' NULL,

E_PDesc VARCHAR(0) CHARACTER SET 'utf8mb4' NULL, S_Heading VARCHAR(0) CHARACTER SET 'utf8mb4' NULL, E_Heading VARCHAR(0) CHARACTER SET 'utf8mb4' NULL,

SignPath VARCHAR(150) CHARACTER SET 'utf8mb4' NULL, PRIMARY KEY (xxDetail_ID), CONSTRAINT FK_tblxxsDetail_tblxxIE

FOREIGN KEY (xxIE_ID) REFERENCES xx.xx(xx_ID`)

ON DELETE NO ACTION ON UPDATE NO ACTION) 0 row(s) affected,

3 warning(s): 1681 Integer display width is deprecated and will be removed in a future release.

1681 Integer display width is deprecated and will be removed in a future release. 1681 Integer display width is

deprecated and will be removed in a future release. 0.063 sec

20:36:42 LOAD DATA INFILE 'xxx.csv' INTO TABLE xxx FIELDS TERMINATED BY ',' ENCLOSED BY ''

Error Code: 1366. Incorrect integer value: '' for column 'xx_ID' at row 1 0.000 sec

20:52:28 LOAD DATA INFILE 'xxx.csv' INTO TABLE tblxxsDetail FIELDS TERMINATED BY ',' ENCLOSED BY ''

Error Code: 1300. Invalid utf8mb4 character string: ' and the xx has had conservative care with several ' 0.000 sec

20:54:41 SET sql_mode = "" SET NAMES utf8mb4 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your

MySQL server version for the right syntax to use near 'SET NAMES utf8mb4' at line 2 0.000 sec

21:26:01 LOAD DATA INFILE 'xxxxloc.csv' INTO TABLE tblLocations FIELDS TERMINATED BY ',' ENCLOSED BY ''

Error Code: 1300. Invalid utf8mb4 character string: 0.000 sec

frederico_fonseca SSCoach Points: 16026 More actions · Answer 2

you now really on to the realm of MYSQL forums - while some here may be able to help this is not the best forum for it.

regarding the integer display warning - see https://stackoverflow.com/questions/58938358/mysql-warning-1681-integer-display-width-is-deprecated

it is just a warning and can be ignored.

the other is an error and there may be ways of fixing it - it would require a change on the load command for sure. have a look at https://www.percona.com/blog/2016/07/05/mysql-5-7-utf8mb4-and-the-load-data-infile/ and https://stackoverflow.com/questions/43408012/mysql-invalid-utf8-character-string-when-importing-csv-table

solution will depend on what your input data charset is.

with regards to converting the scripts to be correct its not as complicated as it seems as the change should be the same in all of the load commands.

And if the table definitions/selects themselves need to be changed (for example if you have sysname columns) then again this is not that hard to do within a single script.

This reply was modified 1 year, 6 months ago by frederico_fonseca.

Saran Right there with Babe Points: 753 More actions · Answer 3

Thank you for your help. I have tested "LOAD DATA INFILE" from csv.

The main problem is CSV itself generating wrong data.

Ex: if I have columns like this, value is loaded differently in each column. Some of CreatedDate value is loade into CreatedBy etc.

Location_ID -bigint

Location -nvarchar

SetAsDefault -bit

CreatedBy -nvarchar

CreatedDate -datetime

Just noticed it is because of "," ex: - Irvington, NJ Office

This reply was modified 1 year, 6 months ago by Saran.

frederico_fonseca SSCoach Points: 16026 More actions · Answer 4

yes - that is one of the pains of using the files - on the cases where you have values on your tables with the delimiter you use you need to enclose those fields with double quotes - and if there is a double quote within the contents it needs to be escaped - similar for the delimiter itself I think. (not enough knowledge here as I don't use mysql)

the db to db option (the other option on the wizard) is likely to bypass this issue - but the query it generates may still need to be changed in some cases.