When repairing cause error - 'Lost connection to MySQL server during query' - Aguma Idea?

Question

When repairing cause error - 'Lost connection to MySQL server during query' - Aguma Idea?

Navigation

#1 by (1 votes)

0

Follow 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 still gives this error, could you help me? Is my logic correct?

mysql delphi

asked by anonymous 13.08.2014 / 13:41

1 answer

Conversion from NSString to NSDictionary always null Web API - How to validate the model from DataAnnotation.Remote ()?

score 1 · Accepted Answer

If your logic is right I do not know, because I do not know what you want to do, but your while is in an infinite loop, since you are 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: Do not need to open and close zConnRepara all the time, just keep the KeepConnection = false the zQuery component will open and close for you.

Another Other Thing: Switch to using clientDataSet.

EDIT:

If the code is really incomplete as mentioned in the comments, it's very likely that you're staying too long with the same open connection by doing loops and business logic , things that do not need to connect to the bank. This can certainly cause a connection Time-out .

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 keepConnection = false relieves you from opening and closing the connection all the time.

If the persistence problem persists then I believe there is some query taking longer than the timeout of your bank, leaving only two options:

Review your query

Increase bank time-out