Dropping table fails with 'unsuccessful metadata update' when using embedded Firebird

Question

My code does the following:

1. Set up a design time target TFDConnection
2. Create a run time source TFDConnection
3. Drop all indices and tables in target
4. Recreate those
5. Copy all data from source to target
6. Drop one of those tables (and indices), named TT_SYS_WEEKS
7. Recreate it and fill it

The TFDConnections can be embedded Firebird or not. This works fine in all combinations except when both are embedded.

In step 6, after executing

DROP INDEX <OWNER>TT_I1_SYS_WEEKS
ALTER TABLE <OWNER>TT_SYS_WEEKS DROP CONSTRAINT TT_I0_SYS_WEEKS

the statement

DROP TABLE TT_SYS_WEEKS

fails with [FireDAC][FB][Phys]unsuccesful metadata update Table TT_SYS_WEEKS already exists.

The exact same operations of dropping and creating tables/indices were already performed in steps 3,4,5. TT_SYS_WEEKS is not the last table that was copied.

The design time target connection and its TFDPhysFBDriverLink are set up as follows:

AConnection.TxOptions.AutoCommit := true;
AFDPhysDriverLink.DriverID := 'FBEmbeddedBase';  // JD 28-3-2018
AFDPhysDriverLink.VendorLib := 'fbembed.dll';  // 32-bits only
AConnection.Params.DriverID := 'FBEmbeddedBase'; // AConnection
AConnection.Params.Database := 'full GDB file';
SetFireBirdMapRules(AConnection); // Some mapping rules
AConnection.UpdateOptions.LockWait := False;
AConnection.UpdateOptions.LockMode := lmNone;

The runtime source connection and TFDPhysFBDriverLink are set up as follows:

// Create 'own' TFDPhysFBDriverLink for embedded connection
// https://stackoverflow.com/questions/46691699/setting-up-a-second-tfdphysfbdriverlink-possible-and-necessary
lDriverLink := TFDPhysFBDriverLink.Create(Application);
lDriverLink.DriverID := 'FBEmbedded';
lDriverLink.VendorLib := 'fbembed.dll';  // 32-bits embedded
LRestoreDB := TFDConnection.Create(Application);
LRestoreDB.UpdateOptions.RequestLive     := false; 
LRestoreDB.ResourceOptions.AutoReconnect := true;
LRestoreDB.Params.DriverID := lDriverLink.DriverID; 
with LRestoreDB do
begin
   Params.Database := AFBFileName;
   Params.UserName := '***';
   Params.Password := '***';
   LoginPrompt := False;
   // ResourceOptions.KeepConnection is default true
   FetchOptions.Mode := fmAll;
end;
SetFireBirdMapRules(LRestoreDB); // Some mapping rules

What can be going on?
Anything else I can investigate?

Other background info:

• Data was copied to the target database with parameterized INSERT queries, for many tables. A transaction with explicit commit was around each table transfer.
• In the table copy operations TxOptions.AutoCommit is true for the target database
• Delphi Tokyo 10.2.3 Win32 app, Firebird 2.5.3.25778 Win32
• This user had an issue with CREATE following DROP. In the answer, Mark writes the use of execute statement adds additional locks iirc which conflict with a subsequent DDL for the same table name. That is PSQL under Firebird 2.1, does not mention embedded, and I don't have the deadlock error.

Answer 1

You need to perform a commit after step 4, 5 and 6 (and - of course - 7). Some of the DDL in Firebird is only really executed on commit, so if you run everything in one transaction, you won't have actually dropped and recreated the indexes yet in steps 3 and 4, the table drop in step 6 might be blocked by the earlier DML in step 5, and the table drop in step 6 will not have been executed yet when you try to recreate it in step 7.