LibMySQL.dll and Lost connections during Query

TheLion · Post by **TheLion** » Fri 10 Dec 2004 12:21

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?

Ikar · Post by **Ikar** » Fri 10 Dec 2004 16:10

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.

TheLion · Post by **TheLion** » Fri 10 Dec 2004 16:17

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

Ikar · Post by **Ikar** » Mon 13 Dec 2004 08:30

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

TheLion · Post by **TheLion** » Mon 13 Dec 2004 13:32

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?

Ikar · Post by **Ikar** » Mon 13 Dec 2004 16:14

Does this error happen if Direct := False?

TheLion · Post by **TheLion** » Mon 13 Dec 2004 16:24

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

Ikar · Post by **Ikar** » Tue 14 Dec 2004 08:13

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

Guest · Post by **Guest** » Tue 14 Dec 2004 10:52

Normal execute time is about 0.02 seconds. There are currently 517 rows in this table. Account and Staffno is the primary keys.

TheLion · Post by **TheLion** » Tue 14 Dec 2004 11:27

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.

Ikar · Post by **Ikar** » Tue 14 Dec 2004 16:19

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.

Guest · Post by **Guest** » Mon 20 Dec 2004 10:30

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?

TheLion · Post by **TheLion** » Mon 20 Dec 2004 15:21

Sorry,

forgot to login again

Ikar · Post by **Ikar** » Mon 20 Dec 2004 15:51

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.

TheLion · Post by **TheLion** » Mon 20 Dec 2004 15:54

Thank you

You've been most helpfull

Devart Forums

LibMySQL.dll and Lost connections during Query

LibMySQL.dll and Lost connections during Query

Re: LibMySQL.dll