Migration to newer versions of Firebird

New Security database and changes to password authentication

It is not possibly to migrate passwords fromn the Firebird 2.5 security database to Firebird 3.0. There is a script in /misc/upgrade/security which will help you migrate users and generate new passwords but you will have to inform users of their new password. See the release notes [1].

If you have legacy applications that cannot use the new Firebird 3 client library you can enable legacy authentication. You should test this in advance. If you can avoid using legacy authentication life will be easier in the long run. However legacy auth is there to ease migration so use it if you have to.

See the release notes [2] and the language reference [3] for more details on everything related to the new security database.

gsec is deprecated

gsec was never easy to use. All its features are now available via SQL. It is recommended to completely abandon using it.

The new method of managing users is documented [4].

Access to user lists is now available within the current database via pseudo tables [5].

User names are now treated as SQL identifiers and are thus restricted to 31 characters

This might be a gotcha as this restriction did not apply previously.

Local aliases and variable names in PSQL are also now considered to be SQL identifiers

Thus they too are restricted to a length of 31 characters.

Permission to use generators and exceptions must now be granted

It is no longer sufficient to create a generator or an exception. You must now grant permission to use it. See this article for a simple solution.

System Tables are now read-only

Be sure to check that you have not used any hacks to modify the system tables.

Local Connections to SuperServer on Windows now require xnet:// prefix

Previously just the path or the alias was sufficient:

isql c:\path\to\my.fdb_or_alias

The above syntax now takes an exclusive lock on the database and no other attachments are allowed.

You now have to do this:

isql xnet://c:\path\to\my.fdb_or_alias

or alternatively a regular tcp connection via localhost:

isql localhost://c:\path\to\my.fdb_or_alias

Cursor Output Columns Must Be Named

If you use DECLARE CURSOR in PSQL you need to check that all the output columns are named or aliased. See the release notes [6] for more info.

Mixing explicit and implicit joins is no longer supported

This is relatively obscure. It was never good practice but was allowed. See the bug report for a full explanation of the problem.

Side effects of improvements to cursor stability

This is a subtle problem. Be sure to check your stored procedures and where you place SUSPEND within FOR SELECT loops. MERGE statements may also be affected. The release note has more info as well as links to the bug reports relating to this issue.

Databases.conf replaces aliases.conf

The new databases.conf allows per database configuration of many parameters. But functionally an existing aliases.conf can be renamed to databases.conf.

Reserved Words

Check that you are not using:

INSERTING
UPDATING
DELETING

in PSQL

And check that BOOLEAN is not used as a domain name.

You can see a full list of reserved words and keywords [7].

Trace configuration syntax has changed

The style of the new format is now consistent with other firebird configuration files such as databases.conf and plugins.conf

Configuration parameters removed or deprecated

The following configuration parameters have been removed from firebird.conf:

CompleteBooleanEvaluation
OldColumnNaming
OldSetClauseSemantics
UsePriorityScheduler
PrioritySwitchDelay
PriorityBoost
LegacyHash
LockGrantOrder

Some of these date back to InterBase days. All have been deprecated for a long time and/or had no actual effect.

Always deploy the new client kit

In most cases you can continue to use existing applications with the older fbclient library. And in general the new client library will work with older applications. The main reasons for not upgrading clients are purely logistical and/or financial ones.

However, for Firebird 3 you need the new client for these features:

• Authentication via Srp plugin
• Wire Encryption

As noted in New Security database and changes to password authentication using a Firebird 2.5 client solves some problems and can create others.

Upgrade your third-party tools to use the new API features

This is more difficult. Driver and Component developers are often still catching with new Firebird features. And upgrading an application is always more involved than just dropping in the new driver or component set. For this reason you may be forced temporarily to use legacy_auth, even if you can upgrade to the Firebird 3 client.

SQL Dialect 1

A lot of users have hung on to dialect 1 partly because it just worked and partly because the rules for integer division just made more sense. But maintaining different dialects requires effort and the intention was never to allow sql dialect 1 to continue for over twenty years.

This change should have been implemented years ago. If you are affected by this issue you can read a fairly detailed article about migration from dialect 1. Support will be removed in future versions of Firebird.

SQL> select 1/3 from RDB$DATABASE;

    DIVIDE
==========
         0

You just need to add some zeros to one or other of the integers to achiev the desired precision:

SQL> select 1/3.00 from RDB$DATABASE;

   DIVIDE
=========
     0.33

UDFs

The old udf library is no longer included with the installation kits. More or less all the functionality of the old udf library is now available via built-in functions. It is unlikely that support for UDFs will be removed in the future but their use is discouraged. The Fb 4 kit includes a migration script if you still use UDFs in your database. It will help convert udf function calls to the new built-in functions. You can find it in misc/upgrade/v4.0/udf_replace.sql. Check the release notes for more information on how to use this script.

If you really need special custom made UDFs it is recommended that you convert them to User Defined Functions (UDRs). They are slightly more difficult to write but much more secure. They were introduced in Firebird 3.0 and you can start to read about them [8].

CURRENT_TIME and CURRENT_TIMESTAMP

These no longer work as expected. To help prepare for this change Firebird 2.5.9 and Firebird 3.0.4 added the new expressions LOCALTIME and LOCALTIMESTAMP. Take a look at the difference:

SQL> set list on;
SQL> select CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP
     from rdb$database;

CURRENT_TIME                    10:37:31.0000 Europe/Paris
CURRENT_TIMESTAMP               2022-11-15 10:37:31.6510 Europe/Paris
LOCALTIME                       10:37:31.0000
LOCALTIMESTAMP                  2022-11-15 10:37:31.6510

This is easy to fix and can be done before migration to FB4.

The old date literals are now rejected in PSQL

InterBase, and thus Firebird has always supported literal strings such ‘now’ as handy shortcuts. If you use these with DATE, TIME or TIMESTAMP their behaviour was inconsistent. Especially TIMESTAMP 'now'. It did return a time stamp but not the value expected. In stored procedures it would return the value at compile time, not run time. And in DSQL the value returned would be that at prepare time, instead of run time. This usage will now be rejected.

Check your SQL for the following case-insensitive strings:

NOW   | TODAY
TOMMORROW | YESTERDAY

And review if necessary.

Calls such as:

SQL> select cast ('now' as timestamp) as TIME_NOW from rdb$database;

TIME_NOW                        2022-11-15 11:02:31.2560

are not affected.

Starting Value of Sequences

The implementation has changed. Although the results are the same. Confused? You will be. Especially if you relied upon the way sequences worked.

Up until Firebird 3 a sequence was created with its current value set to its starting value (or zero by default). So a new sequence would typically be stored as 0 and on first use the value returned would be 1. Which is what you would expect.

Firebird 4 now creates sequences with the starting value minus the increment. Whether you are affected by this change depends upon how you create sequences.

INSERT … RETURNING now requires select privileges

As the release notes say:

If some INSERT statement contains a RETURNING clause that refers columns of the underlying table, the appropriate SELECT privilege must be granted to the caller.

• [1] https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-compat-upgrade-secdb
• [2] https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-security-new-authentication
• [3] https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-security-auth
• [4] https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-security-user
• [5] https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-pseudo-users
• [6] https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-compat-psql-cursorcols
• [7] https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-appx03-reskeywords
• [8] https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb30-engine-extengines
• [9] https://firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-engine-stmt-level-consistency