"Out of Memory" Querying MySQL from MSSQL Linked Server

  • I have another puzzle.

    I am trying to pull records from MySQL into a SQL temp table (either #Temp table, or physical table), and I can pull in a subset with a query like

    select top 1000 ID

    from MYSQL_WEB_PROD...wp_users

    But if I do an ORDER BY, on even just a few records, I get this error:

    OLE DB provider "MSDASQL" for linked server "MYSQL_WEB_PROD" returned message "[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-log]MySQL client ran out of memory".

    Msg 7306, Level 16, State 2, Line 1

    Cannot open the table "wp_users" from OLE DB provider "MSDASQL" for linked server "MYSQL_WEB_PROD".

    I am just selecting ID (int), and can return 10,000 rows, but if I try just 1 row with an order by or MAX, I get the error. The query results seem small enough that I should not have memory issues. I posted on a MySQL forum, but the only thoughts so far is that it's MSSQL or ODBC problem.

    Here is the WordPress create script

    CREATE TABLE `wp_users` (

    `ID` bigint(20) unsigned NOT NULL auto_increment,

    `user_login` varchar(60) NOT NULL default '',

    `user_pass` varchar(64) NOT NULL default '',

    `user_nicename` varchar(50) NOT NULL default '',

    `user_email` varchar(100) NOT NULL default '',

    `user_url` varchar(100) NOT NULL default '',

    `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',

    `user_activation_key` varchar(60) NOT NULL default '',

    `user_status` int(11) NOT NULL default '0',

    `display_name` varchar(250) NOT NULL default '',

    PRIMARY KEY (`ID`),

    KEY `user_login_key` (`user_login`),

    KEY `user_nicename` (`user_nicename`),

    KEY `ID_Desc` (`ID`)

    ) ENGINE=MyISAM AUTO_INCREMENT=2904656 DEFAULT CHARSET=utf8;

  • Someone on a MySQL forun feels confident it's not MySQl causing the problem, but rather a bottle neck in the ODBC DSN configuration ...

    Thoughts on that ??

  • Have you tried nesting the select in another query that has the ORDER BY?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Did you try use OPENQUERY to force the ORDER BY being performed at the MySQL side before transferring the data?

    What happens if you run that query directly at the MySQL Server? Does it perform acceptable?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (1/28/2015)


    Did you try use OPENQUERY to force the ORDER BY being performed at the MySQL side before transferring the data?

    What happens if you run that query directly at the MySQL Server? Does it perform acceptable?

    Wow, OPENQUERY works great !

    That gets my data to MSSQL. Can I use it to get data from MSSQL, and INSERT back to MySQL ?

    I want to select data from my MSSQL table, and insert it into MySQL. But if OPENQUERY runs directly on the MySQL server, it won't have access to the MSSQL data ??

    What I am doing is finding ID records in MSSQL, that are not in MySQL, and inserting them back to MySQL to keep the 2 servers in sync.

    I had it working before with:

    INSERT INTO MSQL_LINKED_SERVER...WP_USERS

    SELECT FLD1, FLD2, FLD3

    FROM MSSQL_TABLE

    But now get the memory error also

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply