MySQL Forums
Forum List  »  MySQL Workbench

Wrong query generated while data migration from Sql Server to MySql for Decimal field
Posted by: Arsh Ankur
Date: March 30, 2017 06:30AM

Hi Everyone,
I needed to migrate my database to mysql from SqlServer.
So i had to do a data migration.
For this, i preferred the Database Migration wizard of MySql Workbench.
I have the MySql Workbench 6.3.9 version build 10690321 CE (64 bits).

I have a field in a table with data type decimal(5,2).
I configured MySql with the relative data types of Sql Server. Used decimal(5,2) for MySql column. Tried data transfer, failed, then changed to decimal(10,4), again tried and failed.

When the error occured, i took a look in the logs, i found that the insert query being prepared by the utility for a decimal value 109.09 had put the same value in the query as '109.0\0' and when this got executed, it gave the following error: Inserting Data: Incorrect decimal value: '109.0\x00' for column 'columnName' at row 28


My question: What is the cause that has placed \0 instead of 9 after .0 in the value 109.09, where in both MySql and SqlServer the column data type is decimal.

Its not that only 9 was changed to \0. Before this i had a negative value in some other column as -16.67. For this value i got the same error where query had put it as '-16.6\0'

I thought it might be because of -ve value. So to test that i made it positive and proceeded ahead and i got success, but then i got same error with a positive value in different column with which i have started my post.

Supportice Info for the question:
I have created a database where i have used
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

because i had to store special chars as well in the text based columns.
Now to check if, dataset or collate was responsible for this, i created the database without them and tried to trnasfer a single record that had the problem. But it failed with every different permutation and combination of configuring the charset and collate.
Even with an unsigned decimal configuration to the column failed.

Nothing has worked yet.

I think i was clear in expressing my problem, if i wasn't please ask the info you require to troubleshoot it for me.

Please provide a solution to get rid of this problem.

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Wrong query generated while data migration from Sql Server to MySql for Decimal field
248
March 30, 2017 06:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.