| Bug #39965 | INSERT INTO OPENQUERY() fails on MySQL Connector 5.1.x | ||
|---|---|---|---|
| Submitted: | 9 Oct 2008 20:48 | Modified: | 23 Jul 2009 7:16 |
| Reporter: | Robert Freeland | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 5.1.5 | OS: | Windows (64-bit Server 2003) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ODBC Connector 5.1 "No database selected" | ||
[9 Oct 2008 20:51]
Jess Balint
Robert, Please attach an ODBC trace for the INSERT statement for both 3.51 and 5.1 drivers.
[9 Oct 2008 21:10]
MySQL Verification Team
Setting Feedback according last comment request. Please re-open when done.
[9 Oct 2008 21:55]
Robert Freeland
How exactly do I do that? When I click the "Start Tracing" button in the ODBC Data Source Administrator on the server, it doesn't create any logfile at all -- even if I select "Machine-wide tracing for all user identities". I even hacked into the registry as suggested in a Microsoft FAQ, and that didn't help either. Note that I'm not using a DSN -- I'm just using the driver directly.
[9 Oct 2008 23:13]
Jess Balint
Robert, You may have to restart your SQL Server instance for the option to take effect.
[10 Oct 2008 2:28]
Robert Freeland
Stopped and restarted SQL Server, but still no trace log. Also tried creating a DSN, but no trace log for it either. Maybe the tracing just doesn't work at all on the 64-bit version of Windows Server 2003? In any case, this is a VERY easy bug to reproduce, so just give a try on your end. Maybe you can get a trace there.
[10 Oct 2008 4:37]
Robert Freeland
After messing around with this for most of the day, I finally stumbled across a workaround. Simply recreate the MYSQL Linked Server using the following:
EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL', @provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1 Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;DATABASE=mydb;'
Note the "DATABASE=mydb;" parameter on the end. That seems to do the trick, though "Initial Catalog=mydb;" did not. Of course, this workaround only fixes the problem if you have a single MySQL database. Moreover, this wasn't required in ODBC Connector 3.51, and judging from the number of times this problem comes up on the blogs, it looks like the change is causing a lot of heartache for a lot of people. I still rate it "Serious".
If DATABASE isn't specified, the driver should use a default (e.g. - "mysql"). Queries that specify tables in full (with a database name) certainly shouldn't fail with this "No database selected" error.
[10 Oct 2008 8:36]
Tonci Grgin
I think we had an issue with "Initial Catalog" already, let me check.
[10 Oct 2008 8:44]
Tonci Grgin
Jess I need feedback from you. Following your last post in Bug#10238 (of which this is most likely a duplicate) and being that Bug#16653 is *closed* I think you might have missed something in SQL_ATTR_CURRENT_CATALOG implementation: options.c, ln. 279 /* If this is done before connect (I would say a function sequence but .NET IDE does this) then we store the value but it is quite likely that it will get replaced by DATABASE in a DSN or connect string. */
[10 Oct 2008 19:08]
Jess Balint
"Initial Catalog" is in no way related to MySQL code. "Database" (or "DB") is the only option to specify a database for a MySQL connection string.
[10 Oct 2008 19:13]
Tonci Grgin
Thanks Jess, I'll take it from here.
[10 Oct 2008 22:10]
Robert Freeland
FWIW, I now know where the proper documentation page is for MySQL ODBC connection strings: http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.... There are examples here... http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-without-dsn... ...but none for ODBC Connector v5.1.
[10 Apr 2009 11:13]
Tonci Grgin
Robert, options and connection string are interchangeable. As I have many many problems with MSSQL on my 2k8x64 box I'll ask Bogdan to take a look.
[20 Jul 2009 9:35]
Tonci Grgin
Robert, the difference in statements was so obvious that I missed it: >> The following statement gives a "No database selected" error w/ v5.1: INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo') SELECT * FROM foo2; >> The crazy thing is that this statement works without complaint: SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo'); In "INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo') SELECT * FROM foo2;" there is no database in "SELECT * FROM foo2;" part. Can you fix this in MSSQL console?
[20 Jul 2009 17:26]
Robert Freeland
Hi Tonci, In that example, I was logged into the correct database in SQL Server, so the SELECT on that side shouldn't need the database descriptor (e.g. -- "MSDB.dbo.foo2"). The database (and user) should use the default. - Robert
[23 Jul 2009 7:16]
Tonci Grgin
Robert, sorry. I should have closed this report right after Jess posted his comment: [10 Oct 2008 21:08] Jess Balint "Initial Catalog" is in no way related to MySQL code. "Database" (or "DB") is the only option to specify a database for a MySQL connection string. So, not only that "Database" (or "DB") does the "trick" (as you put it), it is mandatory for this to work at all.
Description: We have a stored procedure on SQL Server 2005 that moves data from MySQL to SQL Server, modifies it, and then moves it back again. This procedure worked fine with MySQL ODBC Connector v3.51 on 32-bit Windows Server 2003, but it fails with MySQL ODBC Connector v5.1.5 on 64-bit Windows Server 2003. (This is a clean build, without any prior versions installed.) Our Linked Server on SQL Server is created with the following statement: EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL', @provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1 Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;Initial Catalog=mydb;' (Then you have to enable "RPC Out" via the SQL Server Management Studio Express UI for the EXEC examples below to work.) The following statement gives a "No database selected" error w/ v5.1: INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo') SELECT * FROM foo2; The crazy thing is that this statement works without complaint: SELECT * FROM OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo'); Thus, the problem seems to exist with INSERTS through the OPENQUERY, rather than SELECTs. The userID/login are the same that we were using successfully with these same statements on Connector 3.51, and this user has full rights on this database. Besides, this statement actually works fine: EXEC('TRUNCATE TABLE mydb.foo') AT MYSQL; ...so it doesn't appear to be a rights issue. Some users on the forums suggested first calling this: EXEC("USE mydb") AT MYSQL; That trick didn't help -- either from a Request window in SQL Browser or from a stored procedure. The command executes without complaint, but it doesn't seem to prevent the error. As a result, there doesn't appear to be ANY way to push bulk data INTO a MySQL database through ODBC, so I rank this bug as Critical. How to repeat: FROM MySQL: ----------- CREATE TABLE mydb.foo1 (myID INT); INSERT INTO sqldb.foo1 VALUES (1); CREATE TABLE mydb.foo2 (myID INT); FROM SQL Server 2005: --------------------- EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MySQL', @provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1 Driver};SERVER=HOST;Port=3306;USER=uid;PASSWORD=pw;OPTION=3;Initial Catalog=mydb;' (Substitute appropriate server/uid/pw, of course.) SELECT * INTO sqldb.dbo.foo FROM OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo1'); INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM mydb.foo2') SELECT * FROM sqldb.dbo.foo; The expectation is that the INSERT statement should put the row back into foo2, but instead, it gives a "No database selected" error. Suggested fix: Look at the code path for INSERT INTO OPENQUERY in v3.51 and compare it to the code path in v5.1 to see what's changed. This error should not occur.