When repairing causes error - 'Lost Connection to Mysql server During query' - An idea?

Question

Follows the code:

ZQuery1.Close;
      ZQuery1.SQL.Clear;
      ZQuery1.SQL.Add('SHOW TABLES FROM '+zConnRepara.Database);
      ZQuery1.Open;
      ZQuery1.First;
      Progressbar1.Max := ZQuery1.RecordCount;

      lTemp := '';
      Parametro := '';
      //nomeDoDB'.'db.table := db.db.table
      Parametro := zConnRepara.Database + '.' +
        ZQuery1.FieldByName('Tables_in_' +
          zConnRepara.Database).asString;

      if ZQuery1.RecordCount > 1 then
        ZQuery1.Next;
      //check
      while not ZQuery1.Eof do
      begin
        Parametro := Parametro + ',' + zConnRepara.Database + '.' +
        ZQuery1.FieldByName('Tables_in_' +
        zConnRepara.Database).asString;

        ZQuery1.Next;
      end;
      ZQuery2.Close;
      ZQuery2.SQL.Clear;
      ZQuery2.Sql.Text := 'Check table ' + Parametro;
      ZQuery2.Open;
      CloseQuery;

      if ZQuery2.RecordCount > 1 then
        ZQuery2.Next;
      lTemp := '';
      //ProgressBar1.Position := 0;
      //repair
      while not ZQuery2.Eof do
      begin
        try
          status := ZQuery2.FieldByName('Msg_text').AsString;
          if (status <> 'OK') and (status = 'Corrupt') then
          begin
            zConnRepara.Connected := true;
            inc(i);
            ZQuery3.Close;
            ZQuery3.SQL.Clear;
            ZQuery3.SQL.Add('Repair table '+ Parametro);
            ZQuery3.ExecSQL;
            lTemp := lTemp + ',' + ZQuery2.FieldByName(
              'Table').AsString;
            StatusBar1.Panels[0].Text := 'Tabela sendo reparada: '+lTemp;
            zConnRepara.Connected := false;
          end;
        except
          on e: Exception do ShowMessage(''+e.Message);
        end;

my question, is that the code is correct, and even so gives this error, could you help me? my logic is correct?

Answer 1

If your logic is right I don’t know, because I don’t know what you want to do, but your while is in an infinite loop, since you’re not giving a zQuery2.Next;.

I believe your correct loop would be:

while not ZQuery2.Eof do
  begin
    try
      status := ZQuery2.FieldByName('Msg_text').AsString;
      if (status <> 'OK') and (status = 'Corrupt') then
      begin
        zConnRepara.Connected := true;
        inc(i);
        ZQuery3.Close;
        ZQuery3.SQL.Clear;
        ZQuery3.SQL.Add('Repair table '+ Parametro);
        ZQuery3.ExecSQL;
        lTemp := lTemp + ',' + ZQuery2.FieldByName(
          'Table').AsString;
        StatusBar1.Panels[0].Text := 'Tabela sendo reparada: '+lTemp;
        zConnRepara.Connected := false;
      end;
      //Se você não fizer um zQuery2.next aqui você nunca chegará no EOF.
      zQuery2.next;
    except
      on e: Exception do ShowMessage(''+e.Message);
    end;

Another thing: You don’t have to open and close zConnRe all the time, just set Keepconnection = false that the zQuery component will open and close for you.

Other Other Thing: Switch to clientDataSet.

EDIT:

If the code is really incomplete as mentioned in the comments, it is very likely that you are spending too much time with the same open connection doing loops and business logic, things that do not need connection to the bank. This can certainly cause a Time-out Connection.

The ideal is to recover the data, close the connection, work with them in cache and finally open the connection, persist the data and close the connection. Leaving the keepConnection = false relieves you from opening and closing the connection all the time.

If even with the keepconnection = false the problem persists so I believe that there is some consultation taking longer than the time-out of your bank, leaving only two options:

1. Review your consultation
2. Increase the time-out of the bank