Practical Migration Guide to Firebird 5.0

Starting from Firebird 3.0, the SYSDBA user is not initialized by default (for the SRP user management plugin), so you need to explicitly create a user and specify a password for him. This can be done in two ways: using the console tool for executing interactive queries isql.exe and the console tool for managing the security database gsec.exe.

Before installing and running Firebird as a service, you need to choose the server mode.

The instsvc.exe utility writes, deletes or changes information about the server startup in the service base of the operating system. In addition, it allows you to manage the startup and shutdown of the service.

If you run it without parameters, it will display help on commands and parameters.

To install the service, you need to enter the command

In this case, Firebird will be installed as a service with the name "Firebird Server – DefaultInstance". This service will start automatically when the OS starts, under the LocalSystem account, intended for services.

If you need to install multiple instances of Firebird running as services, then you need to assign them different names using the -n option

To start the service, use the command

If the service was installed with a name different from the default, then you need to use the -n switch

To stop the service, use the command

If the service was installed with a name different from the default, then you need to use the -n switch

To remove the service, you need to enter the command

If the service was installed with a name different from the default, then you need to use the -n switch

To view all Firebird services installed in the system, use the command

If you are talking about installing only the client part, then the file fbclient.dll is required. The Firebird 5.0 client requires the installed Microsoft Runtime C++ 2015-2022 of the corresponding bitness. If this library is not installed, you can copy additional libraries that are supplied in the ZIP archive under Windows msvcp140.dll and vcruntime140.dll (and vcruntime140_1.dll for 64-bit installation).

It is desirable that the file firebird.msg be located next to fbclient.dll. Most error messages are already contained in fbclient.dll, but if you are going to use console utilities, the file firebird.msg must be present.

Unlike Firebird 2.5 and Firebird 3.0, the client library also requires ICU files (icudt63.dll, icuin63.dll, icuuc63.dll and icudt63l.dat). Previously, the ICU library was required only by the server. Now it may be required by the client part, if you are going to work with data types TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE. The ICU library is also required when calling the functions UtilInterface::decodeTimeTz() and UtilInterface::decodeTimestampTz().

If you need traffic compression when working over TCP/IP, then you will need the library zlib1.dll.

You may need the library plugins/chacha.dll if you are going to use the ChaCha traffic encryption plugin. This plugin is used by default starting from Firebird 4.0, as it is the first in the list of values in the configuration parameter WireCryptPlugin = ChaCha, Arc4.

The library fbclient.dll, as well as other files of the client library, must be located either next to the application, or in one of the directories where the search is performed, for example added to PATH or the system directory for placing public libraries (system32 or SysWOW64).

Starting from version Firebird 3.0, the embedded version is not distributed separately. You can use the same set of files as both a network server and an embedded server. But, if you need an embedded set of minimal size, then the structure of files and directories for Firebird 5.0 embedded is as follows:

If necessary, you can also copy the executable files of the utilities fbsvcmgr.exe, fbtracemgr.exe, gbak.exe, gfix.exe, gstat.exe, isql.exe, nbackup.exe. If you are going to use gbak with the -zip switch, then you will also need the library zlib1.dll.

SQL language compatibility issues are possible both for the objects of the database itself (PSQL procedures and functions), and for the DSQL queries used in your application.

To detect SQL language compatibility issues for database objects, the following method is recommended. Extract the metadata of the database into a script on the old version of Firebird.

Uncomment the CREATE DATABASE statement inside the script, make the necessary changes, and try to create a new database from the script in Firebird 5.0:

where, the -i key is the input file of the script; the -o key is the output file of messages; the -m key makes isql output error messages to the output message file.

Next, look at the file error.log for errors, and if they are found, change the metadata in the original database. Repeat the algorithm described above until all errors are eliminated. After that, you can safely do backup/restore.

Next, we will list some of the most common compatibility issues at the SQL level, which you can fix before moving to the new ODS. You can read the full list of incompatibilities in Release Notes 5.0 in the chapter "Compatibility Issues". When migrating from 3.0, you should also familiarize yourself with the chapter of the same name in Release Notes 4.0, and when migrating from 2.5 - Release Notes 3.0.

Support for external functions (UDF) starting from Firebird 4 is declared obsolete.

The effect of this is that UDF cannot be used with the default configuration, since for the parameter UdfAccess in firebird.conf the default value is now None. UDF libraries ib_udf and fbudf are removed from the distribution.

Most of the functions in these libraries are already obsolete in previous versions of Firebird and have been replaced by built-in analogues. Now safe replacements for some of the remaining functions are available either in the new library of user-defined subprograms (UDR) with the name [lib]udf_compat.[dll/so/dylib] (this is done after changing the ODS), or in the form of script conversions to stored PSQL functions.

We recommend replacing UDF functions with their built-in analogues in advance (before switching to a new ODS). If you are migrating from Firebird 3.0, you can also rewrite some functions in PSQL.

If after these steps you still have UDF functions, you need to change the configuration parameter

After preliminary preparation, you can try to convert the database to a new ODS using the gbak tool.

Otherwise, you can get a metadata error after the entire terabyte of data has been written, which will be very disappointing. In addition, on restored in the new version of metadata, it is convenient to check the work of scripts for recompiling database objects. In this example, it is assumed that Firebird 3.0 and Firebird 5.0 are installed on the same machine. Firebird 3.0 works using TCP port 3053, and Firebird 5.0 - 3055.

First of all, you need to create a backup of your database on the current version of Firebird using the following command.

Next, you need to restore your copy on Firebird 5.0.

Starting from Firebird 5.0, the gbak utility can create a backup and restore a database using parallelism. The number of parallel threads used for backup or restore is specified using the -parallel or abbreviated -par option. Using parallel threads can speed up the restore process by 2-3 times, depending on your hardware and database.

By default, parallelism is disabled in Firebird 5.0. To be able to use it, you need to set the MaxParallelWorkers parameter in firebird.conf. This parameter limits the maximum number of parallel threads that can be used by the Firebird core or its utilities. By default, it is equal to 1. It is recommended to set MaxParallelWorkers to a value equal to the maximum number of physical or logical cores of your processor (or processors).

Now for the restore you can use the following command.

Here N is the number of parallel threads that gbak will use, it must be less than or equal to the value set in MaxParallelWorkers.

After the restore, carefully examine the restore.log for errors. However, there will be no SQL level compatibility errors in this log, since the objects DB at restore are not recompiled. If some procedure or trigger contains incompatible constructions, then later when ALTER such an object, an error will be issued.

You can completely clear the DB of such errors only if you extract the script from the DB operation

in the previous version of Firebird, and create an empty DB in Firebird 5.0 from this script, correcting the errors of creating metadata in turn.

The easiest will be to transfer the user list from Firebird 4.0.

To transfer the security database from Firebird 4.0 to 5.0, create a backup of the file security4.fdb using gbak Firebird 4.0 and restore it as security5.fdb using gbak Firebird 5.0. Use gbak locally (using the embedded connection) while Firebird Server is not running.

To transfer users from the Firebird 3.0 security database to the Firebird 4.0 security database, you need to perform a backup of security3.fdb using gbak and restore it as security5.fdb using gbak Firebird 5.0.

However, keep in mind that in this case you will lose some new features. We will go the more difficult way:

Congratulations! Your users have been transferred with all attributes and passwords.

Transferring users from Firebird 2.5 is more difficult. In Firebird 3.0, a new authentication method was introduced - SRP - Secure Remote Password Protocol. The old authentication method is also available, but disabled by default as it is considered insufficiently secure. The Release Notes 3.0 describe how to transfer users from Legacy_UserManager to SRP, but in this case you will not be able to connect via fbclient version 2.5. In addition, it is impossible to transfer passwords from Legacy_UserManager to SRP. The proposed script will transfer the user list, but random passwords will be generated. If you want to restore your previous passwords, you will have to do it manually. We wrote an alternative script that allows you to transfer users from security2.fdb to security5.fdb in the Legacy_UserManager plugin. Here we will describe both options.

The network protocol WNET (also known as Named Pipes, also known as NetBEUI), previously supported on the Windows platform, has been removed in Firebird 5.0.

Windows users who worked with any WNET connection string (\\server\dbname or wnet://server/dbname) should instead switch to the INET (TCP) protocol (connection string server:dbname, server/port:dbname, inet://server/dbname or inet://server:port/dbname).

Relevant: when migrating from Firebird versions 2.5, 3.0.

As mentioned earlier, some expressions may return new data types that cannot be interpreted by your application without modification. Such modification may take a significant amount of time or be beyond your capabilities. To simplify migration to new versions, you can set the DataTypeCompatibility parameter to the compatibility mode with the required version in firebird.conf or databases.conf.

or

This is the fastest way to achieve compatibility with new data types. However, over time you may start to implement support for new types in your application. Naturally, this will happen gradually - first one type, then another, and so on. In this case, you need to configure the mapping of those types that you have not yet completed, to other data types. For this, the operator SET BIND OF is used.

The keyword LEGACY in the TO part is used when a data type that is absent in the previous version of Firebird should be represented in a way that is understandable to the old client software (some data loss is possible). There are the following conversions to LEGACY types:

When setting the DataTypeCompatibility parameter, new data types are converted to legacy types according to the table described above.

A detailed description of this operator is available in the "Firebird 4.0 Release Notes" and "Firebird 5.0 SQL Language Reference". With it, you can control the mapping of new types in your application by executing the corresponding query immediately after connecting, and even write an AFTER CONNECT trigger in which to use several such operators.

For example, suppose you have added support for date and time with time zones to your application, but you still do not support the types INT128 and DECFLOAT. In this case, you can write the following trigger.

Relevant: when migrating from Firebird versions 2.5, 3.0.

Firebird 4 not only introduces consistent reading (READ CONSISTENCY) for queries in READ COMMITTED transactions, but also makes it the default mode for all READ COMMITTED transactions, regardless of their RECORD VERSION or NO RECORD VERSION properties.

This is done to provide users with better behavior - both conforming to the SQL specification and less prone to conflicts. However, this new behavior may also have unexpected side effects.

Perhaps the most important of them is the so-called restarts when processing update conflicts. This can lead to some code that is not subject to transactional control being executed multiple times within PSQL. Examples of such code may be:

More about consistent reading in READ COMMITTED transactions can be read in the "Firebird 4.0 Release Notes".

Another important effect is that unfetched cursors in READ COMMITTED READ CONSISTENCY transactions in Read Only mode now hold garbage collection. We recommend that you stop using a single long READ COMMITTED READ ONLY transaction in your application, and replace it with several such transactions, each of which is active for as long as necessary.

If the features of the READ CONSISTENCY mode are undesirable for some reason, then to restore the obsolete behavior, you need to set the configuration parameter ReadConsistency to 0.

The optimizer changes in each version of Firebird. Mostly these changes are positive, that is, your queries should run faster, but some queries may slow down, so you need to test the performance of your application, and if there is a slowdown somewhere, you need intervention from the programmer.

For most optimizer changes, you cannot influence the query plan by changing the server configuration. In this case, you can do the following:

There are a couple of points in the optimizer’s work that can be influenced by changing the configuration:

Starting from Firebird 5.0, client modifying operators INSERT .. SELECT, UPDATE, DELETE, UPDATE OR INSERT and MERGE, containing the RETURNING clause, return a cursor, i.e. they are able to return multiple records instead of issuing an error "multiple rows in singleton select", as it happened before.

Now these queries during preparation are described as isc_info_sql_stmt_select, whereas in previous versions they were described as isc_info_sql_stmt_exec_procedure.

Singleton operators INSERT .. VALUES, as well as positioned operators UPDATE and DELETE (i.e., those containing the WHERE CURRENT OF clause) retain the existing behavior and are described as isc_info_sql_stmt_exec_procedure.

However, all these queries, if they are used in PSQL and the RETURNING clause is applied, are still considered as singletons.

If your application uses modifying operators INSERT .. SELECT, UPDATE, DELETE, UPDATE OR INSERT and MERGE, containing the RETURNING clause, then this may be the cause of errors. Make sure that your driver or access component correctly handles such queries, and if not, then either modify the code (application or component), or wait until an update of the corresponding driver/component that correctly handles these queries is released.

Examples of modifying operators containing RETURNING, and returning a data set: