CODEWITHSUNDEEP
firebirdfirebird2.5symmetricds
Jacky
Jacky

Reputation: 154

Firebird errors when using Symmetricds

I am using symmetricds free version to replicate my firebird database. When I demo by creating new (blank) DB, it worked fine. But when I config on my existing DB (have data), error occurred.

enter image description here

I use Firebird-2.5.5.26952_0 32bit & symmetric-server-3.9.5, OS is Windows Server 2008 Enterprise.

I had searched for whole day but found nothing to solve this. Anyone please help. Thank for your time.

UPDATE: When initial load, Symmetric execute the statement to declare UDF in firebird DB:

declare external function sym_hex blob   
  returns cstring(32660) free_it 
  entry_point 'sym_hex' module_name 'sym_udf

It caused error because my existing DB charset is UNICODE_FSS, max length of CSTRING is 10922. When I work around by updating charset to NONE, it worked fine. But it is not a safe solution. Still working to find better.

One more thing, anyone know others open source tool to replicate Firebird database, I tried many in here and only Symmetric work.

Upvotes: 0

Views: 562

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108997

The problem seems to be a bug in Firebird where the length of CSTRING is supposed to be in bytes, but in reality it uses characters. Your database seems to have UTF8 (or UNICODE_FSS) as its default character set, which means each character can take up to 4 bytes (3 for UNICODE_FSS). The maximum length of CSTRING is 32767 bytes, but if it calculates in characters for CSTRING, that suddenly reduces the maximum to 8191 characters (or 32764 bytes) (or 10922 characters, 32766 bytes for UNICODE_FSS).

The workaround to this problem would be to create a database with a different default character set. Alternatively, you could (temporarily) alter the default character set:

For Firebird 3:

  1. Set the default character set to a single byte character set (eg NONE). Use of NONE is preferable to avoid unintended transliteration issues

    alter database set default character set NONE;
  2. Disconnect (important, you may need to disconnect all current connections because of metadata caching!)
  3. Set up SymmetricDS so it creates the UDF

  4. Set the default character set back to UTF8 (or UNICODE_FSS)

    alter database set default character set UTF8;
  5. Disconnect again

When using Firebird 2.5 or earlier, you will need perform a direct system table update (which is no longer possible in Firebird 3) using:

Step 2:

update RDB$DATABASE set RDB$CHARACTER_SET_NAME = 'NONE'

Step 4:

update RDB$DATABASE set RDB$CHARACTER_SET_NAME = 'UTF8'

The alternative would be for SymmetricDS to change its initialization to

DECLARE EXTERNAL FUNCTION SYM_HEX
    BLOB
    RETURNS CSTRING(32660) CHARACTER SET NONE FREE_IT
    ENTRY_POINT 'sym_hex' MODULE_NAME 'sym_udf';

Or maybe character set BINARY instead of NONE, as that seems closer to the intent of the UDF.

Upvotes: 1

Related Questions