Wednesday, March 25, 2009

InterBase 2009 lost ODS 10.1 support

InterBase for years uses Y-valve, implemented by Jim Starkey, to support previous databases in new InterBase versions.
The native ODS (On-Disk Structure) for the particular server version is the only one - the ODS of database created with this server. For example, native ODS for InterBase 6.0 is 10.0. For Firebird 1.5 - 10.1, etc.
And, as a feature, InterBase and Firebird supports at least N-1 ODS number.
The decision what ODS support to remove from the server is completely on the server developers. Firebird 2.5 still can open databases from InterBase 5.6 only because Firebird developers still keep code to support it.
But, seems that InterBase developers decided to eliminate ODS 10.1 support from the InterBase 2009. InterBase 2007 can open ODS 10.1 databases without a problem. But, InterBase 2009
  • initial release, 9.0.0.206 says "incompatible version of on-disk structure"
  • IB 2009 Update 2 (9.0.2.369) simply crashes
Is it bad, or not? I don't know. Right now InterBase 2009 uses ODS 13 for new databases. The ODS difference a bit far (13-10=3) from the Firebird (11 - 10 = 1).

But, you need to know, that you may not now open old databases (less than ODS 11) with the InterBase 2009.

p.s. I have not found any databases with ODS 10.0 on my computer, but ODS 10.0 and 10.1 differs only by some additional indices on some system tables in ODS 10.1.

Tuesday, February 10, 2009

Broken Indices

IBAnalyst since version 2.0 may report about broken or inconsistent indices. The detection of this cases is being made by checking index key count and record cound. If key count is less than records + versions, than the index is broken.

How it can happen?
At first, of course, it can happen when the database is broken, and some keys are missing. But the more realistic case for this inconsistency is indexing data when the data is being modified.
It can be easily reproduced, because index creation or re-activation passes three steps:
  1. server moves data from the table to the temp file
  2. data in the temp file is being sorted
  3. server moves sorted data to the database as an index
If there is no write lock on the table during all three steps, modified (or inserted) data will not exist in the created index. And will never be found by the index search.
So, find the big table, apply "create index" on it, then wait until temp file for the sorting will be completely created in the temp directory, and after that insert some record in the table and commit. Then try to find this record with where condition. Null. Scary?
Yes, but this is fixed in Firebird 2.0 (by write locks on table during indexing).

p.s. gfix also may detect this type of index inconsistency.

Wednesday, November 12, 2008

FBScanner 2.1 is released

We are proud to announce the release of FBScanner 2.1. This version introduces a very interesting new features, such as plans extraction for queries, improved logging and tags support; these features make development and administration of Firebird and InterBase much more productive and effective. Read feature matrix and download trial of FBScanner 2.1 to estimate its value and effectiveness.

Extraction of queries' plans is implemented both in the Viewer and in the log. Improved logging is a great advantage for administrators who wants some sort of an audit, or for developers who study queries behavior in sophisticated systems.

Using tags developers and administrators can easily track long-running or special queries, watch real-time flow of SQL queries from different applications.

With automatic priority setting for Firebird Classic administrators of heavily loaded Firebird-based systems can perform tuning and use their hardware resources better.

Thursday, October 16, 2008

Is Firebird Poor?

This is a continuation of previous post about Firebird. Now I’d like to consider some facts about money flows around Firebird.
First of all, a good question is how much money is being consumed by Firebird.
The only public information is a set of reports from Firebird Foundation. The last public report is from 2007 year: http://firebirdsql.org/ffoundation/plenary/page_finance_agm2007.pdf
Full set of FF documents is here: http://firebirdsql.org/index.php?op=ffoundation&sub=docs

Well, you can look at numbers in these reports yourself and make conclusions too. Probably many readers of this blogs have annual income more than whole FF has.
I don’t know the numbers from private companies who pay staffers for work at Firebird (part of full time), but I can suppose that their spending is in the same range.
Comparing this amount with money spent by different ventures or IT companies into other open-source databases and their forks we can say it’s low. I don’t speak here about MySQL investments and impressive final sale, but there some examples for PostgreSQL with several million investments (check Google for “PostgreSQL investments”).
There can be a lot of speculations about nature and intentions of venture capitalists and so on, but it seems quite obvious that our “bird” does not eat enough to grow fast enough…
But let’s get back to the initial question - Is Firebird Poor?
In terms of cash flow I think we need to say - yes, Firebird as virtual entity is poor.
But we need also to estimate the assets that Firebird has (since it’s a virtual entity I need to say “can leverage” or “is possible to increase money flow for people involved”, but I hope that “Firebird has” is clear enough). If someone has valuable assets – sure he is not poor .

Let’s start with web-site as it’s a well known asset for everyone.
Since firebirdsql.org has 5000+ visitors highly concentrated on Firebird and related areas, it can be a good source of money.
Let’s imagine a shop at this site, something like shop.firebirdsql.org with featured products at the main page of firebirdsql.org. General shareware rule gives us an empiric ratio of visit to sales as 100 to 1. Consider we have 50 sales of a software everyday (actually there can be more sales since there will be more products for different areas of Firebird-related things), 20 from featured products at main page and 30 from general shop, with average price USD$200. Getting 50% average commission from featured products and 30% from general shop will produce USD$200*20*%50 + USD$200*30*%25 = USD$ 3500. It’s per day, and ~1.2mln USD per year. Looks impossible? Ok, even if you imagine the hardest crisis for the next year, you can divide it by 10, so it can be 120k USD, the same as Foundation gathers from members, sponsorship and donations.
Many users of Firebird are also Delphi/C++ Builder users. Let’s ask them to buy from shop.firebirdsql.org to support Firebird and get sales commission from Embarcadero – here is another hundred of USD$k.
Advertising is also a good point to sell. Imagine the advertising of Oracle at some page? Looks abnormal? Really? Probably you richer than IBM – look here: http://www-03.ibm.com/systems/x/solutions/infrastructure/database/oracle.html
(This way firebirdsql.org also can sell InterBase, by example. No? Still feel you are richer than Mr. Gates?). And this is only the first point. Having community in several hundreds people it’s a good first step to build some nice social network sponsored by advertising (don’t forget that Google is an advertising agency and… they feel good :-) )
There were some opinions that firebirdsql.org is a technical site for project news and stuff only and it should keep spirit of open-source development, and if someone wants to make a shop he need to build it at some other place and so on…

Well, personally I don’t see anything bad in going in a quick way to get money for Firebird and use valuable asset in the most efficient way. I am pretty sure Firebird users will be happy with all-in-one site and they will understand and support the reasons of implementing Firebird-related shop near the downloads and news pages of Firebird.
But, of course, it’s possible that I am just not rich enough and I think too much about money… may be true open-source believers should work in basements and hope for … raise of machines? Come into a fortune from the rich uncle? Donation from mr. Abramovich?

From the organization point of view, to keep intact the independence and distributed nature of Firebird, shop activity (site redesign, orders processing, money flow management) can be outsourced to some company-operator.
The company-operator should be chosen at the open tender to correspond to required conditions and should sign service-level agreement to implement and reflect all important aspects of this shop’s operation.
Who need to perform this tender? What are the conditions? Who will decide how to spend money and there to invest it? (now there is no such question, because all amount is completely consumed by research and developments, but with $1M per year… hmm, Firebird can afford some marketing activities too)
Obviously the main source of decisions should come from current administrators of Firebird project. This will require some courage and will of power, but I am sure they have it. Some people can say that Firebird Foundation can play this role, but, sorry, it is a non-commercial organization. I am the member of Firebird Foundation, but my opinion is that Firebird entity need to set (and achieve) very specific business-oriented goals, such as growth of user base and increasing money flows for R&D and marketing, aggressive competition and so on.
Open source is good when it’s quickly developing and spreading over the world (like IBM does :-) ), but it seems that currently Firebird does not look like a very successful open-source project. I would say the numbers of users and installations are much lower than it can be.
I don’t intend to offence any Firebird Foundation members or Committee, they do a great job, but I truly believe that Firebird need to have more business-oriented management which will rule their assets more efficiently.

And let’s back to the assets :-) In the next part I’ll share some ideas how to use Firebird brand-name (which is currently owned by FF), source code and millions of installations to get several millions $ more for Firebird growth.

Wednesday, October 15, 2008

What is Firebird?

I’d like to clarify some facts about Firebird and Firebird Foundation. I think right now is a correct time to remind how all these things are organized, because some messages about "crisis, firebird's death, low finances" and so on being spreaded around.
It seems that many people think that Firebird Foundations owns Firebird, makes development strategic/tactic decisions or something like this. This is not real, in fact the Firebird Foundation is an institution to collect donations and sponsors money and grant them to people, related with Firebird development. To understand exactly who is who we need to look at the current situation with Firebird.

First of all, Firebird does not exist as an entity. There is no such thing as Firebird company/organization.
We can say that Firebird as virtual entity contains 2 parts: 1) set of material assets and 2) people who perform different activities.

Three main assets of Firebird are: source code and installers stored in Sourceforge.net CVS, website firebirdsql.org and set of services for maintaining bugs and feature requests. Source code is controlled by Project admins awharrison, bellardo, dchri, dimitr, helebor, pcisar, seanleyne, skywalker (http://sourceforge.net/projects/firebird/)
You may notice that some of them are working at Sun :-). Probably this list was not updated for some time. Of course, there are contributors granted by admins who can submit code to CVS and perform other activities with files at sourceforge.net repository.
The second Firebird asset is web site firebirdsql.org. Domain is owned by Helen Borrie according to the DNS records. The site itself is located at the dedicated server (according to tracert) in Canada.
The third asset is Firebird tracker dedicated server, also somewhere in Canada. It would be interesting to see a photo of these computers :-)
Not so much? But it stands more than 5000 visits every day and log more than 1000 installations at Windows platform per day (!), according to the statistics of the installer’s 'landing page'. Also there are more than 1 mln downloads of all Firebird installers and other files per year from the sourceforge:
http://sourceforge.net/project/stats/?group_id=9028&ugn=firebird&type=&mode=year

Groups of people are involved into Firebird projects are much more interesting. There are 102 developers listed at Sourceforge home of Firebird.
As you can understand, not all of them are performing equally. Many people listed there did not submit anything for a long time ago, but there are always a small active group of people who perform 99% of R&D (research and development) and other work. Some members of these groups are migrating slowly: people come and leave, as their interests and life goals are changing.

How these people are compensated by its work? There are two sources of money: people work at “usual job” and dedicate part of time in fact paid by employer, to the Firebird development (the brightest example of this approach is IBPhoenix), and the second source is granting from Firebird Foundation or from other organizations.

How are decisions made in such structure? Firebird development decisions are made by projects admins and leading contributors (they know each other personally) - they discuss and issue roadmaps and perform tasks assignments (using tracker.firebirdsql.org), and perform actual coding and testing.

Web site is made by efforts of Helen Borrie and Pavel Cizar (may be someone else is involved too, not sure).

Finance decisions of Firebird – well, there are no finance decisions in Firebird itself, as it is a virtual entity.
Firebird Foundation considers developers' work and allocates grants according the amounts they have, IBPhoenix and other companies, who employ developers of Firebird, pay to them according to some internal reasons.

Since source code of Firebird is under IPL and IDPL licenses, it’s not possible to sell Firebird itself.

HR decisions – I mean who should make this or that or who takes this (virtual) position. In development area this decisions are made in democracy style: currently most active developers (now leaded by core developer Dmitry Yemanov) consider and discuss a person and its work/commitment. For other activities like web site and documentation there are a few volunteers who make everything, from decisions to implementation (there are always not enough skilled documentation writers).

This is the current situation. Of course, it has some advantages and disadvantages. The biggest advantage that Firebird as a distributed entity is virtually immortal in terms of close of sources. Many people are still afraid that someone will “own” Firebird, close its sources and make it proprietary. According to IPL and IDPL this scenario is not possible, and Firebird will live even with the single person who will perform basic activities.
The biggest disadvantage is the reflection of distributed structure of virtual Firebird entity - is not suitable for large investments of money and volunteers efforts.

To be continued…

Tuesday, September 16, 2008

FBCon 08 - 25-26-27 september - Bergamo - Italy

You CAN'T miss FBCon 08 - 6th Firebird International Conference that will be held in Bergamo from 25 to 27 september. Conference will be from thursday september 25 till saturday Every day have a special theme with best italian and worldwide firebird-world expert!
Here some of our sessions, for a detail go to www.firebirdconference.net:

Italian sessions :
  • CTE e query ricorsive
  • Database per sysdba – avanzato
  • Database per sysdba – base
  • Database per sysdba – web
  • Delphi, DBExpress e Firebird
  • delphi4php e Firebird
  • GO: Gestionale Open
  • ISAPI
  • Php e Firebird
  • SQL and Firebird
  • SQL enhancements in FB 2.5
  • Sw-ing
  • vTiger

english sessions:
  • 10 ways to backup FB database
  • Common Table Expressions : tutorial
  • Delphi and Firebird
  • Developing Firebird apps for pocketpc/smartphone clients
  • Execute block e trigger (database)
  • Fb{Ref?}Doc - the Firebird Documentation project
  • Fire in The Lamp - FireTube an example for scaling Firebird on web2.0
  • Firebird 2.5 Architecture
  • Firebird Development in 2008/2009
  • Firebird in real time applications
  • Firebird on Linux - advanced topics
  • Firebird on Linux – basics
  • Firebird Stored Procedures in Depth
  • Firebird, tools and Linux distribution integration
  • Installing Firebird with your application under Windows
  • Introduction to development with Firebird and Python
  • Know what is happening in your database and server
  • Managing recursive data structures (trees) with Firebird
  • Migrating from MSSQL and mySQL to Firebird
  • Practical Guide to the Garbage Collection
  • Query optimization - how to speed up COUNT(*) function
  • Replication for a high availability infrastructure
  • Security in Firebird: 2.1, 2.5, 3.0
  • Separating web development from database logic
  • Service Oriented Architecture with Firebird
  • SQL: Migliorare e ottimizzare le prestazioni
  • Stored procedures
  • The Firebird System Tables
  • Towards a REST architecture with Delphi and Firebird
  • Transactions In Your Applications
  • Ultimate Vmware Appliance - Ubuntu and Firebird
  • Updating Database structures
  • Using embedded Firebird with .NET
  • Working with Firebird in Python

To REGISTER go to www.firebirdconference.net

Also note that 24-th September at the same place, before the Conference, there will be FREE IBSurgeon seminar:
  • Introduction
  • Firebird database recovery and protection against corruptions
  • How to find and solve performance problem in Firebird with IBSurgeon products
  • Replication with IBReplicator
  • Using dbFile
p.s. I will be there. My wife told me that I must taste native pizza, pasta, cappuchino, etc.

Sunday, September 14, 2008

Multi-file Database ?

Some last database repair cases we did were with multi-file databases. Interesting, that latest InterBase and Firebird versions was used, and also file system for the storage was NTFS, not FAT32 and FAT16. But, the databases was created and maintained using 1 gigabyte files.

If you still use InterBase 5 and less, stop reading this, please :-)

This is strange, because all InterBase and Firebird errors working with >4gb databases are gone, and also lot of people forgot about FAT32.
Some says that FAT32 is faster than NTFS, so they still use FAT32. Maybe this is true, but personally I don't see any reason to use old file system.
Moreover, once I had interesting case with FAT32: there was a 4gb logical drive with 3gb file. Maybe it was Windows 2000, but anyway, I couldn't copy this file to another logical drive with NTFS - operating system gave me an error when it tried to copy over 2 gigabytes of the file.

Well, maybe XP and Vista can copy 3gb files from FAT32 to NTFS, but multi-file databases have several disadvantages, that does not depend on file system:
  • some people think that multi-file database will have better performance than single-file database, on multi-processor/core computers and using InterBase SMP or Firebird Classic. This is wrong. Server does not do any "parallel" access to the secondary files. It treats multi-file database as one sequential file broken into parts. So, if table pages spreaded in several database files, table scan will scan these files one by one.
  • Multi-file databases have hard-links to the secondary files inside it, and it is impossible to move such a database from one logical disk to another (for example, from d: to e:). If you want to do this, you need to make backup and restore, or get hex-editor and hack this hardcoded drive letter and path in the header pages of the mult-file database. Single-file databases does not have this problem at all.
  • You need to watch over mult-file database's size, and from time to time, while database is growing, create additional secondary file with appropriate size. We've seen lot of databases with first files having some equal size, and the last file larger than other - DBA forgot to create additional secondary file at right time.
  • Secondary file size can be specified in 2 ways. Using STARTING AT, when you specify from what page number server will continue database in the next file, and using LENGTH, where you specify secondary file size in pages. The best way is to use LENGTH parameter for all files of the multi-tile database, including primary file and secondary files. But if you will start to mix STARTING AT and LENGTH you can loose understanding what size secondary files will have, and get multi-file database with different file sizes.
  • Backup and restore automated scripts must be updated from time to time to include additional secondary file, because there is no command-line parameters for gbak that could do automated restore of the database to create unknown number of secondary files. Each secondary file and it's size must be specified at the command line.
    This is really a headache for the DBA.
I think I gave you enough reasons to stop using multi-file databases, if you still do this. If you use InterBase 7, 2007, 2009, Firebird 1.0, 1.5, 2.x - at the nearest restore make your database single file, and forget about multi-file database completely.

upd: need to say that the well known (at least for us) database corruption for IB 5.x is when DBA forgets to add new secondary file, and the last secondary file comes to 4gb size.