The Firebird database: Problem handling UTF8 characters

The 'Latin capital letter I with dot above', İ (Unicode 0130), strikes again! This innocent looking Turkish character seems to be reliable when it comes to breaking software that should be able to handle UTF8. (See also this post for a Java example.)

This time it breaks the Firebird database (in my case, v2.1.1 on a 64-bit Debian system). Downcasing some random characters in a database configured to handle UTF8 works fine:

SELECT LOWER('AӴЁΪΣƓ') FROM RDB$DATABASE

returns the expected string, aӵёϊσɠ.

However, when you throw in the trouble-making İ, everything blows up:

SELECT LOWER('AӴЁΪΣƓİ') FROM RDB$DATABASE
*** IBPP::SQLException ***
Context: Statement::Fetch
Message: isc_dsql_fetch failed.

SQL Message : -104
Invalid token

Engine Code    : 335544849
Engine Message :
Malformed string

Slightly different input, generates a different error message:

SELECT LOWER('İA') FROM RDB$DATABASE
*** IBPP::SQLException ***
Context: Statement::Fetch
Message: isc_dsql_fetch failed.

SQL Message : -802
Arithmetic overflow or division by zero has occurred.

Engine Code    : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation

There is an item on the Firebird user list, but without any answers so far.

Update: As mariuz points out in a comment below, this defect now seems to be fixed in an upcoming version. See this bug tracker item.

New version of FlameRobin, Admin GUI for Firebirdsql

FlameRobin 0.9.0 is released

There is a new version of FlameRobin, a GUI for creating and managing Firebird databases. This version introduces tabbed browsing. In earlier versions of FlameRobin, you would end up with an unmanageable large number of open windows after a short while. This problem is now largely gone. (However, not everyone found this to be a problem --- see one of the comments below.)

I've mostly used FlameRobin for inspecting existing databases, for some minor editing of (existing) stored procedures, and for querying the database. The overall judgment is that it is a fine and very useful piece of software.

Update: There is no pre-compiled version for 64-bit Linux, so if this is what you need, you're on your own... (we gave up on compiling from source after 15 minutes of library dependency hell). This was on a Ubuntu machine.

Update of the update: Apparently, there was already a pre-compiled 64-bit Ubuntu version available! To find the information about the Ubuntu repository, you should not go to the download page that the announcement of the new version points to, you should go here. Thanks mariuz, for pointing this out (see comment below).

Firebird vs Postgresql

We have similar databases running on MySql, Postgresql and Firebird. One of the reasons for moving away from MySql was the fact that the UTF8 support didn't work properly. I cannot remember the details, but it had to do with non-Latin-1 data, such as text in Czech or Russian. In some situations MySql refused to correctly identify equal UTF8 strings. You put in some word that you cannot retrieve again, bleh!

Furthermore, we've never understood how the user permissions are supposed to work in MySql (we always end up frantically running all possible variants of the GRANT ALL command).

We moved to Postgresql, which worked a lot better. Now we've started using Firebird, that also seems like a very nice piece of software.

Here is list of a few things I've noticed when moving from Postgresql to Firebird:

* Firebird lacks built-in support for regular expressions. (We make heavy use of complex string searches of natural language data. If we hadn't got help from an expert, who helped us compile some user defined functions, UDF:s, for this purpose, this would have been a show-stopper.)

* Postgres' psql command line tool is better than Firebird's isql(-fb). (If you are a Windows user, see Carlos' comment below)

* Firebird database files grow and grow. This is true even if you delete data. You have to manually back-up and restore a database to reclaim disk space. Maybe this is not a great problem in normal usage, but I noticed that the databases I use for running test suits against keep growing, though the test database itself is quite small (and the data are cleared out between test runs). [Update: Please notice that long-time users of Firebird insist that this is not a problem. See Carlos', Sergio Marcelo's and also Michal's comments below.]

* I've never had any luck installing Firebird from a Debian package. I have had to do a manual install to get it to work

* Firebird has a useful GUI, FlameRobin, that let's you inspect and change your databases. FlameRobin comes with an editor useful for writing/editing stored procedures. The editor has code completion, that helps you with suggestions of table and column names and the like as you type.

* Firebird has a nice way to manage database files: all tables of a database end up in a single file, that you can name whatever you like, and put wherever you like.

* It appears to be easier to find useful documentation for Postgres than for Firebird (but Firebird does have a nice FAQ site)


Answer to Darius Damalakas comment below: I'm not the right person to comment on the performance of the different DBMSs. However, we haven't noticed any significant difference in performance between MySql, Postgresql and Firebird. Currently, the bottlenecks in our software are to be found outside of the databases, so the performance of the individual DBMSs has not been a big concern. They're all fast enough.

Firebird does seem to be a snappy system, and I would be surprised to find it to perform less good than Postgres.

So far, the only difference in features that has mattered to us, is the lack of built-in support for regular expressions in Firebird (see above). In all other respects (of importance to us), the functionality of Postgres and Firebird seems equivalent.

Update: Support for regular expressions is scheduled for the upcoming 2.5.0 release of Firebird.

Update: In response to an anonymous (and rather critical) comment, mariuz has added some useful links in a comment below.

Update: In a comment below, Michal has posted some information on DatabaseGrowthIncrement, taken from the release notes of Firebird 2.1.

Beware of Firebird 2.0 Debian package

We are migrating an application to the Firebird 2.0 database manager (firebirdsql.org). Our server runs Debian (AMD64), and we used the Firebird 2.0 (superserver) Debian package as suggested in the Firebird site's FAQ section. However, when the package was installed, it appears to have silently overlooked a dependency, missing a library necessary for getting the "user defined functions", UDF:s, to work correctly. (Firebird didn't find the UDF:s, resulting in runtime errors when calls to the functions were issued from a Firebird database.)

We made sure that Firebird as well as the UDF:s were all compiled for AMD64.

When uninstalling the apt-get Firebird package, and manually installing Firebird 2.0.3 from the standard .tar.gz file, the missing dependency was spotted, and the database could be properly installed. Unfortunately, I didn't keep a record, but it might have been the correct version of libstdc++ that was missing.

As far as I can remember, this is the only time a Debian apt-get package has failed me. In addition to the fact the apt-get install of Firebird might be broken, you have to be careful not to apt-get install "Firebird 2", since this will give you Firebird 1.5! Peculiar. (But see the comment from mariuz below).

I had a similar experience the first time I tried to install Firebird from a Debian package. This was Firebird 1.5 (the Firebird 2 Debian package), before Firebird 2.0 was released. I never got that one to run either, but had to install the tar.gz version obtained from the official Firebird webserver. I can't remember exactly what went wrong at that time, but it was impossible to get the Debian package that we tried at that time to work. The manual install worked perfectly, just as it did this time.

Update: The Debian Firebird2 package (containing Firebird 1.5) appears to be discontinued.