It is possible to work with a DB on a shared hosting

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
xxxFALKORxxx
Posts: 4
Joined: Fri 20 Jul 2018 17:42

It is possible to work with a DB on a shared hosting

Post by xxxFALKORxxx » Wed 09 Dec 2020 14:08

Hi;

im trying to work with a DB that resides on a shared hosting, buy i just got a lot of Lost connection to MySQL server messages, i don't know what to do.

Im using Delphi 7 and MyDAC 7.6.12

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: It is possible to work with a DB on a shared hosting

Post by ViktorV » Mon 14 Dec 2020 11:00

Yes, using MyDAC you will be able to connect and work both to a local MySQL server and to a remote one.
A connection to MySQL server can be lost due to connection activity timeout specified in the system variable wait_timeout of MySQL server.
Also, the connection can be lost when inserting large data due to server settings.
To load big data you need to increase the server variable value max_allowed_packet in the my.ini file and restart the server.
For example:
max_allowed_packet = 16M
More details here:
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

To restore a lost connection you need to use the event handler TMyConnection.OnConnectionLost. To enable the OnConnectionLost event handler set the property TMyConnection.Options.LocalFailover to True. Please note to use the OnConnectionLost handler you need to add the MemData unit to the USES section of your unit. An example of using OnConnectionLost:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin						
  MyConnection1.Options.LocalFailover := True;
  MyConnection1.Open;
end;
	
procedure TForm1.MyConnection1ConnectionLost(Sender: TObject;
Component: TComponent; ConnLostCause: TConnLostCause;
var RetryMode: TRetryMode);
begin			
  RetryMode := rmReconnectExecute;
end;
In this case when connection is lost MyDAC will try to reconnect and restart the failed operation. Read more in MyDAC documentation: https://www.devart.com/mydac/docs/Devar ... onLost.htm
The OnConnectionLost event occurs only when the following conditions are fulfilled:
- a fatal error occurs;
- there are no opened transactions in a connection that are not ReadOnlyReadCommitted (if connection has at least one opened transaction, which is not ReadCommitedReadOnly, FailOver does not execute. All ReadCommitedReadOnly transaction are restored with FailOver operation);
- there are no opened and non-fetched datasets;
- there are no explicitly prepared datasets or SQLs.
Please make sure that none of the conditions above is violated.

MyDAC does not automatically initiate checking connection to the server. Therefore, after successfully connecting to the server if the connection to the server disconnects the TMyConnection.Connected property will be set to True and the OnConnectionLost event will not be initiated until there is an attempt to connect to the server.
To solve your task you can use the TMyConnection.Ping method. This method is used to test the connection to MySQL server if the connection was disconnected, there will be an error and an attempt to reconnect to the server.
As well this method can be used in order for the server not to close a connection due to timeout connection activity. In this case, you can use TMyConnection.Ping as follows - call it from the timer and set the interval timer to a value less than wait_timeout of the server (so that the intervals between commands receiving by the server do not exceed wait_timeout).

Post Reply