Repeated logins on single connection

[Rytmis]

Hi,

I'm working on something of a legacy application, where we recently switched to MySQL (with MyDAC 4.30) as the backend. When testing the app at the client's site, we discovered an issue where on a long batch of operations the connection would seem to die.

Since we didn't have a dev environment there, I used the next best thing -- I fired up Wireshark (formerly Ethereal) to snoop on the connection, to see what's going on. To my great surprise I noticed that the application logs in to the database literally hundreds or even thousands of times, all the while using a single MyConnection instance. Please note that we have never seen more than one concurrent connection at the server's side (although it is still possible).

I've tried to narrow the issue down to a minimal test case with no luck -- the application is pretty tangled, so extracting a single problematic bit is nigh impossible. However, as an example, when the application starts, it opens up three tables and/or SQL queries which results in three logins to the db. Opening three tables in a blank test project does no such thing, so I'm guessing whatever causes it happens in an event handler somewhere.

For the record: I've tried using both Direct access and the MySQL client library, and both with and without connection pooling, with no change to the behaviour. Also, I've reproduced the issue with MyDAC 4.40.

Now, I'd like to ask if you know of any situation when the MyDAC components would re-login using the same connection, or possibly spawn new connections altogether?

[Rytmis]

Hi again,

As it turns out, the application does indeed seem to cause up to four concurrent connections to the db. Still, we never explicitly open a new connection, so one of the components must be doing it implicitly.

[Rytmis]

Ok, I just figured out the cause. The application used a single Query component all over the place to run ad-hoc queries, and the component had FetchAll set to false, which apparently opened a new connection to make fetching additional rows possible. Setting the value to true seems to have fixed the issue.

[Antaeus]

> Now, I'd like to ask if you know of any situation when the MyDAC
> components would re-login using the same connection,
MyDAC does not relogin on its own.
> or possibly spawn new connections altogether?
One extra connection to the server is created for every dataset only with FetchAll=False option. But this should not lead to appearing connect dialog.

> ...so one of the components must be doing it implicitly.
If you try to call Open, Execute etc. methods of the component that is linked to disconnected MyConnection object, MyConnection tries to establish connection.

Unfortunately we will not be able to help you if we do not have more information about this problem. A small sample to reproduce the problem will be very useful. If you have such application please send it to us (evgeniyD*crlab*com) including script to create and fill table.
Also supply us following information
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MySQL server and MySQL client. You can see it in Info sheet of TMyConnection Editor

[Rytmis]

> or possibly spawn new connections altogether?
One extra connection to the server is created for every dataset only with FetchAll=False option. But this should not lead to appearing connect dialog.

It doesn't, so apparently everything works OK. The fact that new connections are spawned just took me by surprise -- I never said that new login dialogs appear, but rather that I was able to observe new connections being created.

This was an issue because the ad-hoc SQL statements were run pretty often, so on a batch run the code would open and close connections so rapidly that it seemed to exhaust the network stack on the computer.

As it stands, the fault was in our code, so I apologise for the false alarm and thank you for your kind reply.