Delete is not commiting changes to database (MySQL)

The stable tester's forum for ZeosLib 7.0.x series

Report problems concerning our Delphi 2009+ version and new Zeoslib 7.0 features here.
lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Delete is not commiting changes to database (MySQL)

Postby lsrzj » 26.07.2013, 16:39

I'm trying to delete data from two tables that have a master-detail relationship between them. Even with the command to ApplyUpdates and CommitUpdates for the detail query object, it's only deleting the record inside the dataset without, actually commiting it to the database. When I try to delete the master table it fails because of foreign key restriction not being obeyed. Am I missing something here? The MySQL version I'm using is 5.6 with libmysql from this version copied from Workbench's directory to c:\windows\system folder. Zeos version is 7.0.4

if not ZQDetail.IsEmpty then
begin
ZQDetail.Delete;
ZQDetail.ApplyUpdates;
ZQDetail.CommitUpdates;

ZQMaster.Delete;
ZQMaster.ApplyUpdates;
ZQMaster.CommitUpdates;
end;

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 26.07.2013, 17:04

lsrzj,

f not ZQDetail.IsEmpty then
begin
ZQDetail.Delete;
ZQDetail.ApplyUpdates;
//ZQDetail.CommitUpdates;
ZQDetail.Connection.Commit;

ZQMaster.Delete;
ZQMaster.ApplyUpdates;
//ZQMaster.CommitUpdates;
ZQMaster.Connection.Commit;
end;

http://zeosbugs.firmos.at/view.php?id=206

And i've also added a TZDataSet.Options:

A test example i've adde:

Code: Select all

{**
  Runs a test for in extendet clientdatset rules
  All detail-queries should be updated in a single transaction.
  But now the MasterTable should be updated first for an valid ForegnKey.
  Then all DetailTables should have been updated.
  Very tricky and has to deal with MetaData informations.
}
procedure TZTestMasterDetailCaseMBCs.TestClientDatasetWithForeignKey_doUpdateMasterFirst;
var
  SQLMonitor: TZSQLMonitor;
  //CommitCount, I: Integer;
begin
  SQLMonitor := TZSQLMonitor.Create(nil);
  SQLMonitor.Active := True;
  MasterQuery.SQL.Text := 'SELECT * FROM department ORDER BY dep_id';
  MasterQuery.Options := MasterQuery.Options + [doDontSortOnPost];
  MasterQuery.Open;

  CheckStringFieldType(MasterQuery.FieldByName('dep_name').DataType, Connection.DbcConnection.GetConSettings);
  CheckStringFieldType(MasterQuery.FieldByName('dep_shname').DataType, Connection.DbcConnection.GetConSettings);
    //ASA curiousity: if NCHAR and VARCHAR fields set to UTF8-CodePage we get the LONG_Char types as fieldTypes for !some! fields
  if StartsWith(Protocol, 'ASA') and ( Connection.DbcConnection.GetConSettings.ClientCodePage^.CP = 65001 ) then
    CheckMemoFieldType(MasterQuery.FieldByName('dep_address').DataType, Connection.DbcConnection.GetConSettings)
  else
    CheckStringFieldType(MasterQuery.FieldByName('dep_address').DataType, Connection.DbcConnection.GetConSettings);

  DetailQuery.SQL.Text := 'SELECT * FROM people';
  DetailQuery.MasterSource := MasterDataSource;
  DetailQuery.MasterFields := 'dep_id';
  DetailQuery.LinkedFields := 'p_dep_id';
  DetailQuery.Options := DetailQuery.Options + [doUpdateMasterFirst, doDontSortOnPost];
  DetailQuery.Open;
  //CommitCount := 0;
  try
    MasterQuery.Append;
    MasterQuery.FieldByName('dep_id').AsInteger := TestRowID;
    MasterQuery.FieldByName('dep_name').AsString := GetDBTestString('aaaa', Connection.DbcConnection.GetConSettings);
    MasterQuery.FieldByName('dep_shname').AsString := 'abc';
    MasterQuery.FieldByName('dep_address').AsString := GetDBTestString('A adress of aaaa', Connection.DbcConnection.GetConSettings);

    CheckEquals(True, (MasterQuery.State = dsInsert), 'MasterQuery Insert-State');

    DetailQuery.Append;
    DetailQuery.FieldByName('p_id').AsInteger := TestRowID;
    DetailQuery.FieldByName('p_dep_id').AsInteger := TestRowID;

    DetailQuery.FieldByName('p_begin_work').AsDateTime := now;
    DetailQuery.FieldByName('p_end_work').AsDateTime := now;
    DetailQuery.FieldByName('p_picture').AsString := '';
    DetailQuery.FieldByName('p_resume').AsString := '';
    DetailQuery.FieldByName('p_redundant').AsInteger := 5;
    CheckEquals(True, (DetailQuery.State = dsInsert), 'MasterQuery Insert-State');

    MasterQuery.Post;

    CheckEquals(True, (MasterQuery.State = dsBrowse), 'MasterQuery Browse-State');
    CheckEquals(True, (DetailQuery.State = dsBrowse), 'DetailQuery Browse-State');

    {fix it
    for i := 0 to SQLMonitor.TraceCount -1 do
      if SQLMonitor.TraceList[i].Category = lcTransaction then
        if Pos('COMMIT', UpperCase(SQLMonitor.TraceList[i].Message)) > 0 then
          Inc(CommitCount);
    CheckEquals(1, CommitCount, 'CommitCount'); }
  finally
    MasterQuery.SQL.Text := 'delete from people where p_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    MasterQuery.SQL.Text := 'delete from department where dep_id = '+IntToStr(TestRowID);
    MasterQuery.ExecSQL;
    SQLMonitor.Free;
  end;
end;
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 26.07.2013, 17:49

EgonHugeist, thanks for your quick reply!

I was using AutoCommit option from ZConnection and the code you suggested gave an error telling that QueryObject.Connection.Commit is an invalid operation. I tried turning AutoCommit off and tried your code and, even so, the changes were not applied to the database itself, only inside the dataset.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 26.07.2013, 18:02

lsrzj,

Which TransactionIsolationLevel are you using if AutoCommit = False. Use tiReadCommited.

Did you check the CachedUpdates Option on TZQuery? It should be true if you're working with applyupdates.

Hope you didn't setup MySQL with something equal like the "BlackHole" engine?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 26.07.2013, 21:20

EgonHugeist,

I left the MySQL defaults, as I read in it's documentation, it's Repeatable Read, so I configured ZConnection to match MySQL defaults tiRepeatableReads. CachedUpdates is turned on. I tried with and without AutoCommit, tried with and without CachedUpdates, tried with tiReadCommited.... nothing changes, same error. The only thing that differs is the moment of the error, if I'm not using CachedUpdates, when I try to delete the master record with ZQMaster.Delete the error occurs, while if CachedUpdates is turned on the error occurs when ZQMaster.ApplyUpdates is issued.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`hercules`.`detail`, CONSTRAINT `FK_A6085E0491BDF8EE` FOREIGN KEY (`MasterID`) REFERENCES `Master` (`Id`)) 0.688 sec


EDIT:
I made a mini application to test my own code and it had only one button to delete the record. So I put new Query Objects instead of copying and pasting and the error stopped occurring, but no matter what I do, the deletion is only being seen inside the dataset not in the database. Now I remembered that I upgraded from 7.0.3 to 7.0.4 without putting all the query objects again on my DataModule, maybe it's the root cause of many errors. I'll keep you in touch!

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 26.07.2013, 23:26

lsrzj,

Uff i can't believe this is a difference between 7.03 and 7.0.4. So replacing your components won't help.

I'll wait for replay or a template app which points me to this issue.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 27.07.2013, 00:35

EgonHugeist,

Well I discovered the reason for the problem. I zeroed my database and then it worked, changes went to the database, well hard to tell why, but solved. The FK Constraint problem is a mistake I made in the execution order. I'm syncing the master table in the AfterScroll event of the detail one using the FK value of my detail table as the locate for the ID field of the master table, so when I delete and ApplyUpdates on the detail table, it scrolls to the prior record and the locate goes to the wrong record on the master table. Well a newbe error hahaha, sorry. So I'd like to ask you for a suggestion as how could I point to the correct record at the master table after the detail one had a record deleted. Thanks for your patience and for your fast answers!

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 27.07.2013, 19:18

lsrzj,

So I'd like to ask you for a suggestion as how could I point to the correct record at the master table after the detail one had a record deleted.

Doesn't it happen automatically?

Hint: MasterQuery.Options := MasterQuery.Options + [doDontSortOnPost];

Maybe i do understand you wrong? Can you ask a little bit more precise, please?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 29.07.2013, 22:20

Well, I've never used those options because I never understood them well, didn't find any documentation about. I know that there is a way to link master and detail tables with MasterSource, LinkedFields and MasterFields properties in a way that one stays in sync with the other, but I've already heard that using this degrade performance on large recordsets so I'm doing it manually. Is it true that using this resource degrades performance?

Currently the code I'm using:

procedure Detail.AfterScroll;
begin
Master.Locate('Id', DetailMasterId, [])
end;

when I do a Detail.Delete it does a Scroll so the locate points to the wrong record on the master to be deleted.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 30.07.2013, 21:09

lsrzj,

MasterDetail relations do allways degrade the performance. BUT Zeos has it's own filters and locate mechanisms so no DataBase reload is required. Propose you check my suggestion in the code above and see what happens.

To your code: Isn't it a bid confusing if the detail table scolls to relocate the Master? Scrolling after a delete is what we want -> we miss a row. if you've x:n (Master(unique id) and N-detail id's) you should have succes but how would you solve this i no more detail relation exists (detail_id = null because there is no more row left)?
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 30.07.2013, 21:17

EgonHugeist,

How I have to setup my query objects to accomplish what you suggested, what I have to put in what property? My concern is with memory usage on large datasets making the application on the client side very slow or overloading the server. I'm looking forward, too, to make query result pagination in the case of returning a large number of rows from a query. Where do I find documentation about TZDataSet options(doUpdateMasterFirst, doCalcDefaults, doDontSortOnPost, doPreferPrepared, doOemTranslate, doAlwaysDetailResync, doSmartOpen)? And how to use them?

Thanks!

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 31.07.2013, 22:45

lsrzj,

doUpdateMasterFirst:
i made this option. It means all detail tables do register with the master and vice versa. I do determine which one is the master and this table updates first. Than all detail tables will be updated. This is very usefull if you've constraints.

doCalcDefaults:
Should be default. We try to load and handle the default values your table definition does contain for the fields.

doDontSortOnPost:
isn't it selve explainatory? If you use sorted fields than this option avoids refreshing the roworder. So your cursor doesn't leave his position.

doPreferPrepared:
This activates RealPrepared statments. Since 7.1 the most protocols do support such Statements by default except DBLIB (had not the time to write such one yet). This should speed up Parameter handling since the Servers do Prepare the queries and we (in most cases) do no longer need to escape the strings and binaries.
This option is mostly for MySQL since the didn't fix there know issues from MySQL4 up to 5.6.

doOemTranslate:
convert the chars to oem format

doAlwaysDetailResync:
Change the rowno in Master table reloads the Detail-tables fully.

doSmartOpen:
Not sure yet
Should make it possible to open a table whitout beeing connected?

I don't know everyting about the Zeos wonderland just the things i was working on. So forgive me if i'm wrong.. );
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

lsrzj
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 26.07.2013, 16:30

Postby lsrzj » 06.08.2013, 03:07

EgonHugeist,

Thanks for your help. I need a code snippet that uses doUpdateMasterFirst. How does it work? If I put the detail table in edit or insert mode the master table will too be put on those modes automatically? To this work properly I have to fill MasterSource with the the Master's DataSource, MasterFields with master's PK and LinkedFields with Detail's FK?

The FetchRow property, do you know if it works with query result pagination? Example, I have 1000 rows on a query result, if I put FetchRow to 100, will it fetch 100 rows at a time?

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 11.08.2013, 11:41

lsrzj,

The snippet you need i've already attached. See abouve.
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image


Return to “ZeosLib 7.0 Stable Forum”

Who is online

Users browsing this forum: No registered users and 1 guest