Is Firebird reliable, flexible, robust, or secured enough?

[lazjump]

Uhmm... Pardon me, I don't get the joke reading the link above.

Like reply #5 in that thread said, it is a documented feature. I use MySQL and I'm fully aware of that feature. And I have never hurt myself or any of my client with that.

The same goes case insensitivity and also for the SQLite example in that thread too.

In this thread I said that Firebird is not flexible because I can't change VARCHAR(50) to VARCHAR(40) even if the table is empty and I am the owner of the table and nobody else is accessing the table. But that don't give me any right to call Firebird a joke...

It is not fair to say a software is a "joke" just because the way they behave do not conform to what we think they should behave.

[Graeme]

MySQL simply ignored my table schemas. I said don't allow NULL values, yet it didn't complain and just went ahead with the INSERT statement - no errors reported.

Then to make matters worse, MySQL decided on its own to insert the value 0 into those NOT NULL fields! In my data, 0 could have a totally different meaning to NULL or any other value - they are definitely not equal or have the same meaning.

Inserting a string into an integer field still blows my mind!

Table and field case sensitivity due to the underlying filesystem? What the hell - the file system shouldn't dictate anything regarding my database system or SQL statements. Moving a fully working application's database from Windows to Linux or FreeBSD could potentially break the application. That is nuts!

Yes, yes, I know there is apparently some setting that can alleviate some of these issues. But the fact is, I tested on a Linux and FreeBSD system and found the same issues. On the FreeBSD system I installed via ports, which means it downloads the MySQL source code directly from the MySQL developers and compiles a stock database server on my system. So the default settings are the worst settings in the world!

You simply can't put a value on data (it could be worth billions), and for MySQL to simply f*ck with your data like that is a joke!

Your example of changing a Firebird DB field from varchar(50) to varchar(40) is a completely different topic. The Firebird server is trying to prevent you from loosing data (by truncation) - SQL Server and Oracle do the exact same thing here. Just what you would expect from a good RDBMS. You can work around that without much trouble (copy the data to a new table, rename the table etc) - but then it is clear that the database administrator would be to blame if any data is corrupted during the process.

The database server's job is to keep your data safe, and what you put in is what you expect to get out. MySQL doesn't adhere to this.

[lazjump]

I don't know how to put it... it is a documented feature/setting of MySQL that is well known to MySQL users. MySQL is just doing what said in the manual.

How could you missed that when you were doing extensive testing?

The database server's job is to keep your data safe, and what you put in is what you expect to get out. MySQL doesn't adhere to this.

One just need to toggle a documented switch to make MySQL adhere to that.

[Graeme]

• Having such an option is just wrong!
• Having that option disabled by default is even worse
• MySQL gives no warning that you have sub-standard (potential data loss or corruption) options enabled/disabled.

How many people do you know that read documentation from beginning to end before they start using software? I don't know any such people. So my second point above is vital.

Again - our tests were performed around 2008. MySQL still hasn't changed the default options. They still have no regard for your data. So what else, that we haven't found yet, does MySQL also do (or not do)?  I'm sorry, but just because many believe in the hype of MySQL and the whole LAMP thing, doesn't mean I'll fall for it. I'll rather use a database system that comes standard with sane options and adheres to SQL standards (schema rules) out of the box.

[tonyw]

In this thread I said that Firebird is not flexible because I can't change VARCHAR(50) to VARCHAR(40) even if the table is empty and I am the owner of the table and nobody else is accessing the table.

This observation is really useful if you want to characterise MySQL on one side and Firebird and Oracle on the other, and responses to it say more about what the poster is looking for in a database than whether one is better than another. MySQL is "fault tolerant" while FIrebird and even more so Oracle are fault intolerant. That is you can do some very silly things with MySQL and it will do its best to work with this, while Firebird and Oracle will stop with an error and tell you not to do that.

If you can tolerate the odd bit of data corruption and don't want the bother of chasing down that many bugs then MySQL is a good choice. On the other hand, if loss of data integrity bothers you and every bug must be eliminated, then you should do down the road of Firebird or Oracle.

For the record, this is my (incomplete) list of "fault tolerance" that I have found with MySQL over the years:

1. ENUM types. Set an enum type to a value not in the list then the value is set to an empty string rather than an error being declared.

2. Triggers (or at least their actions) don't always rollback when a transaction is rolled back.

3. Foreign keys can often be left pointing to non-existence references.

4. PSQL syntax is not fully checked until the procedure/trigger is executed.

I will apologise in advance if any of the above have been fixed in a recent release.

[miab3]

It seems that it's already Firebird 3.0 RC1 (r31920):
http://tracker.firebirdsql.org/browse/CORE/fixforversion/10586?selected=com.atlassian.jira.plugin.system.project:version-summary-panel
http://www.firebirdsql.org/en/snapshot-builds/
http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Beta2-ReleaseNotes.pdf

and should work with Zeos7.2-beta branches_ testing_Revision 3636:
http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&p=44589#p44589
http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/

Michal

[gour]

Hello Graeme,

I got my Object Pascal book and have started reading/learning, but, nevertheless, considering options/alternatives for my planned app.

As you might remember, I was thinking to use Sqlite3 as storage-back-end and, atm, not considering multi-user RDBMS with concurrent access etc.

Otoh, I'd like to have properly typed data...

I would opt to use Firebird 2.5.x over MySQL/MariaDB and SQLite any time of the day!

so if we eliminate MySQL/MariodDB/PostgreSQL, would you also recommend same thing if it goes about Firebird vs Sqlite3?

I must say that, based on short research, Firebird really looks great, so wondering why not use more and/or being more popular...

[gour]

I must say Firebird 3 is very promising to be up to the many-required-features in any DB.

I just wonder about deploying Firebird-powered app...it looks that on the Linux "you can't just ship libfbembed.so with your application and use it to connect to local databases. Under Linux, you always need a properly installed server, be it Classic or Super." (from the docs...) Isn't in such case better to use Sqlite3?

[bylaardt]

My experience:

In the past i had a system with a non selectable DBMS, and every client had a different opinion about security, support and stability.
I made a kind of pre-SQLparse who eliminate the corresponding expressions in relation of the chosen DBMS:
'/*sqlite '
' sqlite*/'
'/*no_sqlite '
'no_sqlite*/'

'/*mysql '
' mysql*/'
'/*no_mysql '
'no_mysql*/'

and so on...

when no match found, every code in /* */  are considered comments and i can handle the diferences between the DBMS, like this:
|| or concat(
Least() or min()
/ or div (with integers)


My clients can choose between MySQL, Postgres, SQLite, MariaDB,  Oracle, MS-SQL, Firebird.

My DUMP routine use the same parse. This give autonomy to migrate SGDB any time.

PS: I Use ZEOS 7.4 and works fine.

Conclusions:
every client can use a different DBMS
MySQL (or maria DB) is the most chosen;
Oracle is the choice for who needs commercial support
Firebird and Postgresql are rare but have followers
and i waiting anyone use MS-SQL...
Maybe with new versions Firebird or Postgresql migrations can happen.

[JZS]

More than four months now, since I started using Firebird, and it proved that it is really a competitive option.

I realized now, after this long time, what happened and made me start this thread.
If you are not handling your DB properly you get some issues, which confuses you, and you end up uncertain whether it is your application to blame or the DB performance. In most cases it will be the application (coding).

When I tested the DB from the memory stick and the DB file was disappeared at sudden it seems like the DB was still preserving open transactions and hence the very next connection the server points to WriteFile/ReadFile error. But I had to connect/disconnect several times for the DB to close the transaction, I can't say for sure I know the right way to recover from that (Lost DB File while connection is active) but sure there is a way to test such ungraceful actions and do repair before connecting.

Also I have a new concern, which is the DB file size, not sure how it will be like when working with several GB (probably more than 10GB) DB file. I know there are case studies of hundreds GBs DB files but having experience in DBs I can tell, your 1TB can perform better than someone else's 1GB, and the factor is not only how you setup your DB, there might be many others.

Does anyone have had some +/- experience on this, or advice?

[Graeme]

it looks that on the Linux "you can't just ship libfbembed.so with your application and use it to connect to local databases. Under Linux, you always need a properly installed server, be it Classic or Super."

That is not 100% correct. You don't always need a fully installed "database server" - you can deploy your application with libfbembed.so. All you need are the 2 or 3 additional .so files too. They are all pretty small .so files, so not a really issue when it comes to disk space.

I have successfully deployed Embedded Firebird under Windows, Linux and FreeBSD. The latter two are pretty much identical in required .so files for a successful Embedded Firebird deployment. I'll try and hunt through my archived projects to find the exact list of .so files for Embedded Firebird under Linux and FreeBSD.

[bobl]

Ummmm... SQLite does have a server facility. It's called SQLitening and it's authors are real nice guys. Hope that helps

[JZS]

I always get fascinated by hard work and good efforts.

It appeared, as per the forum's posts, late in 2009 but no one pointed it out through all the wars of discussing RDBMs. Apparently it has a very low publicity.

Anyways, not yet tested, downloading and testing now.

[JZS]

Quick look, that can't be used with Lazarus or FPC.

Sources are in basic!!!

[bobl]

>Quick look, that can't be used with Lazarus or FPC.
Yes sources are in Powerbasic but Someone used it in a retailers with MSVC6 so not just basic.