locked
mysql linked server queries RRS feed

  • Question

  • hello everyone, after spending most of the day trying to figure this out i hope that you guys with more experience can provide some suggestions. i have been experimenting with the a linked server (MYSQL 5) and managed to get all the select queries to work fine, but the insert is giving me troubles. MSSQL is 2008 R2, the odbc connector is the most recent.

    so here is some useful testing material:

     

     

    -- Table structure for myTest on the MYSQL server
    -- ----------------------------
    DROP TABLE IF EXISTS `myTest`;
    CREATE TABLE `myTest` (
     `id` bigint(20) NOT NULL,
     `mystring` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
     `mydate` datetime DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    -- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `myTest` VALUES ('1', 'string1', '2009-04-17 13:11:41');
    INSERT INTO `myTest` VALUES ('2', 'strin2', '2009-04-17 13:12:48');
    INSERT INTO `myTest` VALUES ('3', 'string3', '2009-12-09 12:29:48');
    INSERT INTO `myTest` VALUES ('4', 'nostring', '2009-12-18 12:29:48');
    INSERT INTO `myTest` VALUES ('5', 'stfing5', '2010-07-17 16:40:00');
    

     

    then i copied the data from mysql to MSSQL and made up new data and copied it into the 'local' copy of the mysql table:

     

    -- copy stuff from mysql
    select * 
    into [msTest]
    from openquery(MYSQL, 'select * from myTest');
    
    --make a copy and change a row for this example
    select * into [msTestCopy]
    from [msTest];
    UPDATE [msTestCopy]
     SET [id] = 10
      ,[mystring] = 'sadfasdfas'
      ,[mydate] = '2009-04-17 14:11:41.0000000'
    where [ID] =1;
    
    -- insert new row in the imported table
    declare @myId as int;
    set @myId=10;
    
    insert into [msTest] ([ID],[mystring],[mydate])
    select	T1.[ID],
    	T1.[mystring],
    	T1.[mydate]
    from [msTestCopy] T1
    where T1.[ID] = @myId;
    
    

     

    Then i tried an update query which was successful:

     

    UPDATE OPENQUERY (MYSQL, 'SELECT mystring FROM mydatabase.myTest WHERE id = 1') 
    SET mystring = 'sadfasdfas';
    

     

     

    Now this is where things just do not work. i tried an insert with the following:

     

    -- insert in mysql
    insert OPENQUERY(MYSQL, 'SELECT * FROM myTest')
    select * FROM [dbo].[msTest] T1
    where T1.[ID] = @myId;
    

     

    the error returned is not very useful"

     

    Msg 7399, Level 16, State 1, Line 5

    The OLE DB provider "MSDASQL" for linked server "MYSQL" reported an error. The provider did not give any information about the error.

    Msg 7343, Level 16, State 2, Line 5

    The OLE DB provider "MSDASQL" for linked server "MYSQL" could not INSERT INTO table "[MSDASQL]". Unknown provider error.


     

    then i went back to basics and tried the following

     

    insert OPENQUERY(MYSQL, 'SELECT * FROM myTest')
    values (7,'sadfasdfas','2009-04-17 14:11:41.0000000')
    

     

     similar error as a result:

     

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "MSDASQL" for linked server "MYSQL" reported an error. The provider did not give any information about the error.

    Msg 7343, Level 16, State 2, Line 1

    The OLE DB provider "MSDASQL" for linked server "MYSQL" could not INSERT INTO table "[MSDASQL]". Unknown provider error.

     

    Does anyone have any clue of what is happening and how i could solve it?

     

    Sunday, July 11, 2010 4:14 PM

Answers

  • hello everyone, this is one of the most annoying things i have come across (and i was amused by all the possible solutions proposed out there which did not work using TSQL)!

    anyway, apparently if you try to create the linked server using studio and the various menus, a small detail is not saved in the connection string, which is the database! (see last bit of the connection string)

     

    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;
    

     

    the bug in the mysql odbc driver seems to affect only the insert statement and has been around since 2008... it took me a long time to dig this out and i would have hoped that mysql fixed it by now...

    after you create the linked server using the procedure as above, the insert works as well as all the rest!

    • Marked as answer by psycolor Monday, July 12, 2010 10:48 AM
    Monday, July 12, 2010 10:48 AM

All replies

  • Now this is where things just do not work. i tried an insert with the following:

     

    -- insert in mysql
    insert OPENQUERY(MYSQL, 'SELECT * FROM myTest')
    select * FROM [dbo].[msTest] T1
    where T1.[ID] = @myId;
    

     

    the error returned is not very useful"

    In BOL there is no example of an Insert with OpenQuery based on a Select. Is it an option to use 4-part naming, is does MYSQL not support that?

    For example:

     

    insert into MYSQL.my_database.my_owner.myTest
    select * from msTest
    where ID = @myId
    
    

     

    then i went back to basics and tried the following

    insert OPENQUERY(MYSQL, 'SELECT * FROM myTest')
    values (7,'sadfasdfas','2009-04-17 14:11:41.0000000')
    

     

     similar error as a result:

    The example in BOL looks very similar, but does not use *. Have you tried this:

     

    insert OPENQUERY(MYSQL, 'SELECT Id, Name, myDate FROM myTest')
    values (7,'asdf','2009-04-17 14:11:41')
    
    
    

    -- 

    Gert-Jan

     

    Sunday, July 11, 2010 4:49 PM
  • well this is why it is a problem: BOL is not very helpful in this case and the example based on select was from a forum post which i cannot find anymore!

    the idea of using this seems to be the most 'natural'

    insert into MYSQL.my_database.my_owner.myTest
    select * from msTest
    where ID = @myId
    

    however the problem is that i literally have no clue (and tried to figure it out) how to refer to the linked server as a database object. unfortunately it doesn't seem to work as a normal database, therefore the use of thee openquery statement.

    what i have yet to understand is if the insert issue is a problem specific of mysql (the BOL example works with oracle) or if there is something i do not know/understand of the query. 

    if anyone has experience of linking with mysql i would certainly appreciate it very much!

    Sunday, July 11, 2010 5:03 PM
  • hello everyone, this is one of the most annoying things i have come across (and i was amused by all the possible solutions proposed out there which did not work using TSQL)!

    anyway, apparently if you try to create the linked server using studio and the various menus, a small detail is not saved in the connection string, which is the database! (see last bit of the connection string)

     

    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;
    

     

    the bug in the mysql odbc driver seems to affect only the insert statement and has been around since 2008... it took me a long time to dig this out and i would have hoped that mysql fixed it by now...

    after you create the linked server using the procedure as above, the insert works as well as all the rest!

    • Marked as answer by psycolor Monday, July 12, 2010 10:48 AM
    Monday, July 12, 2010 10:48 AM
  • If I use the above syntax:

    EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='NAME OF MYSQL ODBC',
    @provider='MSDASQL', @provstr='DRIVER={MySQL ODBC 5.1
    Driver};SERVER=SERVER IP;Port=3306;USER=myUid;PASSWORD=myPassword;OPTION=3;DATABASE=photo;'
    

    the linked server creates properly, but it won't even let me run this statement anymore:

    select * FROM OPENQUERY(MYSQL, 'SELECT * FROM photo.tag_type');
    

    It give this error:

    /* OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL".  */

    If I add this to the create statement @datasrc='MYSQL_MAIN' -- this being the name of the MySQL ODBC, therefore looking like this:

    EXEC master.dbo.sp_addlinkedserver @server='MYSQL', @srvproduct='MYSQL_MAIN',
    @provider='MSDASQL',@datasrc='MYSQL_MAIN', @provstr='DRIVER={MySQL ODBC 5.1
    Driver};SERVER=SERVER_IP_ADDRESS;Port=3306;USER=myUID;PASSWORD=#####;OPTION=3;DATABASE=photo;'

    I can at least run the above select statement, but I still get the error when running the insert statement.

    Insert statement:

    INSERT OPENQUERY(MY_SQL, 'SELECT * FROM photo.tag_type') VALUES (1, 'test');

    Error:

    /* OLE DB provider "MSDASQL" for linked server "MY_SQL" returned message "[MySQL][ODBC 5.1 Driver][mysqld-5.0.48-log]Commands out of sync; you can't run this command now".

    Msg 7343, Level 16, State 2, Line 1

    The OLE DB provider "MSDASQL" for linked server "MY_SQL" could not INSERT INTO table "[MSDASQL]".  */

    So can you please specify your exact syntax for this create linked server query and maybe a sample insert?  Which version of MySQL are you running?  I have tried a lot of different things and even read this post, http://bugs.mysql.com/bug.php?id=39965 with no luck, please help.

    Thanks

    Wednesday, July 14, 2010 3:35 PM
  • Wow I am an idiot, disregard this problem.  I finally realized that this part "{MySQL ODBC 5.1 Driver}" also needs to the name of the ODBC connection. Thanks for your great post though.
    Wednesday, July 14, 2010 7:08 PM
  • Wow I am an idiot, disregard this problem.  I finally realized that this part "{MySQL ODBC 5.1 Driver}" also needs to the name of the ODBC connection. Thanks for your great post though.


    dont suppose you are able to clarify this comment (a year later i know)?

    as i still cannot get it to work, and not sure how i can drop the ODBC connection name into the driver part of the string?

    this issue has been driving me crazy for months now, and keep coming back to it to be stuck in same place!

    cheers

    Wednesday, July 27, 2011 10:35 AM
  • This is probably out of date but I had the same issue (not with INSERT statements but with UPDATE to DATETIME fields) and I followed the instructions above with a few modification and got it to work.

    EXEC master.dbo.sp_addlinkedserver @server='MySQL', @srvproduct='MySQL ODBC',
    @provider='MSDASQL', @datasrc=N'MySQL_ODBC_Name', @provstr='USER=root;PASSWORD=PWD;OPTION=3;DATABASE=myDB;'

    The part that's in bold is what's missing from the official answer.

    Monday, April 7, 2014 4:44 AM
  • Thank you very much Cathlynn this is solved the problem for me.

    I wrote:

    @provstr=N'DRIVER={MySQL ODBC 5.3 Driver};

    but my SQL_OBDC name was MySQL ODBC 5.3 unicode Driver

    this is the right way for writing it

    @provstr=N'DRIVER={MySQL ODBC 5.3 unicode Driver};


    Tuesday, June 9, 2015 8:28 AM