Page 1 of 3
LibMySQL.dll and Lost connections during Query
Posted: Fri 10 Dec 2004 12:21
by TheLion
Hi
I now have several different version of this library located in many different folders on my programming pc and my live servers.
Do I need to have the same version on my PC as on my servers?
I am running mysql 4.0, wich LibMySQL.dll is the correct one and where do I place it to make sure its the right same one I use on my PC and servers?
Re: LibMySQL.dll
Posted: Fri 10 Dec 2004 16:10
by Ikar
If you work with TMyConnection.Options.Direct := True (by default) libmysql.dll isn't used.
But if even not version of the client doesn't have a great importance.
Posted: Fri 10 Dec 2004 16:17
by TheLion
OK.
But I have just added the TMyConnection.Options.Direct := False, due to your answer on another question. Does this result in other bugs?
We reproduced and fixed this error. Usually it happens on the execution of long queries. Fix of the problem will be available with the next version in about two weeks.
As a temporary solution you can set TMyConnection.Options.Direct := False
Posted: Mon 13 Dec 2004 08:30
by Ikar
If by any reasons you cannot use Direct := True, you'd better use the last stable version of libmysqld.dll (since 4.1.7). Although it doesn't have great sense
Posted: Mon 13 Dec 2004 13:32
by TheLion
Well. What I am really trying to do, is limit the Lost connections I get
Here is part of the code I use to retry and hopefully complete a Query.
Normally fRetries := 10; But sometimes it isn't enough.
Code: Select all
...
SQLDone := False;
RetryNo := 0; // records the number of failed link to the SQL server
while (Not SQLDone) and (RetryNo 0 then // if Row(s) returned then point to the first row
fMyQuery.First; // fails if there isn't any row to choose. Hence the if statement
SQLDone := True;
except
Inc(RetryNo);
Sleep(250); // Wait if we have an error
end;
...
The 2 most used tables are also the ones I loose connection to most of the time.
The tables is status tables with 6 and 7 fields and from 4 to 24 rows.
The Select Statements that fails is
SELECT * FROM tmlstatus WHERE tlineno=1
and
SELECT * FROM programstatus WHERE name="TML" AND computername="DKTML001"
What can I do more, to try to get a query succesfully done? Should I put some disconnect/Reconnect code in the TFRMySQL.ErrorMyConnection procedure? Do you have any surgestions?
Posted: Mon 13 Dec 2004 16:14
by Ikar
Does this error happen if Direct := False?
Posted: Mon 13 Dec 2004 16:24
by TheLion
Yes. It happens wether its True or False. Have not been running with the False settings for more than a few days, but it still happens. To soon to tell if it happens more or less. The last time a Query on one of our live servers failed (lost connection 10 times in a go) was 16:04 (GMT). Thats about 15-20 minuttes ago. It was a simple select query:
SELECT * FROM staff WHERE account=147 AND staffno=2619
I got 10 lines like this one in my error log:
16:04:59.328: [TTLLStaff.ErrorMyConnection] Lost connection to MySQL server during query
Posted: Tue 14 Dec 2004 08:13
by Ikar
Please specify how much time roughly does it take to execute this query.
Is it possible that this query will be executed more than 30 sec.?
Posted: Tue 14 Dec 2004 10:52
by Guest
Normal execute time is about 0.02 seconds. There are currently 517 rows in this table. Account and Staffno is the primary keys.
Posted: Tue 14 Dec 2004 11:27
by TheLion
Sorry, Forgot to login
Normal execute time is about 0.02 seconds. There are currently 517 rows in this table. Account and Staffno is the primary keys.
Posted: Tue 14 Dec 2004 16:19
by Ikar
Do you use any locking for this table? Can it be that working with this table differs from working with another tables?
According to the same behaviour on using different clients (Direct := True,False), the problem isn't in MyDAC but in the server. Try to look for a correlation with the execution of another queries to the same table. Especially, UPDATE.
Also you can try to execute SELECT with modifier HIGH_PRIORITY.
Posted: Mon 20 Dec 2004 10:30
by Guest
Thank you for your answer. Gave me some things to look at
Don't use any locking on any tables, except for the nightly backup. I work in the same way wiith all my tables. So it don't differ.
I have crossreferenced my logs for all my services running on my systems. I can se that my programs sometimes read and writes to my 2 status tables several to many times a second. That is perhaps not a good idea
On one system I have 5 programs on 2 servers doing this. On my other system I have 7 programs on 3 servers figthing to get through to these status tables.
I can se that when one of my status tables "fails", the following queries and posts to other tables fails aswell!!! But only in the same program/connection. The other programs seems unaffected, until they in "turn" fails.
I am using MySam tables, would it be better using InnoDB or any of the other engines?
Is there any way of finding out how much my installations of MySQL can handle when working through MyDAC?
Posted: Mon 20 Dec 2004 15:21
by TheLion
Sorry,
forgot to login again
Posted: Mon 20 Dec 2004 15:51
by Ikar
It seems this issue is already out of MyDAC functionality. Try to experiment with options UPDATE and SELECT or ask a question at MySQL forum.
> I am using MySam tables, would it be better using InnoDB or any of the other engines?
All of them have advantages and imperfections. For status table MyISAM must be more suitable.
Posted: Mon 20 Dec 2004 15:54
by TheLion
Thank you
You've been most helpfull