MySQL Bugs: #31413: Memo fields not working in MSSQL linked server

Bug #31413	Memo fields not working in MSSQL linked server
Submitted:	4 Oct 2007 20:57	Modified:	18 Jun 2008 12:36
Reporter:	Jim Breffni	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.20	OS:	Windows (XP SP2)
Assigned to:		CPU Architecture:	Any
Tags:	odbc longtext mssql linked server

Description:
Using 
SQL Server 2005 9.00.3054.00
MyODBC 3.51.20
MySQL 4.1.22

When I do a select * on a linked server from mssql to mySQL via odbc I get this error for any table with a longtext field in it:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

How to repeat:
Do a select * on a mySQL table containing a longtext field via odbc from sql server 2005 Management Studio.

Thank you for the bug report. How you process to connect with MySQL server
via MyODBC within SQL Server 2005 Management Studio.?. Thanks in advance.

Process for connecting to mySQL from SQL Server 2005 Management Studio:

1.  Create System DSN using Windows ODBC Data Source Administrator
2.  Add linked server to MSSQL:
EXEC sp_addlinkedserver 'yourNameForLinkedServer', 'MySQL', 'MSDASQL', Null, Null, 
  'Driver={MySQL ODBC 3.51 Driver};DB=yourdatabasename;SERVER=serverrunningmysql;uid=yourmysqllogin;pwd=yourmysqlpassword

3.  To issue a select:
SELECT * FROM OPENQUERY(yourNameForLinkedServer, 'select * from mySQLdatabase.mysqlTable')

Hi Jim. I think I see where the problem lies... Microsoft DB SW is unable to handle data types longer than 32bit. Anyway, I am not able to repeat your problem with 3.51.23 (don't remember the revision number) even though it does not have "Limit column size to signed 32-bit range" like 5.1 has. I presume this was worked around while working on FoxPro problems lately. This is in no case our bug but we do provide workaround.

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug31413 (Id int unsigned not null auto_increment primary ke
y, someverylt longtext);
Query OK, 0 rows affected (0.08 sec)

See attached image too.

MSSQL project

Attachment: Bug31413.JPG (image/jpeg, text), 75.97 KiB.

Hi,

Unfortunately this bug is still present. The selectg works well on an empty table, but it doesn't work at all when there is at least one row in the table... Is there any other workaround for this issue?

10q,
CG

The workaround I used was to change my longtext fields to text fields.

This was only possible because OUR data for the longtext fields can fit into a text field size.

Thank you Jim, you've saved me. I've made a test and it seems that it works indeed with TEXT and MEDIUMTEXT fields. Indeed the 2^32 limit gets us all... Looks like I'll have to 'downgrade' the LARGETEXT fields to MEDIUMTEXT.

Thanks once more,
CG

I am using Mysql 5 with sql server 2005 as a linked server with ODBC connector (you can download from here http://dev.mysql.com/downloads/connector/odbc/5.1.html ) on windows XP SP2. I got similiar longtext field issues, it's temporary fixed. 

Solution: 
1 - On Windows XP just go to Administrative Tools -> Data Source(ODBC) -> Select your ODBC connection which you have used for Linked Server. 
2- Click on "Details" button 
3- Click on "Flag 3" Tab
4- Checked the option "Limit Column Size to 32-bit range"

This will limit longtext field to 32-bit range so that your query will work.

that's it. Your query will work now without modifying the schema, as i was not in favor of modifying existing schema. 

Note: Please make sure that you are not loosing data, in my case the actual data was in range of 32-bit . So it worked for me.