MySQL Bugs: #6452: data overflow in varchar/char fields using myodbc/dts to mysql

Bug #6452	data overflow in varchar/char fields using myodbc/dts to mysql
Submitted:	5 Nov 2004 5:24	Modified:	8 Dec 2005 17:09
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.07	OS:	Windows (windows 2k/sql server 2k)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
just downloaded 3.51.07...still unable to convert varchar and char cols  with myodbc/dts. creates table ok, but generates data overflow on insert. integers and dates seem ok.

How to repeat:
from sql server, export, dts, source sql, dest mysql via myodbc, copy table, edit sql to varchar(50) and char(1), run...creates table but fails on insert. note that despite the lengths in the sql set to 50 and 1, they both show as -1 in the menu.

fails even when lengths are displayed properly in dts. even tried to use varchar(255) and char(10) even though i only want 50 and 1. still fails what gives?

Thanks for the bug report.  I was able to reproduce it.

What is the status of this bug? I have the exact same problem with the same systems.  

I get the following error: insert error, column 7 ('column_name')(dbtype_str), status 6, data overflow

I can create tables and transfer INT fields ok.

I have the exact same problem and it's holding me up. I'm trying to migrate from SQLserver to mySQL. I'm using DTS along with mySQL Connector/ODBC 3.51.

The tables get transfered ok but not the data (varchar and char don't work). I get this error in Microsoft DTS:

Copy data from myColumnName to 'mydatabase'.'myColumnName' Step.

Error at destination for row #1. Errors encountered so far in this task: 1.
  Insert error, column 5 ('mycolumnname', DBTYPE_STR), status 6: Data overflow.
  etc.
  Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

It would be really cool to get it working...

Thanks! And good job on mySQL, it's great!!!

It is still a problem.  I disagree with the severity rating for this bug.  I think it should be given a higher priority.

I absolutely agree with Jim Starr. The severity shoul be higher! 
Its very tedious to make migrations with this bug!

Please give it a higher severity.

Thanks and regards

Fredy

This really needs to be fixed, can't you post a workaround, how do i get around this? it's taken days out of my life to get this fixed. Peter mate, at least post a workoaround, i need help, this is sooo annoying, it's been listed since 2004!

I'd also like to comment that this problem causes replication from sqlserver to mysql to die as well. please can you make this more critical or something.

FOUND A SOLUTION - in the configure DSN window under the advanced tab, check "don't optimize column width."

Where is the configure DSN window? In the ODBC setup?

If your using windows.

Go to Control Panel
Admin Tools
Data Sources (ODBC)
locate your DSN
Hit configure
Go to the Advanced tab
and check the Don't optimize column width

Once I did this it started working.

Woohoo! Thanks Christopher, I've confirmed the fix in my own migration.

I found the workaround to be extremely slow (over 15 min for a small 13.5mb table)
http://www.ucl.ac.uk/is/mysql/access/index.html

I've have been more happy with setting up a linked table in Access to the MS SQL table and then exporting that to MySQL via the MyODBC datasource.  The migration of the same table as above took 20 seconds by this method.

Just a thought in case it's useful to anyone else.

I've had gathered some more experience on this which might be helpful to other users:

- The MS SQL DTS can be made to work by setting up the ODBC DSN you use for your MySQL database to either "Don't optimize column widths" or use the "Safe" setting under the "Advanced" tab -> "Flags 1" tab.  Both worked for me.

- The slow usage I reported before can be "fixed" as well.  I found that the table in question was set up as an "InnoDB" table.  When I cleared the table and changed it to "MyISAM" I found that the MS SQL DTS ran in the same time or faster than by the MS Access method.  (Curious thing is that the table createb by Access was actually InnoDB so don't know what made the difference there but maybe it doesn't implement transactions properly.)

- I've since changed to "default-storage-engine=MyISAM" in my "my.ini" file and all my DTSs run perfectly and efficiently.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

This bug has been fixed in v.3.51.12