Firebird 2.5 query returns COLLATION UTF8_CI_AI_NUMERIC_SORT for CHARACTER SET UTF8 is not installed

Question

I have an old source database in which apparently custom collation UTF8_CI_AI_NUMERIC_SORT was created. I'm running it on docker via image jacobalberty/firebird:2.5-ss. Originally database was created on a Windows machine.

When I try to do a query on the table where this collation was used, I get the error:

SQL> select * from "InvoiceService";
Statement failed, SQLSTATE = 22021
COLLATION UTF8_CI_AI_NUMERIC_SORT for CHARACTER SET UTF8 is not installed

Show collations returns the following:

SQL> show collations;
UTF8_CI_AI_NUMERIC_SORT, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), CASE INSENSITIVE, ACCENT INSENSITIVE, 'NUMERIC-SORT=1'

I tried the following fixes:

1. add entry to fbintl.conf:

<charset UTF8>
    intl_module fbintl
    collation   UTF8_CI_AI_NUMERIC_SORT
</charset>

Then run the sp_register_character_set("UTF8", 4) procedure, and receiving error about duplicate collations (because UTF8_CI_AI_NUMERIC_SORT is already defined in the DB).

2. Dropping collation

SQL> drop collation UTF8_CI_AI_NUMERIC_SORT;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-Collation UTF8_CI_AI_NUMERIC_SORT is used in table InvoiceService (field name NAME) and cannot be dropped

3. Adding new column in which different collation would be used, but can't even add it:

SQL> ALTER TABLE "InvoiceService" ADD NAME2 VARCHAR(600) CHARACTER SET UTF8;
Statement failed, SQLSTATE = 22021
unsuccessful metadata update
-InvoiceService
-COLLATION UTF8_CI_AI_NUMERIC_SORT for CHARACTER SET UTF8 is not installed

4. With using gbak restoring only metadata, fixing the schema and then inserting only the data, but gbak does not support restoring data only

...

I'm out of ideas now. What else could I try?

Answer 1

So, I finally managed to solve the problem. What I did was to create a DB backup with

gbak -v -t -user SYSDBA /path/to/source.fdb /path/to/backup.fbk

Then use the 3.0 version of Docker image with Firebird DB (jacobalberty/firebird:3.0) and restore from backup with

gbak -create /path/to/backup.fbk /path/to/restored3.fdb

Note that the same backup-restore procedure without switching the Docker image did not work.

I didn't have to do anything else. There's only a slight difference in SHOW COLLATIONS; output:

// originally:
UTF8_CI_AI_NUMERIC_SORT, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), CASE INSENSITIVE, ACCENT INSENSITIVE, 'NUMERIC-SORT=1'

// restored DB
UTF8_CI_AI_NUMERIC_SORT, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), CASE INSENSITIVE, ACCENT INSENSITIVE, 'COLL-VERSION=58.0.6.50;NUMERIC-SORT=1'