MySQL Bugs: #63386: MSSQL linked server connected to MySQL 5.1.9 driver causes hard crash

Bug #63386	MSSQL linked server connected to MySQL 5.1.9 driver causes hard crash
Submitted:	22 Nov 2011 16:44	Modified:	19 Jul 2013 19:08
Reporter:	jorn de R	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.9	OS:	Windows (2008R2 Standard)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	5.1.9, x64

Description:
64-bit ODBC mySql driver causes MS SQL server to crash.

This started happening since SQL Server 2008 R2 SP1.

How to repeat:

Steps to reproduce :

1. create a 64bit ODBC connection with the 5.1.9 driver to a MYSQL machine
2. Go to MSSQL Server 2008R2 SP1 
3. Create a linked server to the ODBC dsn
4. test it.

result is the crashing of MSSQLservr.exe

dump :

Faulting application name: sqlservr.exe, version: 2009.100.2789.0, time stamp: 0x4e842163
Faulting module name: myodbc5.dll, version: 5.1.9.0, time stamp: 0x4e8b6a8e
Exception code: 0xc0000005
Fault offset: 0x00000000000247b3
Faulting process id: 0x13bc
Faulting application start time: 0x01cca932fc1ec6ac
Faulting application path: F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Faulting module path: C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll
Report Id: 3d15fb12-1526-11e1-ac4e-1cc1deeb54cf

Suggested fix:
--

same problem with SQL 2008 (10.0.5500) 32bit using oledb

sorry las post not complete.
the problem occurs on 32bit and sql 2008 standard (not R2) also
using the 5.1.9 32bit drivers.

Message
Faulting application sqlservr.exe, version 2007.100.5500.0, time stamp 0x4e7b605c, faulting module myodbc5.dll, version 5.1.9.0, time stamp 0x4e8b6aa5, exception code 0xc0000005, fault offset 0x0001fdde,
process id 0xd48, application start time 0x01ccaf755487d468.

using the same DSN with excel works fine. but using the Microsoft Oledb MSDASQL it crash

I tried to repeat bug in MSSQL 2005 64-bit and it did not crash.
Unfortunately, I do not have 2008 version.

Can you enable the ODBC tracing in windows ODBC administrator and then try to crash MSSQL 2008?

Hi,

The logs stay empty. Usually I see SQL code pop up in the logs. With the crash it stays blank.

Are you aware that you can get SQL express 2008 64 bit for free? There are also trail versions of SQL server standard/enterprise 2008 downloadable. 

Hope to here from you guys..

I installed MSSQL 2008 Express 64-bit version and tried getting the databases, tables and table data from the linked MySQL Server. It did not crash.
The screenshot demonstrates the table list, the query and the data returned from the table.

Screenshot

Attachment: mssql2008_screen.jpg (image/jpeg, text), 87.74 KiB.

Maybe you should check the provider settings 
(MSDASQL)
and make sure the following options are enabled:

 - Nested queries
 - Level zero only
 - Allow inprocess
 - Supports 'Like' Operator

Bogdan Degtyariov, what are the settings you have set in the ODBC connection and also for the SQL linked server? I have been trying to get this working and keep crashing my sql server, so any help would be greatly appreciated!

Thanks!!

Rob,

ODBC data source options are set by default (none of flags are selected).
Set only:

 - Data Source Name
 - TCP/IP Server
 - User
 - Password
 - Database

In MS SQL Server I left the default configuration as well, except as already noted in my previous message, the following options are selected in 
"Server Objects"/"Linked Servers"/"Providers"/"MSDASQL":

 - Nested queries
 - Level zero only
 - Allow inprocess
 - Supports 'Like' Operator

It just came to my mind, in earlier versions of MySQL ODBC driver it might crash when using UTF-16 or UTF-32 character sets ("Details >>"/"Connection"/"Character Set" combobox or "CHARSET=..." in the connection string).

What charset did you use for your connection?

Had the same problem. Checking "Interactive Client" in the ODBC setttings, and then selecting "UTF-8" seem to have solved it.

Interactive client option does not change anything in the client behavior.
It just tells the server to use interactive_timeout instead of wait_timeout when connection has been idle for longer than that timeout. I do not see how this could cause the crash.

It seems that setting utf-8 character set resolves the problem.
So, it basically depends on the character set you use in MSSQL server.

@Bogdan .. you are not quite correct here: "It just tells the server to use interactive_timeout instead of wait_timeout when connection has been idle for longer than ... "

It is *ALWAYS* (SESSION) wait_timeout that controls this. The only thing interactive_timeout does is it will *initialize* SESSION wait_timeout to the value of GLOBAL interactive_timeout. 

MySQL docs are clear about this "On thread startup, the session wait_timeout value is initialized from the global  wait_timeout value or from the global interactive_timeout value, depending on the type of  client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect())."

In other words: if user after connection executes "SET [SESSION] wait_timeout = ... " interactive_timeout setting has no effect at all.  It is only used for initialization of wait_timeout SESSION variable.

I bloged this years ago: http://www.webyog.com/blog/2009/09/02/“mysql-server-has-gone-away”-part-2-session-time...
(because even MySQL'ers confuse this frequently)

Ther is also a bug report: http://bugs.mysql.com/bug.php?id=45689

Peter
(not a MySQL person)

Thank you Peter for your comment.
Your point is absolutely correct.

In my previous message I just wanted to say that those two timeouts should not have an immediate effect and certainly do not interfere during the connecting time.

Similar problem here with SQL Server Ecpress Edition with advanced services 10.50.2500.0 and ODBC Connector 5.1.10.

Solved it by using ODBC Connector 5.1.8.

Has anybody tried just setting CHARSET=utf8 and nothing else in ODBC DSN?

We cannot repeat the problem neither with 5.1.9 not with 5.1.10. 
Also, we are willing to resolve it but so far there was no instructions on how to do it and succeed....

Hello again,

I have 2 machines :

1 32-bit Windows 2003 / SQL 10.50.2789 + ODBC 5.1 driver
1 64-bit Windows 2008 / SQL 10.50.2789 + x64 ODBC 5.1 driver

I tried the following things on both :

Create a linked server :

DataSource = name of the odbc
Catalog = random db on mysql 
Security = Be made withou using a security context. (user/password is in ODBC)

---

Create an ODBC :

filled in user and password. 
Database : same one as the catalog in the linked server
In Connection Tab :

Interactive client = tried it on and off.
Character Set = utf8 in the drop box

------------

Results :

SQL 2008R2 64-bit -> SQL server gets killed.
SQL 2008R2 32-bit -> Linked server suffers a catastrophic failure.. 

-----------

Logs :

SQL2008R2 Sql Server crashes.. this gets logged in the eventviewer.. the 32bit version doesn't crash : nothing in the eventviewer..

1.

Faulting application name: sqlservr.exe, version: 2009.100.2789.0, time stamp: 0x4e842163
Faulting module name: myodbc5.dll, version: 5.1.9.0, time stamp: 0x4e8b6a8e
Exception code: 0xc0000005
Fault offset: 0x00000000000247b3
Faulting process id: 0x6dc
Faulting application start time: 0x01cce588c87c704b
Faulting application path: F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe
Faulting module path: C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll
Report Id: dcafd638-594a-11e1-909f-1cc1deeb54cf

2. (this is the big one)

The MSSQLSERVER service terminated unexpectedly.

If there's anything you need Bogdan, let my know.. I could make some screendumps if that is helpful..

Jorn, it looks that the our only option would be generating the minidump.
Note that in Win7/Win2008 the minidumps are produced by Windows Error Reporting (WER) rather than DrWatson. And it is not enabled by default. To enable it you need to edit registry as follows:

(full article is here: http://blogs.technet.com/b/yongrhee/archive/2010/12/29/drwtsn32-on-windows-vista-windows-s...)
-----------------------------------------------------------------------------
WARNING: If you use Registry Editor incorrectly, you may cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error Reporting\
Disabled (dword) 0 (hex)
LoggingDisabled (dword) 0 (hex)
ForceQueue (dword) 1 (hex)
DisableQueue (dword) 0 (hex)
MaxQueueCount (dword) 32 (he x)
    Note:  32 hex = 50 dec which is the default.

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\Windows Error Reporting
LocalDumps (Key)
DumpFolder (Expandable String Value) %LocalAppData%\CrashDumps
    Note:  System services is %WINDIR%\System32\Config\SystemProfile.
    For Network and Local Services, the folder is %WINDIR%\ServiceProfiles.

DumpType (dword) 1 (hex)
    Note:  Where 1 is setting for a "Mini Dump".
-----------------------------------------------------------------------------

The resulting file is going to be larger than 512kb even for minidump, so please pack it using zip and upload to ftp://ftp.oracle.com/support/incoming/.
In order to make it easier to find this file please name it using the bug report number such as bug_63386_minidump.zip

Software :

Windows 2008 X64 / SQL 2008R2 

Steps taken :

1. I made the changes to the registry as described above.
2. create a new ODBC to a ubuntu / Mysql 5.1 machine. 
3. filled in the DB / user / password . selected "UTF8" & "interactive Client".
4. in SQL server -> new linked server. Only filled in "Product name" & "datasource"
5. Went to the security tab : Selected "be made without a security context"
6. clicked ok.
7. used an OPENQUERY to access the Linked server.

Be amazed that everything works as it should.

Tried the "test connection" button on ODBC & linked server => everything fine.

Even switched off  "UTF8" & "interactive Client" -> no problem whatsover when running the open query. (even when resetting the connection.)

I tried the same thing on a 32bit machine.. It doesn't crash.. but it doesn't work. I just get the usual hourglass when waiting on something..

Don't think logging on that machine has use, because the SQLServer Service doesn't shutdown.

Between my last try and todays, no patches have been installed. And the machine hasn't been rebooted.

Do I get it right that the problem with 64-bit ODBC driver has just disappeared?
If you did not install any updates and it has gone away the problem could be outside the driver. For instance, the logical error in the filesystem or something similar...

MYSQL Linked Server Crashes with MSSQL 2008 R2 64bit Server. This issue remains unresolved! 

This was tested on a: 

x64 Windows 2008R2 Server (Virtual)
x64 MSSQL Server 2008R2 Standard 10.50.1600
x64 MYSQL 5.5.22 
MySQL ODBC 5.1 Driver V 5.01.10.00

As Jorn de R states the MSSQL system does crash when attempting to test a newly created linked server. Though this appears only to occur if the linked server created has any of the additional settings provided for it (including provider, optional security and/or optional server options). A simplified linked server that relies on an existing ODBC DSN for its details appears to work correctly. Any linked server whether added by tsql or mgmt studio that includes the other details leads to an error. I suspect that anything, setting or otherwise that doesn't match the details of the underlying ODBC DSN causes this bug - fails to override.

So the work around for now appears to be do just as he did when he was prepping to send you a minidump; ONLY provide the Name, Provider, Product Name and reference to Data Source.

Luckily for me the last 2 hours of testing were on a non production mssql server. I hope this saves many others from similar frustrations and time wasting.

Sean, thanks for your help. Finally, I was able to repeat the SQL Server 2008 crash. I must confess, this really hard...

Now we need to check what can be done to fix it.

The driver crashes because mssql process is trying to get the current catalog (SQL_ATTR_CURRENT_CATALOG 109) before the connection is established:

myodbc5.dll!SQLGetConnectAttrW(void * hdbc=0x00000000096a8bb0, long attribute=109, void * value=0x000000000e7658f0, long value_max=514, long * value_len=0x0000000000000000)

MSDN says this attribute can be get/set either before or after the connection is made:

http://msdn.microsoft.com/en-us/library/ms713605%28v=vs.85%29.aspx

So, the driver has to consider such possibility as well and not to try using non-initialized charset pointer:

unicode.c, line 423-424:

    wvalue= sqlchar_as_sqlwchar(dbc->cxn_charset_info, char_value,
                                &len, &errors);

dbc->cxn_charset_info is NULL and the conversion fails with the crash

MSSQL server does not crash when SQLGetConnectAttr()/SQLGetConnectAttrW() functions return SQL_ERROR upon trying to get the catalog before the connection is established.

The patch and the test case are coming soon.

patch for the bug

Attachment: bug63386.diff (application/octet-stream, text), 704 bytes.

I don't know how to make a C test case for this bug because MSSQL is calling ODBC functions in a very unusual way: 

to repeat the problem SQLGetConnectAttr() has to be called BEFORE SQLDriverConnect(), but the driver needs be loaded at that time.

So, just calling SQLGetConnectAttr() before SQLDriverConnect() does not help. Somehow SQLGetConnectAttr() should be called internally in the driver manager when it resolves the DSN, loads the driver and does the primary diagnostics before the actual driver connect is done.

That is why I only posted the code patch without the test case.

mysql-connector-odbc-5.1.11-winx64 still have this problem

Win 2008 R2 Ent SP1, SQL 2008 R2 Ent (v 10.50.2500).
Linked server to mysql odbc is successfully created, but any query on it hangs up client (mssql server management studio, same version), but sql server continues to work.

5.1.08 connector works fine.

PS Excuse me if i'm wrong with bug number

Hello,
I'm facig this error with ODBC Connector 5.2.2. MSSQL Server just crashes.

Regards

pushed to revision 1147 in Connector/ODBC 5.2

pushed to revision 1098 in Connector/ODBC 5.1

Added an entry to the Connector/ODBC 5.2.6 and 5.1.13 changelogs:

When trying to create a linked server in Micrsoft SQL Server 2008 to a MySQL server set up with Connector/ODBC as a DSN, the Microsoft SQL Server (if it is a 64-bit version) crashed or the linked server suffered a catastrophic failure (if a 32-bit version of the Microsoft SQL Server is used).