ISAPI 'Unable to execute query' error under mySQL dbexpress.
Dan Ridenhour
Well, im making progress on my ISAPI issues with mysql, and think I have things narrowed down to the following error:
I can duplicate this myself by using two workstations and clicking refresh on two pages... these pages open about 4-5 tables a piece and display information. Its all selects, no writes of any kind.
After doing this for a min, it gets about 20 instances of the webmodule active. Then starts reporting errors. I originally had cache connections off, and the errors being reported were a mix of Access Violations outside of my code and some inside my code.
I enabled cached connections, in case the issue with reconnecting to mysql at each action... and all my errors became very consistent, as follows.
If i hit refresh on the two workstations for a while ill start getting the following error:
Exception: Unable to execute query.
If I wait a few seconds and hit refresh, the app works fine again... if I keep hitting refresh i keep getting errors, then if i wait a couple minutes and try again its like whatever it was running out of, it got back and now functions normally again for a couple minutes... then gives the 'Unable to execute query' again.
Could this possibly be related to the connections hanging for 240ms that was mentioned in these boards earlier?
At this point, the app seems very stable though... no errors crash the system, it just cant execute queries for a few seconds then it can again. Kind of like the max connections to mysql is full and you have to wait for them to timeout.
Any ideas?
Thanks,
Dan Ridenhour
dride...@stltoday.com
Dan Ridenhour
Dan Ridenhour
When i put enough of a load on the ISAPI it will return 'Unable to execute query' on the following query:
Select *
from
SCHEDULE
where
Where SIndex=296 and
(HIndex=5 or AIndex=5)
order by
GDate, GTime
This is the last of 4 queries that this action makes during its run... in 15 duplications of the error it was always on this query, but in hundreds of other instances this query works without issue, and its not overly complicated. The SCHEDULE table consists of approx 400k records, and typically returns a result set of 50 or less records.
I tend to think that the error is being caused more because its the 4th query for the action, instead of what the query actually does.
Shiv Kumar
system, it just cant execute queries for a few seconds then it can again.
Kind of like the >max connections to mysql is full and you have to wait for
them to timeout.
That's what it looks like to me. This is more a Database limitation issue
than something to do with dbExpress. Of course I can't be sure.
> I can duplicate this myself by using two workstations and clicking refresh
on two pages... these pages open about 4-5 tables a piece and display
information. Its all selects, no writes of any kind.
If any of these tables can be cached, that might be one way of reducing the
number of queries being fired. Further, have you tried using stored procs
instead? Maybe you'll get different behavior.
> After doing this for a min, it gets about 20 instances of the webmodule
active.
Try setting this to a higher number. If a recall correctly, the default is
30 is it not. You might also want to remove the code you have that attempts
to change this value. Set it once in the dpr file.
> Exception: Unable to execute query.
That's the beauty of dbExpress. Exception messages leave you clueless. I
hope Borland will fix this in the next release.
--
Shiv R. Kumar
The Delphi Apostle
http://www.matlus.com
Shiv Kumar
Try using field names instead of *
Try using stored procs instead of queries.
My thinking here is, if the db engine is getting overly stressed it might
make sense to speed things up or help it speed up.
Is the db server on the same machine as the web server? If so, try moving it
to a different machine. I wonder what the effect of network latency would
have on your "consistency" of errors. (still consistent, better, worse)
> I tend to think that the error is being caused more because its the 4th
query for the action, instead of what the query actually does.
Can you make it the 1,2 or 3rd?
DaveH
If not, you need to use a Transaction around your query - even if it is just
a read transaction.
If you are, what are your Connect parameters in the TSQLConnection?
It sounds like your database is not setup to allow multi-user access. If you
had to, you could always use a CriticalSection around your SQL calls so only
one happens at a time. Kind of sucks for performance, but it should isolate
the problem.
DaveH
"Dan Ridenhour" <dride...@stltoday.com> wrote in message
news:3f5ce674$1...@newsgroups.borland.com...
Dan Ridenhour
Im using dbexpress. The app itself is legacy though, its been in production for 2 years using Direct Oracle Access with as many as 100 users online without problems. Im migrating it to MySQL, basically changed just the db components in my web module and changed the param setting from .SetVariable( in DOA to .ParamBYName in dbexpress.
>If not, you need to use a Transaction around your query - even if it is just
>a read transaction.
>If you are, what are your Connect parameters in the TSQLConnection?
Ive tried an assortment. Right now i have CachedConnections enabled in the web module, keepconnections=False in the TSQLConnection, but ive tried it with just about every combo of connection properties.
>
>It sounds like your database is not setup to allow multi-user access. If you
>had to, you could always use a CriticalSection around your SQL calls so only
>one happens at a time. Kind of sucks for performance, but it should isolate
>the problem.
True, although it does allow multiple connections for a while, then it stops... then it starts again. Truely wierd. If i put all the sql queries into crit sections it would hurt performance under a load too much, i may as well turn it in a CGI instead of ISAPI at that point.
Dan Ridenhour
"Shiv Kumar" <sh...@erols.com> wrote:
>Are GDate and GTime indexed fields?
No they are not, the Other Index fields are all indexed, and narrow the data down to maybe 50 rows. The query is actually quite speedy maybe 100ms tops.
>Try using field names instead of *
Done. No difference.
>Try using stored procs instead of queries.
Havent tried that yet, Im really trying to find the problem without completely rewriting how I access things. Im under the gun on this, managment got a timeline from me, sat on it for a couple months than wanted it done by the original date.
>
>My thinking here is, if the db engine is getting overly stressed it might
>make sense to speed things up or help it speed up.
>
>Is the db server on the same machine as the web server? If so, try moving it
>to a different machine. I wonder what the effect of network latency would
>have on your "consistency" of errors. (still consistent, better, worse)
The dbserver is in the same rack, but different machine. Im not in a position to move it, although Id like it on the same box to remove the network from the equation.
>> I tend to think that the error is being caused more because its the 4th
>query for the action, instead of what the query actually does.
>
>Can you make it the 1,2 or 3rd?
No matter what order I place the queries, its always the last query in the list that reports the error. Or at least it seems to be, the dbexpress errors are very unspecific about things.
Btw, how can you check to see how many tcpip ports are being used by a process in NT4?
Thanks,
Dan Ridenhour
dride...@stltoday.com
Shiv Kumar
list that reports the error. Or at least it seems to be, the dbexpress
errors are very >unspecific about things.
Maybe a small (intentional) delay between queries might help?
> Btw, how can you check to see how many tcpip ports are being used by a
process in NT4?
Don't know :) Why?
Dan Ridenhour
> No matter what order I place the queries, its always the last query in the
list that reports the error. Or at least it seems to be, the dbexpress
errors are very >unspecific about things.
>Maybe a small (intentional) delay between queries might help?
I tried that one. No change.
> Btw, how can you check to see how many tcpip ports are being used by a
process in NT4?
Don't know :) Why?
Just this issue ive been looking at over on the dbexpress forum about dbexpress mysql connections not releasing there TCPIP ports immediately after the connection is closed... so you can over time build up open ports until you hit the max for the os and then your connections start failling... wait a while and try again and everything works until you create a bunch more ports and do it again.
That sounds like whats happening to me alot of the time, ill start getting Unable to execute queries on simple queries that worked 100s of times before, and then ill get invalid username/password which is the generic error dbexpress returns if it cant make a connection.
What ya think?
Dan
Shiv Kumar
dbexpress mysql connections not releasing there TCPIP ports immediately
after the connection is closed... so you can over time build up open ports
until you hit the max for the os and then your connections start failling...
wait a while and try again and everything works until you create a bunch
more ports and do it again.
>
But if you have KeepConnection (or whatever that property is) set to true
and your WebModules are cached you should only ever have as many connection
as webmodules. If that's too many, you should probably try limiting the
number of web modules then.
Del Murray
Have you talked to the support people at MYSql ??? Do they have support ??
If not, here is what i think after watching all this. I will state up front
that this is biased and opinionated :-). I dont love M$, in fact I hate M$,
but if you are up against the wall with an "unsupported" database, it seems
to me that a big company like the St Louis newspaper can afford a copy of MS
SQL server 2000. I think , as Shiv, that you have DB issues and if there is
no real MySql vendor with tech support , then you have to fix that problem,
not your code. Our job shouldn't be to have to spend our time debugging a
vendors code, they should do it. If the TCP connection thing is your issue
(and it sounds plausible), then MYSql should either fix it or help you out,
not leave the rest of the world to deal with it in a support forum.
Just my stupid opinion ... I may be wrong , but I'm sure.
Del,
PS ... I have never had a problem with SQL server (when properly patched up
from M$) and I have webmodules with upwards of 40 querries active in them at
the same time and multiple copies of the webmodule pounding away at the
database with straight querries, strored procedures, transactions,
everything.
DaveH
Can you do a quick port to Firebird? It's as free as MySql but with more
capability (ie StoredProcs). I've had no problems with multi access and
I've ported from both Oracle and MS SQL.
DaveH
"Dan Ridenhour" <dride...@stltoday.com> wrote in message
news:3f5df282$1...@newsgroups.borland.com...
Dan Ridenhour
>Have you talked to the support people at MYSql ??? Do they have support ??
Nope, support is an added cost option, my companies not springing for... so its figure it out along the way.
>If not, here is what i think after watching all this. I will state up front
>that this is biased and opinionated :-). I dont love M$, in fact I hate M$,
>but if you are up against the wall with an "unsupported" database, it seems
>to me that a big company like the St Louis newspaper can afford a copy of MS
>SQL server 2000.
I Have SQL server 2000 but havent used it, also have a production application in Oracle 8i which routinely services 50-100 users, multiple web modules, transactions, etc. In other words Ive go a few years working with ISAPIs in borland tools and have been quite successful... just hit a multiuser snag porting to mysql. The move to mysql was a cost decision... dont know where you have been but in the post dotcom/911 world everybody is tight with the purse strings these days. Its being moved to mysql also for scalability, so new servers can be added without new licensing.
I think , as Shiv, that you have DB issues and if there is
>no real MySql vendor with tech support , then you have to fix that problem,
>not your code. Our job shouldn't be to have to spend our time debugging a
>vendors code, they should do it. If the TCP connection thing is your issue
>(and it sounds plausible), then MYSql should either fix it or help you out,
>not leave the rest of the world to deal with it in a support forum.
Well, I differ from your opinion here. A deveopment support forum is a place to look for answers others may have posted, answer other peoples questions if you can, and ask your own when you have a problem... I dont think its anyones JOB to come here... well perhaps the teamb folks but i dont see teamb on your name. So I had a problem and I posted questions to help me track it down... the help of shiv and dmitry (over in dbexpress) have been invaluable in tracking down whats going on in this case, and I cant thank them enough. Im sure more people read here than post, and im also sure theres not enough activity on these forums to scream about the bandwidth ive been using... so I dont see a problem with it, in fact I think its just what these forums are for... and ill be just as likely to answer someone elses problem if i can. Sorry if thats a different world view from yours.
MySQL has support, they also charge for it... and again with the purse strings. If i had my choice Id stay in oracle, its a beautiful robust environment... just costs way to much for each and every server you add... and the same goes for most commercial dbs, so mysql it is... for better or worse.
>Just my stupid opinion ... I may be wrong , but I'm sure.
>
>
>Del,
>PS ... I have never had a problem with SQL server (when properly patched up
>from M$) and I have webmodules with upwards of 40 querries active in them at
>the same time and multiple copies of the webmodule pounding away at the
>database with straight querries, strored procedures, transactions,
>everything.
havent worked with MS sql, had a copy purchased way back when but its user limitation prohibits its use on a webserver app... Ive had great luck with oracle though... doing pretty much everything you are in oracle without problems... just porting to dbexpress -->mysql that had issues.
Thanks,
Dan
>
>
Dan Ridenhour
"DaveH" <da...@sympatico.com> wrote:
>Just a thought from reading the other messages...
>
>Can you do a quick port to Firebird? It's as free as MySql but with more
>capability (ie StoredProcs). I've had no problems with multi access and
>I've ported from both Oracle and MS SQL.
Wish I could give it a try... but its more a corporate decision... moving more and more to mysql, it lets more tasks be supported by the same mysql admin i suppose.
Dan
>
>DaveH
>
>"Dan Ridenhour" <dride...@stltoday.com> wrote in message
>news:3f5df282$1...@newsgroups.borland.com...
>>
>> "DaveH" <da...@sympatico.com> wrote:
>> >Which Data Access components are you using? dbExpress?
>> >
>>
>> Im using dbexpress. The app itself is legacy though, its been in
>production for 2 years using Direct Oracle Access with as many as 100 users
>online without problems. Im migrating it to MySQL, basically changed just
>the db components in my web module and changed the param setting from
>..SetVariable( in DOA to .ParamBYName in dbexpress.
Shiv Kumar
Having known Del, I think you've misunderstood him :).
When he said "our job" he didn't mean people on these ngs helping out, but
rather, "us programmers". He was simply making a suggestion.
I'm sure we all understand having to work within constraints, but one still
feel to make suggestions.
Anyway, I thought I'd cool the waters here some what :)
Let get back to solving your problem.
Dan Ridenhour
"Shiv Kumar" <sh...@erols.com> wrote:
>Dan,
>
>Having known Del, I think you've misunderstood him :).
>
>When he said "our job" he didn't mean people on these ngs helping out, but
>rather, "us programmers". He was simply making a suggestion.
>
>I'm sure we all understand having to work within constraints, but one still
>feel to make suggestions.
>
>Anyway, I thought I'd cool the waters here some what :)
>
>Let get back to solving your problem.
Shiv,
Your probably right... it just hit me the wrong way, like my asking questions was a waste of everyones time and bandwidth... but it had been a long day.
Btw ive made a bit of progress based on alot of feedback from these boards. It seems while my app worked fine under Oracle with DOA (direct oracle access) the limitations of mysql and dbxpress's handling of the interface led to alot of issues.
Heres what things ive done recently to my test app:
Each webmodule now has a single connection component again, this connection is established during the oncreate of the webmodule. It has autoclone set to false so now additional connections will be cloned. The connection is made in a critical section so only one thread can make a connection at a time.
All queries are now connnected to a data provider and client dataset enabling them to be closed after data is retrieved. I call connection.closedatasets after each query in an action to assure that all datasets are closed before opening the next query. Between this an autoclone=off it assures that only one connection per webmodule is ever used.
The connections are set to keepconnection=true to reduce the number of connections opening/closing and the associated tcpip ports that mysql allocates for each connection.
The end result is fairly stable, and I can only reproduce one error... If i hit it hard creating multiple webmodules (20+) ill start getting Invalid Username/Password errors. Im looking into this problem now.
Heres what I think ive learned about mysql:
- Only one open query per connection is allowed.
- dbexpress autoclones connections for queries as needed.
- opening connections needs to be done serially for all threads
- Use client datasets for data access.. only use dbexpress for retrival/updates.
- Each connection uses 2 tcpip ports in mysql which dont free immediately after use (takes about 30 seconds to timeout)
- Large numbers of connections will cause the MySQL server to stop responding.
I can safely say the DOA components for oracle access are MUCH more robust... allowing unlimited queries per connection makes things alot easier to deal with, but dbexpress+mysql seems to work, just there are a number of pitfalls out there and not much doc on it.
I'll msg again later today to see what i come up with on the invalid username/password issue. Also, hopefully i can reproduce the stability i got last night when i retest today. ;-)
Thanks again,
Dan
Dan Ridenhour
"Dan Ridenhour" <dride...@stltoday.com> wrote:
>
>I'll msg again later today to see what i come up with on the invalid username/password issue. Also, hopefully i can reproduce the stability i got last night when i retest today. ;-
Shiv,
I found the cause of the Invalid Username/Password errors... I was opening too many connections... in my Oncreate for the webmodeul i was still opening 6 TSQLConnection components from my earlier attempts to pool connections.
I removed the extra connections so there was only one per datamodule and I stressed it to 80 simultanious ISAPI requests under IIS and it didnt break! I havent tried data updates using this model yet... but queries are looking MUCH better.
Thanks,
Dan Ridenhour
dride...@stltoday.com