List:General Discussion« Previous MessageNext Message »
From:Daniel Kasak Date:December 17 2002 3:39am
Subject:Re: ODBC query freezes server
View as plain text  
B F wrote:

> Hi all,
>
> I am having a problem with MySQL and I hope some nice guru can help me.
>
> I am running MySQL server v3.23.49a on Red Hat Linux v7.1. I also need 
> to run queries on Microsoft Access running on my Win2K machine, using 
> a MyODBC connection over the Internet. However when I run certain 
> queries my whole MySQL server hangs until the query is complete (up to 
> 10 minutes). Thus my entire website hangs while I run reports.  :(
>
> My queries use around 5 tables with <100,000 rows in total. Perhaps my 
> query is locking too many tables or using too many threads? I am 
> certain that I am NOT using the maximum # of MySQL network connections 
> (it is set to 1,000 and netstat reports <100 open connections). Is 
> there a way to limit resources so that a single query doesn't bring my 
> server to its knees?
>
>
> Any and all replies are much appreciated.
> Thanks in advance!
>
> Barry
>
Access will pull the entire tables across the internet and then query 
the data locally. This is what's taking all the time. Probably Access is 
requesting a lock until it gets the entire recordset(s).
Set up pass-through queries from Access (or better still use ADO / VB).

To set up a pass-through query, design the query, switch to SQL mode, 
copy the text (Access sometimes deletes it at this next stage) and click 
Query ==> SQL Specific ==> Pass Through. Then edit the query properties, 
and set up your connection options to MySQL. You may have to edit the 
query to get rid of Access' "additions" (eg [] around everything).
This will make MySQL perform the query and send the results to Access, 
instead of having Access use MySQL as a dumb data storage device.

The second option (ADO / VB) is a bit more complicated but more 
versatile. I won't go into the details here, but you can search on the 
'net for ADO - you'll find plently of examples.

Note that using Access to query MySQL over the internet isn't exactly 
secure. Someone correct me if I'm wrong here, but doesn't this send 
clear-text passwords across the internet?

-- 
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: www.nusconsulting.com

Thread
ODBC query freezes serverB F17 Dec
  • Re: ODBC query freezes serverDaniel Kasak17 Dec
Re: ODBC query freezes serverB F17 Dec
  • re: Re: ODBC query freezes serverVictoria Reznichenko17 Dec