MySQL Bugs: #46857: select enum column via mssql linked server, expected data length error

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

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

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

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).

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.

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.

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.

I can repeat the reported problem. Will try tracing what actually happens now.

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.

ODBC DM trace

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

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.

Just tried this and it worked perfectly. Thanks so much for your help!

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.

Is this not fix until now?

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)

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.

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.

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.