Firebird 2 brings plenty of enhancements to backing up databases: a new utility for running on-line incremental backups and some improvements to gbak to avoid some of the traps that sometimes befall end-users.
N. Samofatov
Fast, on-line, page-level incremental backup facilities have been implemented. The backup engine comprises two parts:
The functional responsibilities of NBak are:
The backup state cycle is:
nbak_state_normal -> nbak_state_stalled -> nbak_state_merge -> nbak_state_normal
Note: This merge state logic has one quirky part. Both Microsoft and Linux define the contents of file growth as “undefined” i.e., garbage, and both zero-initialize them.
This is why we don't read mapped pages beyond the original end of the main database file and keep them current in difference file until the end of a merge. This is almost half of NBak fetch and write logic, tested by using modified PIO on existing files containing garbage.
The functional responsibilities of NBackup are:
Incremental backups are multi-level. That means if you do a Level 2 backup every day and a Level 3 backup every hour, each Level 3 backup contains all pages changed from the beginning of the day till the hour when the Level 3 backup is made.
Backing up
Creating incremental backups has the following algorithm:
Restoring
Restore is simple: we reconstruct the physical database image for the chain of backup files, checking that the backup_guid of each file matches prev_guid of the next one, then fix it up (change its state in header to nbak_state_normal).
Usage
nbackup <options>
Valid Options
Note:
(v.2.0.6) An improvement has been done for POSIX versions to address a problem whereby the full backup tool of the nBackup would hog I/O resources when backing up large databases, bringing production work to a standstill. Now, NBackup tries to read from the operating system cache before attempting to read from disk, thus reducing the I/O load substantially.
Note: The cost may be a 10 to 15 percent increase in the time taken to complete the full backup under high-load conditions.
Tracker reference CORE-2316.
P. Vinkenoog
A user manual for NBak/NBackup has been prepared. It can be downloaded from the documentation area at the Firebird website: https://www.firebirdsql.org/pdfmanual/ - the file name is Firebird-nbackup.pdf.
V. Horsun
The new gbak switch
is a separate switch designed to make harder for the unsuspecting to overwrite a database accidentally, as could occur easily with the shortened form of the old switch:
In summary:
This change means that, if you have any legacy batch or cron scripts that rely on gbak -r or gbak -R without modification, then the operation will except if the database exists.
If you want to retain the ability of your script to overwrite your database unconditionally, you will need to modify the command to use either the new switch with the OVERWRITE argument or the new short form for the old -REPLACE_DATABASE switch.
During Firebird 1 development, an optional numeric <counter> argument was added to the -V[erbose] switch of gbak for both backup and restore. It was intended to allow you to specify a number and get a running count of rows processed as the row counter passed each interval of that number of rows. It caused undesirable side-effects and was removed before Firebird 1.0 was ever released. So, although it never happened, it was documented as “implemented” in the release notes and other places.
See also:
Work on ISQL has involved a lot of bug-fixing and the introduction of a few new, useful features. One trick to note is that CHAR and VARCHAR types defined in character set OCTETS (alias BINARY) now display in hex format. Currently, this feature cannot be toggled off.
The following command-line switches were added:
D. Ivanov, C. Valderrama
Command line switch -b to instruct isql to bail out on error when used in non-interactive mode, returning an error code to the operating system.
When using scripts as input in the command line, it may be totally unappropriate to let isql continue executing a batch of commands after an error has happened. Therefore, the -b[ail] option will cause script execution to stop at the first error it detects. No further statements in the input script will be executed and isql will return an error code to the operating system.
Some Features
SET BAIL [ON | OFF]
As is the case with other SET commands, simply using SET BAIL will toggle the state between activated and deactivated. Using SET will display the state of the switch among many others.
Example
isql -b -i my_fb.sql -o results.log -m -m2
Tip: However, if the user loads isql interactively and later executes a script with the input command, this is considered an interactive session even though isql knows it is executing a script.
Example
isql Use CONNECT or CREATE DATABASE to specify a database SQL> set bail; SQL> input my_fb.sql; SQL> ^Z
Whatever contents the script has, it will be executed completely, errors and all, even if the BAIL option is enabled.
C. Valderrama
This is a command-line option -m2 to send the statistics and plans to the same output file as the other output (via the -o[utput] switch).
When the user specifies that the output should be sent to a file, two possibilities have existed for years: either
(In either case, simply passing the command OUTput is enough to have the output returned to the console). However, although error messages are shown in the console, they are not output to the file.
The -m command line switch was added, to meld (mix) the error messages with the normal output to wherever the output was being redirected.
This left still another case: statistics about operations (SET STATs command) and SQL plans as the server returns them. SET PLAN and SET PLANONLY commands have been treated as diagnostic messages and, as such, were always sent to the console.
What the -m2 command line switch does is to ensure that stats and plans information go to the same file the output has been redirected to.
Note: Neither -m nor -m2 has an interactive counterpart through a SET command. They are for use only as command-line isql options.
C. Valderrama
The sole objective of this parameter is to specify a case-sensitive role name.
The following commands have been added or enhanced.
C. Valderrama
Some people consider it useful to be able to do a SELECT inside isql and have the output sent to a file, for additional processing later, especially if the number of columns makes isql display impracticable. However, isql by default prints column headers and, in this scenario, they are a nuisance.
Therefore, printing the column headers – previously a fixed feature – can now be enabled/disabled interactively or from a script by means of the
SET HEADing [ON | OFF]
command in the isql shell. As is the case with other SET commands, simply using SET HEAD will toggle the state between activated and deactivated.
Note: There is no command line option to toggle headings off.
Using SET will display the state of SET HEAD, along with other switches that can be toggled on/off in the isql shell.
The SHOW <object_type> command is meant to show user objects of that type. The SHOW SYSTEM commmand is meant to show system objects but, until now, it only showed system tables. Now it lists the predefined system UDFs incorporated into FB 2.
It may be enhanced to list system views if we create some of them in the future.
A. dos Santos Fernandes
This SQLDA_DISPLAY command shows the input SQLDA parameters of INSERTs, and DELETEs. It was previously available only in DEBUG builds and has now been promoted to the public builds. It shows the information for raw SQLVARs. Each SQLVAR represents a field in the XSQLDA, the main structure used in the FB API to talk to clients transferring data into and out of the server.
Note: The state of this option is not included in the output when you type SET; in isql to see the current settings of most options.
C. Valderrama
The SET TRANSACTION statement has been enhanced so that, now, all TPB options are supported:
Example
SET TRANSACTION WAIT SNAPSHOT NO AUTO UNDO LOCK TIMEOUT 10
See also the document doc/sql.extensions/README.set_transaction.txt.
C. Valderrama
ODS (On-Disk Structure) version is now returned in the SHOW DATABASE command. (C. Valderrama)
C. Valderrama
In previous versions, the only reasonable way to know where a script had caused an error was using the switch -e for echoing commands, -o to send the output to a file and -m to merge the error output to the same file. This way, you could observe the commands isql executed and the errors if they exist. The script continued executing to the end. The server only gives a line number related to the single command (statement) that it's executing, for some DSQL failures. For other errors, you only know the statement caused problems.
With the addition of -b for bail as described in (-b[ail] Bail out), the user is given the power to tell isql to stop executing scripts when an error happens, but you still need to echo the commands to the output file to discover which statement caused the failure.
Now, the ability to signal the script-related line number of a failure enables the user to go to the script directly and find the offending statement. When the server provides line and column information, you will be told the exact line of DML in the script that caused the problem. When the server only indicates a failure, you will be told the starting line of the statement that caused the failure, related to the whole script.
This feature works even if there are nested scripts, namely, if script SA includes script SB and SB causes a failure, the line number is related to SB. When SB is read completely, isql continues executing SA and then isql continues counting lines related to SA, since each file gets a separate line counter. A script SA includes SB when SA uses the INPUT command to load SB.
Lines are counted according to what the underlying IO layer considers separate lines. For ports using EDITLINE, a line is what readline() provides in a single call. The line length limit of 32767 bytes remains unchanged.
M. Kubecek
When unknown parameters are used, isql now shows all of the command-line parameters and their explanations instead of just a simple list of allowed switches.
opt/firebird/bin] isql -? Unknown switch: ? usage: isql [options] [<database>] -a(all) extract metadata incl. legacy non-SQL tables -b(ail) bail on errors (set bail on) -c(ache) <num> number of cache buffers -ch(arset) <charset> connection charset (set names) -d(atabase) <database> database name to put in script creation -e(cho) echo commands (set echo on) -ex(tract) extract metadata -i(nput) <file> input file (set input) -m(erge) merge standard error -m2 merge diagnostic -n(oautocommit) no autocommit DDL (set autoddl off) -now(arnings) do not show warnings -o(utput) <file> output file (set output) -pag(elength) <size> page length -p(assword) <password> connection password -q(uiet) do not show the message "Use CONNECT..." -r(ole) <role> role name -r2 <role> role (uses quoted identifier) -sqldialect <dialect> SQL dialect (set sql dialect) -t(erminator) <term> command terminator (set term) -u(ser) <user> user name -x extract metadata -z show program and server version
SF #910430 ISQL and database dialect, fixed by C. Valderrama, B. Rodriguez Somoza. What was fixed When ISQL disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which could lead to some inappropriate warning messages.
SF #223126 Misplaced collation when extracting metadadata with ISQL, fixed by B. Rodriguez Somoza.
SF #223513 Ambiguity between tables and views, fixed by B. Rodriguez Somoza.
SF #518349 ISQL SHOW mangles relationship, fixed by B. Rodriguez Somoza.
Unregistered bug Possible crashes with long terminators, fixed by C. Valderrama.
Unregistered bug Avoided several SQL> prompts when using the INPUT command interactively. Command-line Utilities 90 implemented by C. Valderrama.
Unregistered bugs Some memory leaks, fixed by C. Valderrama
Changes to the gsec utility include:
C. Valderrama
gsec now returns an error code when used as a non-interactive utility. Zero indicates success; any other code indicates failure.
See also:
Changes to the gfix utility include:
N. Samofatov, D. Yemanov
The options for gfix -shut[down] have been extended to include two extra states or modes to govern the shutdown.
New Syntax Pattern
gfix <command> [<state>] [<options>] <command> ::= {-shut | -online} <state> ::= {normal | multi | single | full} <options> ::= {-force <timeout> | -tran | -attach}
Note: Multi is the default state for -shut, normal is the default state for -online.
The modes can be switched sequentially:
normal <-> multi <-> single <-> full
Examples
gfix -shut single -force 0 gfix -shut full -force 0 gfix -online single gfix -online
You cannot use -shut to bring a database one level more “online” and you cannot use -online to make a database more protected (an error will be thrown).
These are prohibited:
gfix -shut single -force 0 gfix -shut multi -force 0 :: gfix -online gfix -online full :: gfix -shut -force 0 gfix -online single
Timeout:
As before, the timeout is in seconds. In the case of the -attach and -tran timeouts, the timeout determines how long the engine will wait for any attached clients to complete their work and log off. The shutdown request should return the SQLCode -902 message shutfail (ISC code 335544557), Database shutdown unsuccessful if there are still active attachments when the timeout expires.
However, there is a known issue with the implementation of the new modes. A regression occurred, whereby the said message is returned but the engine does not revert the database to the online state, as it should. It affects all versions of Firebird up to and including v.2.0.5 and v.2.1.3, and all v.2.5 alphas, betas and release candidates.