MySQL Bugs: #54500: DTS table insert fails to load correct data

Bug #54500	DTS table insert fails to load correct data - loads null or 0
Submitted:	14 Jun 2010 21:05	Modified:	29 May 2013 6:03
Reporter:	Ben Waller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	DTS Insert null 0

Description:
I am using DTS to load data from a SQL 2000 db to a new MySQL db.  When I load a MySQL table from a mapped Not Null field in SQL 2000 the data is populated in MySQL as null or 0.  I've tried dropping an recreating the table to try to fix the problem but this does not work.  I have tried restarting MySQL.  I have successfully loaded the data to a text file with DTS.  Interestingly enough, I added a "dummy" column to the MySQL table and made it the first column in the table.  So this moved the problem field from position one to two in the table.  I ran the DTS and the data successfully loaded in column two!! - Except however the new column, column 1, did not load to the table again.  Why is this first column in the table not accepting mapped data?  BUG!!!

I am using MySQL Workbench to manage the tables.

Also to note:  I am loading this same data to other tables and it works fine.  

How to repeat:
create a table in MySQL
create dts to move table fields from SQL Server to MySQL
Run DTS
depending on field data type, if type is int was loading all zero's, of the type was varchar was loading NULLs

Please, send the exact CREATE TABLE for the table in SQL Server and MySQL. What exact version of MySQL server are you working with?

We are using MySQL Version 5.1.41.

The Table Create Statement is:
CREATE TABLE `coll_object_package` (
  `objectid` int(11) NOT NULL,
  `packageid` int(11) NOT NULL,
  `sortorder` int(11) NOT NULL,
  `objectnumber` varchar(45) DEFAULT NULL,
  `notes` mediumtext,
  PRIMARY KEY (`objectid`,`packageid`,`sortorder`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have written a DTS package that loads data from SQL Server 2000 to a new MySQL instance.  In the above table the objectid field is always populated with a 0.  The Insert will fail with the primary key constraint.  When I remove the primary key constraint, the data will load but the first column, objectid, will load all 0's.  When I run this same process and output to a text file, the correct data for objectid outputs to the text file.

Try two: If I move the objectnumber field to the first column of the table, it will load all data values as NULL.  When it is in it's original column position, position 4, it loads the actual data correctly.

Ironically, I am loading similar data to other tables.  I even recall loading data without a problem before to this table with DTS.  It seemed during a test run through of the DTS Insert some sort of db odd error started to occur.  I have had three developers in-house look at this problem and they are perplexed.

Thanks for your help.

This is the Table Create for the data SOURCE in SQL Server 2000:

CREATE TABLE [dbo].[ObjPkgList] (
	[ObjPkgListID] [int] IDENTITY (0, 1) NOT NULL ,
	[ObjectPackageID] [int] NOT NULL ,
	[ObjectID] [int] NOT NULL ,
	[ObjectNumber] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
	[OrderPos] [int] NOT NULL ,
	[LoginID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,
	[EnteredDate] [datetime] NOT NULL ,
	[Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
	[SysTimeStamp] [timestamp] NULL 
) ON [PRIMARY]
GO

I discovered a cause and a solution to this bug.  I would still register this as a MySQL bug, the database did not perform as expected.

Basically I was loading five SQL Server 2000 fields to an empty MySQL table.  The problem was that I was loading a field from SQL Server 2000 that was a varchar(2000) field to a MySQL mediumtext field.  From the MySQL documentation, mediumtext in MySQL is simply a larger capacity datatype of varchar(). For some reason MySQL did not like that I was matching up the varchar(2000) SQL Server 2000 field to the MySQL mediumtext field.  What fixed the problem was I changed the MySQL destination field data type from mediumtext to varchar(2000).  I would not expect that the value of the first column in a MySQL database populate only 0's if one of the other columns being loaded fails, and has this datatype "mismatch" or match up problem, not sure how to phrase this mismatch of the load...  Thanks.

Thank you for the feedback.

But what is DTS package and how exactly " For some reason MySQL did not like that I was
matching up the varchar(2000) SQL Server 2000 field to the MySQL mediumtext field.  "?

DTS is Data Transformation Services that is a data mapping tool used with MS SQL Server 2000.  With DTS you build what are know as packages.  I have created a package that I use to connect to my MS SQL 2000 db source and connect to my MySQL db destination.  Within this package I have mapped the db source and destination data table fields.  I am basically using DTS to automate the daily data synchronization between two databases.  The package can be scheduled in MS SQL Server to run daily etc...

When I had tried to map or "match" the field from SQL Server 2000 to MySQL in the DTS Package, the error/bug occurred.  See my prior comments for details of the data types and field mapping that caused the problem.  Thanks!

Ben, DTS is using the bridge to do the transformations. I presume, in your case, it is the ODBC one, right? Can you send us details on MySQL c/ODBC used and a description od DSN you created for this transformation please.

On types. MSSQL types are usually shorter than MySQL ones. MySQL varchar can be 32 times larger than what you use in your transformation thus there is no need for TEXT fields which are also too big for MSSQL (see "Limit column size to 32bit signed integer" option in c/ODBC).

So, I do not see a bug here but would like to see the answers to my questions and a ODBC (if used) trace generated for this transformation (see documentation for details on how to obtain ODBC trace on Windows).

You may also wish to "un-private" your first post so that the others might see what exactly are we talking about.

The connection to MySQL is being completed with the MySQL ODBC 5.1 Driver.  The DSN is a System DSN created in the ODBC Data Source Administrator of a Windows Server 2003 Standard Edition computer.

Regarding your comment on the differences in the data type sizes between MSSQL and MySQL, you state that MSSQL fields are typically shorter, if this is true then there should not be a bug as I reported.  I am only loading data from MSSQL to MySQL, so based on the claim that data field types in MSSQL are shorter in length, I should not have had a problem loading a shorter length field column in MSSQL to MySQL.  Basically, the problem I encountered with one MySQL field in a table and it's sizing, and this field being loaded with data that the data does not "fit into" correctly, causing ANOTHER field in the same table to have problems - should not happen.  This is a bug as far as I am concerned.

I still fail to see any evidence of MySQL server bug here. Looks like the problem is related either to DTS software used, or to Connector/ODBC version used. So, please, provide the information requested by Tonci previously if you are still interested in any progress with this report.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.