Devart Forums

Hello

I have an application that needs to populate a database with a large number of entries.

I use the following code, which is called many thousands of times

s = "INSERT INTO KEYTAB (KEYID, KEYPARENT, HIVEID, HIVEDATE, KEYTAG, KEYNAME, PATH, KEYLASTMODIFIED, VALS, FIRTSVALUE, SUBKEYS, PARENTPATH) VALUES(";
s += IntToStr(ID) + "," + IntToStr(ParentId) + "," + IntToStr(HiveId) + ",'" + HiveDate + "'," + IntToStr(KeyTag) + ",'" + Name + "','" + Path + "'," + KeyLastModified + "," + IntToStr(Vals) + "," + IntToStr(99) + "," + IntToStr(SubKeys) + ",'" + Parent + "')";
ComQuery->SQL->Clear();
ComQuery->SQL->Add(s);
ComQuery->Execute();

After inserting about 4000 records I get an error from BDS2006 ‘can’t connect to MySQL server on 127.0.0.1

I check the database at the command prompt and all records have been inserted correctly.

anyone have any ideas why the connection is being dropped?

sandy771 wrote:Hello

I have an application that needs to populate a database with a large number of entries.

I use the following code, which is called many thousands of times

s = "INSERT INTO KEYTAB (KEYID, KEYPARENT, HIVEID, HIVEDATE, KEYTAG, KEYNAME, PATH, KEYLASTMODIFIED, VALS, FIRTSVALUE, SUBKEYS, PARENTPATH) VALUES(";
s += IntToStr(ID) + "," + IntToStr(ParentId) + "," + IntToStr(HiveId) + ",'" + HiveDate + "'," + IntToStr(KeyTag) + ",'" + Name + "','" + Path + "'," + KeyLastModified + "," + IntToStr(Vals) + "," + IntToStr(99) + "," + IntToStr(SubKeys) + ",'" + Parent + "')";
ComQuery->SQL->Clear();
ComQuery->SQL->Add(s);
ComQuery->Execute();

After inserting about 4000 records I get an error from BDS2006 ‘can’t connect to MySQL server on 127.0.0.1

I check the database at the command prompt and all records have been inserted correctly.

anyone have any ideas why the connection is being dropped?

Try the fix in Windows Registry

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"MaxUserPort"=dword:0000fffe
"TcpTimedWaitDelay"=dword:0000001e

This changes are done on the Client or Server Side ?

looks like your problem is the number of packets mysql allows per query. This option (max_allowed_packet) can be found on my.ini file.

When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.

By default this value is 1M, so try increasing to 16MB for example.

At last, try to avoid MyQuery for large queries. Use MyLoader instead. Also dont forget to set Connection.Options.Compression to true, to get the maximum performance possible.

sandy771 wrote:After inserting about 4000 records I get an error from BDS2006 ‘can’t connect to MySQL server on 127.0.0.1
I check the database at the command prompt and all records have been inserted correctly.
anyone have any ideas why the connection is being dropped?

Probably the problem you have encountered is related to your MySQL user settings, like MAX_CONNECTIONS_PER_HOUR (if you set the DisconnectedMode option to True), MAX_QUERIES_PER_HOUR, etc. It is pretty hard to determine what is the reason of this problem without additional information about what settings of MyDAC components did you change, what are settings of your MySQL server in the my.ini file, and your MySQL user.

ben wrote:At last, try to avoid MyQuery for large queries. Use MyLoader instead. Also dont forget to set Connection.Options.Compression to true, to get the maximum performance possible.

Yes, usage of the TMyLoader component looks reasonable in this case.