Replication error is raised for CREATE TABLE ... EXTERNAL if the specified disk does not exist on the replica host

[vario-ck]

When publication is used, DDL CREATE TABLE-statements for external tables are replicated to replica. According to documentation, external tables should never be replicated.

These statements raise the following error on replica-side, regardless of whether the path of the external file exists on replica or not.

REPLSERVER (replica) Wed Sep  3 00:07:03 2025
	Database: E:\DATA\V8_PROD.FDB
	ERROR: unsuccessful metadata update
	CREATE TABLE TMP_EXTDB_TEST1122 failed
	A node name is not permitted in a table with external file definition
	At segment 111167, offset 48

To avoid this problem I tried several ways to not replicate DDL-statements. These issues affect all CREATE TABLE statements, not just those for external tables.

1. set exclude_filter in replication.conf to

    exclude_filter = TMP_%

which doesn't work either for CREATE TABLE nor DROP TABLE-statements.
2. use DISABLE PUBLICATION in CREATE TABLE-statement:

CREATE TABLE TMP_EXTDB_TEST123
EXTERNAL 'd:\temp\test123.bin'
(
  FIELD01 CHAR(100) CHARACTER SET WIN1252 COLLATE WIN1252,
  FIELD02 CHAR(100) CHARACTER SET WIN1252 COLLATE WIN1252,
  CRLF CHAR(2) CHARACTER SET WIN1252 COLLATE WIN1252
)
DISABLE PUBLICATION

which also doesn't work for DDL-statements.

As a workaround for using external tables without replicating them I now disable publication for the whole database before CREATE/DROP TABLE and enable it again after creating/dropping the table. When committing this as a whole this doesn't seem to affect other connections.

Checked in FB 4.0.6.3221 and FB 5.0.3.1683 on Win64.

[dyemanov]

When publication is used, DDL CREATE TABLE-statements for external tables are replicated to replica. According to documentation, external tables should never be replicated.

Data of external tables should never be replicated, but DDL statements are replicated regardless.

In your case the replica host obviously does not have disk "D:", thus it's considered being a remote path and error is raised. I can think of two possible solutions:

1. Disable pathname validations during DDL on the replica side. No replication errors will appear after that, but external tables will be unusable on the replica host (due to missing disk "D:").
2. Add a filter (actually, both include and exclude filters) being applied to replicated DDL statements so that you could exclude CREATE TABLE ... EXTERNAL statements altogether.

Perhaps we could disable replication of CREATE TABLE ... EXTERNAL at all (because it relies on the physical file locations), but I'd like to see more opinions before going that way.

[aafemt]

IMHO, as long as replication is supposed to be used for standby scenario, every metadata change should be replicated to make sure that switchover is smooth. It is up to user to create standby host with the same environment as primary host.

Using of dynamic metadata in Firebird is discouraged.

[sim1984]

In this case, the first option seems more logical to me.

We will have a similar problem with tablespaces, but the error cannot be ignored there.

For tablespaces, as well as external tables, we could use relative paths with macro substitutions that are expanded on a specific server, but this is a separate task.

[basid-irk]

3. Add ability create aliases for paths in databases.conf (commented as invalid for now):

# pathname/ = /some/path/       # POSIX
# pathname/ = disk:/some/path/  # Windows
# dbname = pathname/filename    # any system

or (in DDL):

... 'pathname/somefile' ...

P.S.
RDB uses separate file, but Okkam blade...

[vario-ck]

dyemanow, please excuse my mistake. Your assumption about the non-existent path to the external table file is correct.

I think the best solution would be not to check the path on the replica side.
This would also ensure that the replica database can be easily restored to the primary.

Path aliases would also be a good solution, but could lead to attempts to access the same files for the external tables on the replica side, which would then trigger access violations on the file.

[basid-irk]

(Non-)existent "real path" and "aliased path" are identical (in)accesible: both refer to (non-)existent FS-location.