Bug #46857 select enum column via mssql linked server, expected data length error
Submitted: 21 Aug 2009 14:18 Modified: 2 Oct 2009 22:39
Reporter: Brian Gold Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:Connector 5.1.6 x64, server: 5.0.51a-15 OS:Windows (Server 2003)
Assigned to: Assigned Account CPU Architecture:Any
Tags: enum, ODBC

[21 Aug 2009 14:18] Brian Gold
Description:
I'm using the mysql odbc connector to link to sql server 2005. when i run a select statement that includes an enum column, I get the following error:

"OLE DB provider 'MSDASQL' for linked server 'mysql_dash_dev' returned data that does not match expected data length for column '[mysql_dash_dev]...[login].sort'. The (maximum) expected data length is 10, while the returned data length is 8."

#mysql table
CREATE TABLE login
(
	id BIGINT AUTO_INCREMENT KEY
,	sort enum('name', 'color')
)
COLLATE cp1250_general_ci
ENGINE = INNODB;

#sql command (via mysql ODBC linked server)
insert into mysql_dash_dev...login (sort) values ('name')
insert into mysql_dash_dev...login (sort) values ('color')
insert into mysql_dash_dev...login (sort) values ('name')
insert into mysql_dash_dev...login (sort) values ('color')

How to repeat:
1) create mysql table with enum column
2) install mysql/ODBC connector 5.1
3) install sql server 2005 (express edition should work fine) - note, i'm not sure if this
issue is confined to sql server 2005...
3) set up an ODBC connection for your mysql instance with the flags below:
 Flags 1: allow big result sets, use compression
 Flags 2: dont cache results of forward-only cursors
 Flags 3: read options from my.cnf, allow multiple statements

4) run the following script to create a linked server between sql server 2005 and your
mysql instance (filling in your odbc name on line 2)

declare @odbc nvarchar(50)
set @odbc = 'mysql' --your ODBC name here

-- drop and recreate, if already present
if exists (select * from sys.servers where name = @odbc)
exec master.dbo.sp_dropserver @server = @odbc

EXEC master.dbo.sp_addlinkedserver
  @server = @odbc
,	@srvproduct = @odbc
,	@provider = N'MSDASQL'
,	@datasrc = '@odbc

--this sets the sql server options for the linked server. none of these options has any
effect on the outcome
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'collation compatible',
@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'data access', @optvalue
= N'true'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'dist', @optvalue =
N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'pub', @optvalue =
N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'rpc', @optvalue =
N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'rpc out', @optvalue =
N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'sub', @optvalue =
N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'lazy schema
validation', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'query timeout',
@optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'use remote collation',
@optvalue = N'true'

5) run the sql statements in the description on this setup, to reproduce. i've reproduced
this on 2 separate machines, one running 32bit windows and sql server & one running 64bit windows and sql server
[21 Aug 2009 14:56] Brian Gold
Just a quick addition.

I tried using a different column name, as I thought the name I had been using (sort) might be causing the issue. I got the same result when calling the column simply 'test_column'.

Also, I am using the latest dev snapshot of the ODBC Connector (5.1.6) due to the following bug: http://bugs.mysql.com/bug.php?id=46162
[24 Aug 2009 6:44] Tonci Grgin
Hi Brian and thanks for your report.

I guess this is as it should be as MS does not recognize ENUM type... Can you please convert ENUM column to CHAR in SELECT and retest (CAST(Enum_Col AS CHAR) AS Enum_Col).
[24 Aug 2009 18:31] Brian Gold
Hi Tonci. Thanks for the quick response.

I tried your suggestion, but I'm unfortunately getting the same result.

select statement:
select (CAST(sort AS CHAR)) as 'sort' from mysql_dash_dev...login

result:
SQL Server Database Error: OLE DB provider 'MSDASQL' for linked server 'mysql_dash_dev' returned data that does not match expected data length for column '[mysql_dash_dev]...[login].sort'. The (maximum) expected data length is 10, while the returned data length is 8.
[16 Sep 2009 14:58] Brian Gold
Sorry to be a bother, just wondering if anyone has had any luck in reproducing this issue or has any suggestions for a resolution, aside from the obvious: not using enums.
[17 Sep 2009 5:48] Tonci Grgin
Brian, I apologize but setting up all that's needed to reproduce this is not trivial... Lot's of 3rd party SW involved.
Will see what I can do.
[1 Oct 2009 7:42] Tonci Grgin
I can repeat the reported problem. Will try tracing what actually happens now.
[1 Oct 2009 8:14] Tonci Grgin
So I've set up WinXP 32 bit with MyODBC 5.1.5GA running against remote MySQL server 5.1.32x64 on OpenSolaris host. SQLEXPRESS is "SQL Server 9.0.1399".
MySQL server is started with --default-character-set=utf8 (though "latin1" also fails).

My only change was:
EXEC sp_addlinkedserver 'DNSName', 'MySQL', 'MSDASQL', Null, Null,'Driver={MySQL ODBC 5.1 Driver};DB=test;SERVER=***;uid=***;pwd=***'

I see that query (SELECT * FROM OPENQUERY(DNSName, 'select * from test.bug46857')) reached the MySQL server but can't find anything useful in ODBC trace (which will be attached shortly).

Verified as described but it's unclear to me where and why error happens.

Irrelevant:
  To my opinion, WinOS flavor is irrelevant, MySQL server can be any of 5.x line, c/ODBC used to reproduce can be current released product, 32-64 bit is irrelevant.
[1 Oct 2009 8:14] Tonci Grgin
ODBC DM trace

Attachment: SQL.rar (application/octet-string, text), 751 bytes.

[2 Oct 2009 22:22] Greg Greene
On MSSQL, as a workaround, the following TSQL can be used (before the Select statement to pull data from MySQL):

DBCC TRACEON(8765)

This allows the use of variable length data, from ODBC driver. It should resolve this error.
[2 Oct 2009 22:39] Brian Gold
Just tried this and it worked perfectly. Thanks so much for your help!
[2 Oct 2009 22:52] Greg Greene
No problem.

Now, I'm not sure this workaround should be concluded as a final solution. It would be great to see a smoother translation from MySQL enum to MSSQL varchar / char. Without the need for a DBCC call.
[2 Dec 2010 13:24] Thilo Langbein
Is this not fix until now?
[9 Dec 2010 11:32] Bogdan Degtyariov
Running a simple select query

SELECT * FROM MYSQL_DASH_DEV...login

results in an error:

Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "MYSQL_DASH_DEV".

(Verified with MySQL ODBC Driver 5.1.8)
[8 Jun 2011 14:52] Marcus Lashley
Found it!

Seems to be related to an ODBC error.

Open up the ODBC connector for your connection and select the details button.

I changed settings so every tick box on every tab is clear. 

I cleared the exclude table name on flag3 tab, log queries on debug tab, and under misc options tab I cleared the Character Set. 

It worked instantly! Unfortunately for the purpose of reporting what caused this I turned each option back on and it continued to work!

However what led me down this path was that I have two identicle mySQl servers, with different data in, which I am replicating down to a SQL box. the first server has worked properly for a long time and I have only just replicated the second. These settings had been cleared on my first connection but not the second, so definately related.
[22 Sep 2011 14:09] Garu Azazello
I can confirm Marcus Lashley method to solve the bug with a little difference.

I cleared every option and applied the changes, but the query against an enum field wouldn't work.

I turned back the options ("Allow big results", "Use compression", "Treat BIGINT as INT", "Enable safe options") I had and the query worked.
Trying different queries after the first success don't raise any errors.

I use SQL Server 2005 on Win Server 2008 R2, MySQL ODBC Connector 5.1, MySQL 5.1.41 on Ubuntu 10.04.
[26 Sep 2011 7:37] Garu Azazello
UPDATE on my previous comment.
I came back to work today just to find the problem appeared again.
Re-doing the procedure described before doesn't give any result.