mysql - last_insert_id() - sometimes the result is zero

[bbiuser]

Hi,
I'm using Delphi7 with Zeos 7.2.4 and mysql (mysql 5.7, libmariadb.dll 3.0.0.7, same with mysql.dlls) and this is my problem:
I insert rows in my table and sometimes I get 0 as result with the mysql builtin function "last_insert_id()".
There is a pattern: id 1-59 -> ok / id 60-99 -> not ok / id 100 - 599 -> ok / id 600 - 999 -> not ok / id 1000 - 5999 -> ok
(not ok -> result is 0)

I have no idea how this can happen and also with a pattern, totally strange for me. I hope someone can help me.

Table:

Code: Select all

CREATE TABLE `block` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `userid` tinyint(4) NOT NULL DEFAULT '0',
  `blockstatus` tinyint(4) NOT NULL DEFAULT '0',
  `creatorid` mediumint(3) DEFAULT '0',
  `creationdate` datetime DEFAULT '1001-01-01 00:00:00',
  `modifierid` mediumint(3) DEFAULT '0',
  `modifieddate` datetime DEFAULT '1001-01-01 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Example Code:

Code: Select all

procedure TMyForm.SpeedButton1Click(Sender: TObject);
var
  lQuery: TZQuery;
begin
    lQuery:= TZQuery.Create(self);
    try
      lQuery.Connection:= Connection1;
      lQuery.SQL.Text:= 'insert into block(userid, creatorid, creationdate, modifierid, modifieddate)' +
          'values (:userid, :creatorid, now(), :modifierid, now())';
      lQuery.ParamByName('userid').AsInteger:= 1;
      lQuery.ParamByName('creatorid').AsInteger:= 1;
      lQuery.ParamByName('modifierid').AsInteger:= 1;
      lQuery.ExecSQL;
      
      lQuery.SQL.Text:= 'select last_insert_id() as lastid';
      lQuery.Open;
      ShowMessage(IntToStr(lQuery.FieldByName('lastid').AsInteger));
      lQuery.Close;     
    finally
      lQuery.Free;
    end;

Regards
Matthias

[bbiuser]

Update: I updated ZeosLib to version 7.2.10 and it seams to work now.

[marsupilami]

Update: I updated ZeosLib to version 7.2.10 and it seams to work now.

Good to know. Thank you for letting us know