ySQL server has gone away: but not a timeout issue

sean · Post by **sean** » Tue 15 May 2007 15:20

Hi,

I've mydac5 with Delphi7. I have this issue for a few months now, and its still there after a recent upgrade to mydac5.

This happens to me in several situations, lets start by tracing one: when I run by program in Delphi 7, and quit in the standard way, I get the "MySQL server has gone away".

This has nothing to do with the client or server side timeouts mentioned in the FAQ, because I just start and stop the program in a few seconds.

If I do trace into, or step over in Delphi, it just continues as if the program has terminated normally.

The MySQL server is running on the localhost and is 5.0.21.

I also get the "MySQL server has gone away" at other points in my program, but not related to idle timeouts either.

I have tried enabling and disabling pooling, but that made no difference. The ConnectionTimeout is 60.

Thanks in advance..

Antaeus · Post by **Antaeus** » Thu 17 May 2007 08:39

We could not reproduce the problem.
Please send us a complete small sample at evgeniyD*crlab*com to demonstrate it, including script to create database objects.
Also specify the exact version of MyDAC you are using. You can see it in the About sheet of TMyConnection Editor.

Antaeus · Post by **Antaeus** » Thu 17 May 2007 09:04

Also specify, does the problem arise in a single-threaded or in a multi-threaded application?

sean · Post by **sean** » Thu 17 May 2007 10:04

I'm not explicitly use threads (but Delphi might be doing it in the background).

The 'MySQL gone away' only happens in a complex application with about 50 tables. A simple sample app cannot reproduce it. I suspect that the error only happens on one specific table, but need to nail it down.

To try and trace the last SQL commands send before the error happens, I added a MyDacMonitor. Then I cannot compile the application:
"Unit MyDacMonitor was compiled with a different version of DBAccess.TDAParams"

I upgraded from Mydac4 to Mydac 5.00.1.6 just last week.

sean · Post by **sean** » Thu 17 May 2007 17:25

As regards threads, mydac obviously uses threads, because sometimes
when I quite the application, instead of the 'MySQL gone away' message,
I get:

EMySQLException #HY000Unknown thread id: 256

The thread ID number changes.

Does this help to trace the issue?

Antaeus · Post by **Antaeus** » Fri 18 May 2007 10:12

sean wrote: Then I cannot compile the application:
"Unit MyDacMonitor was compiled with a different version of DBAccess.TDAParams"

I upgraded from Mydac4 to Mydac 5.00.1.6 just last week.

This may happen because MyDAC uninstallation was unsuccessful. So, you have some files left from previous version of MyDAC on your hard drive. Try to uninstall MyDAC and remove all the files concerned to it. Then install MyDAC one more time. To remove all vestiges of MyDAC, please follow these instructions:

1) Close IDE
2) Uninstall MyDAC
3) Search for MyAccess.*, DBAccess.*, mydac*.bpl, dac*.bpl etc files at your HDD and delete them
4) Remove from the project directory all files made by compiler and linker
5) Remove from the registry (HKCU\Software\Borland\Delphi\7.0\Known Packages) all the links to MyDAC packages (mydac*.bpl, dac*.bpl)
6) Run IDE and check that it is started without errors
7) Close IDE
8) Install MyDAC once more

sean wrote:As regards threads, mydac obviously uses threads, because sometimes
when I quite the application, instead of the 'MySQL gone away' message,
I get:
EMySQLException #HY000Unknown thread id: 256
The thread ID number changes.

Does this help to trace the issue?

This thread is the MySQL server thread number.
This is an internal MyDAC exception, you should not see this error if you run your application without debugging. It can happen if you close your application that has unfetched datasets in FetchAll=False mode.

sean · Post by **sean** » Fri 18 May 2007 12:39

As regards MyDacMonitor

1) Close IDE
2) Uninstall MyDAC
3) Search for MyAccess.*, DBAccess.*, mydac*.bpl, dac*.bpl etc files at your HDD and delete them
4) Remove from the project directory all files made by compiler and linker
5) Remove from the registry (HKCU\Software\Borland\Delphi\7.0\Known Packages) all the links to MyDAC packages (mydac*.bpl, dac*.bpl)
6) Run IDE and check that it is started without errors
7) Close IDE
Install MyDAC once more

When I upgraded from mydac4 to 5, I had to use the /force option, since if I deinstall mydac4 (via the Control Panel), it wipes the Delphi tool palette. i.e. it does not deinstall cleanly.
Will the /force option not ensure that the correct files are installed? I don't want to mess up my IDE!

Antaeus · Post by **Antaeus** » Mon 21 May 2007 07:03

In the general case installation with this option works correctly. But if you have files from an old MyDAC version in another directory (MyDAC installation paths of the old MyDAC version and the new one are different, or the files are copied manually), MyDAC installation wizard will be unable to handle this situation.
Try to search DBAccess.* files on your hard drive, and remove all of them except the one that is in the directory where MyDAC 5 is installed. If this does not help, check patches specified in Project Options and in Environment Options. Probably they point to network resources.

sean · Post by **sean** » Mon 21 May 2007 23:00

Hmm. I wonder if its not a mydac/sdac conflict?
I do not use network drives, or non standard install locations.
I searched for DBAccess.* and there were entries in the lib dirs of:

C:\Program Files\CoreLab\Sdac for Delphi 7
C:\Program Files\Borland\Delphi7\Sdac
C:\Program Files\CoreLab\MyDac for Delphi 7
C:\Program Files\Borland\Delphi7\MyDac

So I renamed the last two directories to DIRECTORY.OLD, and re-ran the mydac install with /force.

Which recreated C:\Program Files\CoreLab\MyDac for Delphi 7
The problem with the mydac monitor persists though.

Ah, then. I had the idea to rename C:\Program Files\Borland\Delphi7\Sdac to C:\Program Files\Borland\Delphi7\Sdac.OLD

This *worked*, which would mean it was pulling the library for an older version of sdac?

Antaeus · Post by **Antaeus** » Tue 22 May 2007 10:22

Yes, you are right. When compiling application with MyDAC, IDE tries to use DBAccess.dcu file from SDAC directory. In order to continue using both products simultaneously, you should use compatible versions of MyDAC and SDAC. They are: MyDAC 5.00.1.7 and SDAC 4.00.0.7.
The last versions of our products available for download from our site, are always compatible.

sean · Post by **sean** » Tue 22 May 2007 14:03

Going back to my original problem with 'mysql gone away' I need to track it down.

I'm developing two applications and one is running just fine, so its not a general mydac issue, but something in the way I'm opening/closing datasets. [This also means I cannot easily create an example app to demo the issue]

You said:

This is an internal MyDAC exception, you should not see this error if you run your application without debugging. It can happen if you close your application that has unfetched datasets in FetchAll=False mode

Can I catching this somehow and cleanly close datasets, so I can at least get rid of these design time errors?

I wonder if its how I close tables? I use the following function to close most tables explicitly:

Code: Select all

procedure CloseMyTable(Table: TMyTable);
begin
  if (Table.State = dsInactive) then begin
    debug2('CloseMyTable, already closed: ' +Table.TableName);

  end else if (Table.State in [dsBrowse,dsSetKey]) then begin
    try
      //debug2('CloseMyTable: ' +Table.TableName);
      Table.Close;
    except
      log_error('CloseMyTable: cannot close ' +Table.TableName);
    end;
  end else if (Table.State in [dsEdit,dsInsert]) then begin
    debug2('CloseMyTable: Cancel & Close ' +Table.TableName);
    try
      Table.Cancel;
      Table.Close;
    except
      log_error('CloseMyTable: cannot cancel & close ' +Table.TableName);
    end;
  end else begin
    log_error('CloseMyTable, in unexpected state: ' +Table.TableName);
  end;
End;

Is this a good idea, or overkill?

When I quit the application I don't see any activity in MySQL monitor either. (I enabled tracing of all SQL).

Thanks in advance.

Antaeus · Post by **Antaeus** » Wed 23 May 2007 14:13

sean wrote:Can I catching this somehow and cleanly close datasets, so I can at least get rid of these design time errors?

This internal MyDAC exception is cached and handled by MyDAC. You can suppress this exception using Debugger Options of your IDE.
Just call the corresponding dialog from the Tools IDE menu, and add EMySQLException to the Exception Types to Ignore on the Language Exception tab.

sean wrote:I wonder if its how I close tables? I use the following function to close most tables explicitly:

It looks like a correct way. We recommend also put the exception class name and the exception message to your log:

Code: Select all

  try 
    Table.Close; 
  except 
  on e: Exception do 
      log_error('CloseMyTable: cannot close ' + Table.TableName 
                    + ' e.ClassName: ' + e.ClassName + ' e.Message: ' + e.Message); 
  end;

Exceptions of the EMySQLException class should not appear in it. Please inform us if such exceptions appear.

sean · Post by **sean** » Fri 25 May 2007 08:25

Just call the corresponding dialog from the Tools IDE menu, and add EMySQLException to the Exception Types to Ignore on the Language Exception tab.

This has no effect on the EMySQLException prompts I get when quitting the application. ??

And why do I see EMySQLException with HY000Unknown thread id: 233 when quitting my app after it has browsed a few tables (only in design mode)? If these are internal to mydac, they should be caught by mydac? What does this error mean?

Antaeus · Post by **Antaeus** » Fri 25 May 2007 10:34

Please provide us with the exact steps to reproduce the problem in a new application. And specify the exact MyDAC version you use.