Tuesday, June 07, 2016

Mess of gbak options

I see lot of places where gbak options (Firebird, InterBase) specified randomly.
gbak -c -r

what do you mean? -c or -r? Documentation specifies -c | -r. Symbol "|" is OR, not AND. Moreover, -r is not restore, it's REPLACE!
Thanks god, Firebird 1.5 and higher disabled -r option, because it allow to drop original database file without warning. Now you must specify -rep or -r o. Anyway, both of these is useless, because -c is enough. If you write script, rename original file before gbak -c. If something went wrong, you will have original DB file safe.

gbak -t ...
Come on, -t is default option for transportable backup. You think that someone will change it later to non-default? I doubt so.

gbak -c ... -page_size ...
don't incude this to regular script. Page size change usually made once, by administrative decision. If you specify this option in the script, and there is 4096 specified, after changing page size to 8192 and regular restore you will get 4096 again. Leave -page_size option to manual change.

gbak -b ... -ig ...
Ignoring checksum errors is useful only when database is corrupted. If you include this option to regular script, you may not find that database is broken.

45 Ways To Speed Up Firebird Database
12 Common Mistakes while Backing Up Databases
HQbird: advanced Firebird SQL for Enterprises

Saturday, April 23, 2016

Delimiter hell

You know, that each computer can have different regional settings. Moreover, some users change this settings to their preferences.
For example, if I like '.' as decimal separator, I can change it, if by default it is ','.

But, we are speaking here about Firebird and InterBase, right? Yes. There is a tool, GSTAT, used to get statistics from DB. It uses fixed decimal separator, '.'. So, if you run gstat -r employee.gdb, somewhere in the file there will be lines

    Primary pointer page: 407, Index root page: 408
    Average record length: 26.00, total records: 14, max record length: 31

You see "26.00" ? Right, here dot is used as delimiter, and it's being used forever, and system settings are ignored.
But, someone in Embarcadero, working on new InterBase version, decided to use system delimiter for Services API. So, if we will try to get statistics not by gstat, but by services API, we can get

    Average record length: 26.00, total records: 14, max record length: 31
    Average record length: 26,00, total records: 14, max record length: 31

I don't know why and when this happened. But I found this only few hours ago...

Wednesday, October 10, 2012

Don't miss Firebird Conference!

Only 15 days left before Firebird Conference, Luxembourg, 26-27 October.
Unique chance to handshake Firebird developer team, drink beer/wine during evening gathering, and, of course, to learn lot of interesting things about Firebird and application development!


Read the topics list, speaker names, and join us!

Tuesday, September 06, 2011

Operating system on RAID

We deal with the hardware by lot of different cases - when someone ask us what to buy for the new server, how to improve existing hadware, and, of course, when database becomes corrupted.

Many hadware vendors (as we see) sell servers with ready to use RAID (1, 5 or 10), with operating system installed on that raid. Also these hardware vendors think that it is cool that customer will place everything else on that RAID, and it will work perfectly (long and stable), so, no additional disks. That common mistake leads to real pain if something happens with the RAID.

Well, if you had your database on the RAID, and it fails, you need to find latest backup, and restore from it (after repairing RAID, of course).
But, if there were operating system on that RAID, it becomes a bit more complicated.

10 minutes ago we had a request to repair database from the RAID, broken by it's controller fail. The database is a real mess, so, sorry, there is nothing left to repair.
I don't know, was there also operating system, or not, but if it were:
- Your server failed, and it can't boot. You can't understand what happened with RAID.
- You need to attach additional HDD to server, install operating system on it, boot, then install RAID drivers and RAID software.

How much time it will take?
Well, let's put operating system on separate drive. RAID failed? Well, ok, but we can boot and look at RAID state.
You have doubts about that hard drive reliability? Make image backups. OS does not occupy much space - such an image can be stored even on flash-drive.

Ok, someone will say - yes, I will separate OS and my data. But I still want to keep OS on RAID 1, for reliability. Of course, it will protect OS from single disk failures, but, if RAID controller will fail, we will be at the starting point of that story.

Thus, my opinion is to have cheap (or not) and one (!) HDD for OS, that can be changed in a few minutes, and can be bought in any computer store nearby.
This solution, in spite of cheapness, can save lot of time in case of failure.
Think about it. Don't rely on "cool expencive RAID". Be a bit paranoid. :-)

Wednesday, March 23, 2011


You do not know what is that? Well, InterBase and Firebird allow specifying connection parameter that will disable garbage collection by statements in this connection.
This is the same as the option -g for gbak. And, of course, all other connections without this parameter will continue to try collecting garbage in the database, if any.

This connection option mostly is not known, because standard and "closed source" drivers usually does not produce access to server-specific features. Thus, you may use this option only for IBX (IBExpress components in Delphi and C++Builder), FIBPlus, and some other "direct access" components. ODBC, dbExpress? No, of course.

But, why you may ever need this option?
Sometimes you may want to run huge report that will scan lot of data. And, at the moment this report will run, there may be lot of garbage record versions in the database. So, your report connection will collect garbage (especially if you use Firebird Classic), and report will run much slower than it could be.
In this case, before running the report, you may set no_garbage_collect option, and report will be fast as a rocket.

I also knew some small applications with small databases (up to 1gb), that used no_garbage_collect option for all connections. This way garbage is not collected at all, and during the day size of database could grow 2 or 3 times. But, each day database was scheduled to backup and restore, so, why to care about garbage?

Since I do not see needs to do backup and restore each day, I do not recommend disabling garbage collection this way. Use it only for specific connection, if you ever need to do this at all.

Thursday, October 29, 2009

Are you ready? Yes, I am.

Alexey posted interesting info about sudden computer failure. Since I'm working with computers from 1987, I wan to share my experience too.

3 years ago my computer won't turn on. Possible causes could be - processor, motherboard or power unit. After some thinking I choosed to buy new power unit, and I was right - motherboard and processor were alive. I was lucky not buying new motherboard, righ?
During last 3 months I hadn't any problems, but
- videocard on my wife's computer died
- motherboard of my daughter's computer died

In first case it took 2 days to diagnoze what happened. The second took 1 day. But, anyway, daugter's computer still don't work, because I hadn't bought new MB and processor.

Alexey's case also shows that it is very important to have hardware nearby to change. So, it means that you computer (server) may stop working because something is dead
  1. processor
  2. motherboard
  3. videocard
  4. power unit
  5. hard drive
While first 4 parts you may change without affecting your system, last part, hard drive, is a core of your system. If you don't have backup, you will not be able to return server to it's working state. Yes, you can spend some time installing OS at your new hard drive, but, have you ever thought about how much time it will take?

Alexey and I speaking about desktops. Alexey had a lot of computers at home, and a netbook, so, the failure isn't so hard. Also, my daughter is "safe", because all the music and photo's at her hard-drive still undamaged.
But, really, what if the server will stop working? How much time it will take to restore it? Think about software as a hardware. Database failure can cause same damage, as broken hard drive.

p.s. right now we are at contact with the customer, who has broken hdd. Instead of 3 databases restore service produced 9 databases. Now customer need to understand what databases are the latest ones, and which one we need to repair. Also, databases are heavily damaged, so, only copies of that databases may help to restore data.

Saturday, September 26, 2009

Tips'nTricks using FBScanner

Yes, sometimes I use FBScanner too. :-)
My system is complex, because I have huge number of Firebird, InterBase and Yaffil versions. While Yaffil does not interfere with Firebird and InterBase, I need to run periodically Firebird 1.0, 1.5, 2.0, 2.1, 2.5 and InterBase 6.x, 7.0, 7.1, 7.5, 2007 and 2009. I do this by removing services records with "instsvc remove" after installation, because I don't need FB or IB as a service and run them ony as application like

fbserver -a
ibserver -a

To simplify this task I've created several cmd files that looks like
call remove_all.cmd
d:\firebird2\bin\instreg install
d:\firebird2\bin\fbserver -a

and remove_all.cmd is:
d:\ib71\bin\instreg remove
d:\ib75\bin\instreg remove
d:\ib2007\bin\instreg remove gds_db
d:\ib2009\bin\instreg remove gds_db
d:\ya\bin\instreg remove
d:\intrbase\bin\instreg remove
d:\firebird\bin\instreg remove
d:\firebird2\bin\instreg remove
d:\firebird25\bin\instreg remove

So, if I need to run Firebird 1.5, I simply call fb15.cmd and less than in a second I have Firebird 1.5 running. If I need to run InterBase 2007, I just stop Firebird 1.5 application (shutdown) and run ib8.cmd.

Well, returning back to the FBScanner. By default it tries to find Firebird or InterBase service installed and intercept it's configuration to work on different than 3050 port. Unfortunately for the FBScanner I have only InterBase 4.1 service installed. Anyway, I leave FBScanner configuration as is, to intercept 3050 port and to redirect it to port 3052.
Then, I'm editing firebird.conf for example for the Firebird 2.1, uncommenting and changing parameter RemoteServicePort:

RemoteServicePort = 3052

So, when I start fb2.cmd my Firebird 2.1 runs and listens to port 3052, not to 3050.

So, if I will connect from any application to the Firebird, FBScanner will intercept traffic to the 3050 and will log everything is happening between Firebird server and client.

But, sometimes I don't want to intercept or watch some specific connections, or to watch connections only for specified databases. That's simple!
You need to know, that if fbclient.dll finds in the path one level above the file firebird.conf, it will use port number specified in it.

For example, if I will connect to some database with IBExpert, specifying client libriary as ...firebird2\bin\fbclient.dll, it will use port 3052 from the firebird.conf and traffic will not be intercepted by FBScanner.
Instead, if I want traffic to be intercepted by FBScanner, I need to write server name not as localhost, as usual, but as localhost/3050. This time traffic will go through FBScanner, and every statement and transactions will be monitored.

I hope this example will help you to configure Firebird and FBScanner if you want to check what your application is doing with the server.