Firebird CHARACTER SET UTF8 is not defined

Question

I am attempting to connect to a firebird database running in windows from a linux client, and when trying to attache the database I get the following error:

  bad parameters on attach or create database, CHARACTER SET UTF8 is not defined

I have googled and searched the answers here but can't seem to find a solution.

Any suggestions on how this can be overcome from the client side, or does it require the databse to be rebuilt with UTF8 support ?

Client-side I am using node-js with the node-firebird module, server side engine version is 2.5, ODS Version is 11.2

function dbConnect(cb){
    fb.attach({
        host: '192.168.42.233',
        database: 'gi',
        user: 'SYSDBA',
        password: 'xxxxx'
    }, function(err, db){
        if (err) console.log(err.message);
        else cb(db);

    })
}

Answer 1

Harriv's comment prompted me to test my idea that this might be caused by a missing entry in RDB$CHARACTER_SETS. If I manually delete UTF8 from this system table I get the same error when I try to connect with UTF8:

SQL Message : -924
Connection error

Engine Code    : 335544325
Engine Message :
bad parameters on attach or create database
CHARACTER SET UTF8 is not defined

The solution is to backup the database and restore it again. That will recreate the RDB$CHARACTER_SETS system table to include UTF8 again.

Note that this will only solve your problem if UTF8 is missing from RDB$CHARACTER_SETS. If it does, you should ask yourself why it was missing in the first place. Maybe a DBA or other developer deleted entries from RDB$CHARACTER_SETS, if so it is probably a good idea to find out why that was done.

For example: Maybe the database uses NONE as the default characterset (and for every column), and this was a way to ensure people only connect with the 'right' characterset by deleting all other options (a normal Firebird 2.5 database contains 52 entries in RDB$CHARACTER_SETS). If that is the case, make sure you fix this issue before connecting with UTF8, otherwise you will probably experience some form of data corruption (on read or on write) by incorrect transliteration.

The fix for this would be to create a new database with the right default characterset (and for the columns), and pump the data from the old to the new (making sure the read and write is done with the appropriate character set). After that Firebird can take care of transliterating between the database (or column) character set and the connection character set (with the exception of applications connecting using NONE as the connection character set).