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!
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?
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989