MyQuery +UniDirectional

[meszarosistvan]

Hi!

I use UniDirectional dataset to keep the memory usage low, but I have founed a problem:

1:Build a table

DROP TABLE IF EXISTS `cube_database`.`cube`;
CREATE TABLE `cube_database`.`cube` (
`D_0` date NOT NULL,
`L_1` int(11) NOT NULL,
`L_2` int(11) NOT NULL,
`L_3` int(11) NOT NULL,
`VALUE_0` double NOT NULL,
`VALUE_1` double NOT NULL,
`VALUE_2` double NOT NULL,
`VALUE_3` double NOT NULL,
KEY `CUBE_D_0` (`D_0`),
KEY `CUBE_L_1` (`L_1`),
KEY `CUBE_L_2` (`L_2`),
KEY `CUBE_L_3` (`L_3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2: Try this code:

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
MyConnection.Connected:=True;

MyQuery.Free;
MyQuery:=TMyQuery.Create(Application);
MyQuery.Connection:=MyConnection;
MyQuery.UniDirectional:=True; // // it and it will work!

MyQuery.SQL.Add('select D_0 from CUBE where D_0>=:ACTUAL_DATE order by D_0');
MyQuery.Params.ParamByName('ACTUAL_DATE').AsDate:=EncodeDate(1998,01,01);
MyQuery.Open;

YearOf(MyQuery.Fields[0].AsDateTime);

MyQuery.Close;
end;

and you have an exception: connection lost to database when MyQuery.Close...

[Antaeus]

We could not reproduce the problem using the information you have posted.
Please supply us following information
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MyDAC. You can see it in About sheet of TMyConnection Editor
- Exact version of MySQL server and MySQL client. You can see it in Info sheet of TMyConnection Editor
- Script to fill table with data necessary to reproduce the problem. According to your code, this should be just a couple of records.

[meszarosistvan]

Hi!

It is very, very interesting, please try this:

Create the table..

DROP TABLE IF EXISTS `cube_database`.`cube`;
CREATE TABLE `cube_database`.`cube` (
`D_0` date NOT NULL,
`L_1` int(11) NOT NULL,
`L_2` int(11) NOT NULL,
`L_3` int(11) NOT NULL,
`VALUE_0` double NOT NULL,
`VALUE_1` double NOT NULL,
`VALUE_2` double NOT NULL,
`VALUE_3` double NOT NULL,
KEY `CUBE_D_0` (`D_0`),
KEY `CUBE_L_1` (`L_1`),
KEY `CUBE_L_2` (`L_2`),
KEY `CUBE_L_3` (`L_3`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Try this code

procedure TForm1.BitBtn1Click(Sender: TObject);
var nN:Integer;
begin
MyConnection.Connected:=True;

RandSeed:=0;

for nN:=0 to 100000 do
begin
MyQuery.Free;
MyQuery:=TMyQuery.Create(Application);
MyQuery.Connection:=MyConnection;
MyQuery.UniDirectional:=True;

MyQuery.SQL.Add('select D_0 from CUBE where D_0>=:ACTUAL_DATE order by D_0');
MyQuery.Params.ParamByName('ACTUAL_DATE').AsDate:=EncodeDate(1900+Random(1000),01,01);
MyQuery.Open;

//while MyQuery.Eof=False do
//begin
YearOf(MyQuery.Fields[0].AsDateTime);
// MyQuery.Next;
//end;

MyQuery.Close;
end;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
var nN:Integer;
begin
MyConnection.Connected:=True;

MyCommand1.SQL.Add('insert into CUBE');
MyCommand1.SQL.Add('(D_0,L_1,L_2,L_3,VALUE_0,VALUE_1,VALUE_2,VALUE_3)');
MyCommand1.SQL.Add('values');
MyCommand1.SQL.Add('(:D_0,:L_1,:L_2,:L_3,:VALUE_0,:VALUE_1,:VALUE_2,:VALUE_3)');

RandSeed:=0;

for nN:=0 to 100000 do
begin
MyCommand1.Params.ParamByName('D_0').AsDateTime:=EncodeDate(1900,01,01)+Random(1000*365);

MyCommand1.Params.ParamByName('L_1').AsString:=IntToStr(Random(100000));
MyCommand1.Params.ParamByName('L_2').AsString:=IntToStr(Random(100000));
MyCommand1.Params.ParamByName('L_3').AsString:=IntToStr(Random(100000));

MyCommand1.Params.ParamByName('VALUE_0').AsInteger:=Random(100000);
MyCommand1.Params.ParamByName('VALUE_1').AsInteger:=Random(100000);
MyCommand1.Params.ParamByName('VALUE_2').AsInteger:=Random(100000);
MyCommand1.Params.ParamByName('VALUE_3').AsInteger:=Random(100000);

MyCommand1.Execute;
end;

MyConnection.Commit;
end;

Press BitBtn2 first, and you will get

---------------------------
Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EMySqlException with message 'Lost connection to MySQL server during query'. Process stopped. Use Step or Run to continue.
---------------------------
OK Help
---------------------------

when MyQuery.Close (I hope).

It may be MySQL error, but please check it.....
At the first run it works without problem, but now the first .Close
fail, so this is interesting....

My Delphi: Delphi 6 version 6.240 update pack 2
MyDAC: 4.40.0.18
MySQL Version: mysql-5.0.26-win32.zip

Best regards.

[meszarosistvan]

Hi! I have something too.....

---------------------------
Project1
---------------------------
Can't connect to MySQL server on 'localhost' (10061)

Socket error on connect. WSAGetLastError return 10048($2740)
---------------------------
OK
---------------------------

[Antaeus]

This is the problem of MySQL server. In the UniDirectional mode MyDAC creates additional connection to the server to prevent blocking of main connection. So, on the every query execution in UniDirectional mode a new connection to the server is established. In your code the query execution is performed very often. After some thousands of connecting/disconnecting sever stops to respond. You can easy reproduce this problem with following code:

Code: Select all

  for nN:=0 to 100000 do begin
    MyConnection.Connect;
    MyConnection.Disconnect;
  end;

Starting with the next build of MyDAC you will be able to use Pooling to solve this problem.