"Out of Memory" Querying MySQL from MSSQL Linked Server

Question

"Out of Memory" Querying MySQL from MSSQL Linked Server

homebrew01

SSC Guru

Points: 55548
More actions
January 28, 2015 at 11:31 am

#300653

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;

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

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

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 ??

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

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)

LutzM SSC Guru Points: 107049 More actions · Answer 3

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]

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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