Bug #96744 Error on migrating NUMERIC from MSSQL
Submitted: 4 Sep 2019 11:06 Modified: 19 Nov 2019 21:42
Reporter: Jonathan Borges Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Migration Severity:S3 (Non-critical)
Version:8.0.17 OS:Windows (Windows 10)
Assigned to: CPU Architecture:x86 (64bit)
Tags: migration, MSSQL, numeric

[4 Sep 2019 11:06] Jonathan Borges
Description:
Error on migrating NUMERIC from MSSQL.

Data type migration is correct.
MSSQL: NUMERIC(5,1)
MySQL: DECIMAL(5,1)

Copy data type correspondence. (from debug log).
MSSQL: numeric  (type=SQL_NUMERIC, len=5)
MySQL: MYSQL_TYPE_STRING

Actual data.
MSSQL: 104.64

Error (from log):

07:56:03 [INF][      copytable]: Statement execution failed: Data truncated for column 'vl' at row 1:

INSERT INTO `test1`.`test` (`vl`) VALUES ('104.6\0')

ERROR: `test1`.`test`:Inserting Data: Data truncated for column 'vl' at row 1
ERROR: `test1`.`test`:Failed copying 1 rows.

This bug can be related with another: https://bugs.mysql.com/bug.php?id=96230

How to repeat:
Migrate with MySQL Workbench, use NUMERIC for data type.

You can:

1) Create database and table on SQLServer:
create database test1;
use test1;
create table test (vl numeric(5,2));
insert into test values (104.64);

2) Migrate with Mysql Workbench

Suggested fix:
1. Correct migration data type correspondence.
2. Verify and fix, convertion from SQL_NUMERIC to MYSQL_TYPE_STRING in copytable.cpp.
[4 Sep 2019 13:26] MySQL Verification Team
Thank you for the bug report.
[19 Nov 2019 15:57] Miguel Tadeu Mota
Posted by developer:
 
commit id: 0445a619c6021a5441a9a1bc686cb0cf82072185
[19 Nov 2019 21:42] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.19 release, and here's the changelog entry:

An original SQL Server record (for example, 1020.6) was copied
inaccurately to the corresponding MySQL column (1020.0) when migrated
using the DECIMAL data type. Similarly, data migrated using the NUMERIC
type generated a type-conversion error. Now, both decimal and numeric
values are stored as DECIMAL types to fix the resolution of the decimal
value or to fix the type conversion.

Thank you for the bug report.