Is Firebird reliable, flexible, robust, or secured enough? lazarus / 16-Databases ---------------------------------------------------------------------- JZS · 2015-05-31 11:06 It is a debatable question, based on many factors, I know. I am sure many will have so much as input (either supporting, or not) for features that Firebird has over other RDBMS. Since I have relatively short experience with Firebird, I must say, I do not claim I know it very well, so I will mention what I have noticed and probably others, with better or more experience, can comment either to confirm or to correct, both are welcome. I find it a good way to point out things and during the discussion one might learn from someone manged to overcome something I just noticed recently, while others might tackled long ago. For sure it will help someone with same skeptics lingering without delving into the subject matter. Here I go: Is it flexible? It has nice flexibility, probably not matching MySQL/MariaDB and others, but for supporting SQL standards (2003 and soon 2008, as I read somewhere) that would be enough. Is it secured? Well, there is no such thing as 100% security, in many aspects, everything is vulnerable target. But at least do not make it 0%. I find giving a locked safe away to some one with the key tied to it or hidden under it, is way better than giving it open and there is no key. Firebird has no security whatsoever. The moment you get hold of the DB file you can extract whatever you need, without effort. Without even using any technique just install Firebird and connect to that DB file and you are good to go. Here is what hurts me much. I read in this link that it is pointless to make Firebird secured (encrypted) as long as you supply the key in anyway, to your clients. Well isn't that the case with all other secured DBs? Even MariaDB started making encrypted Tables/DBs, and guess what you are going to give the key with your application otherwise no one can use it. That was something I found so funny. If Firebird is not a match to MariaDB (Clients base, project size, or whatever the nature of comparison), take example ComponentAce which made a wonderful DB for Delphi, which is so nice and uses strong encryption (unfortunately they do not want to port it to Lazarus, due to handling many projects (lack of time issue), as they said). Does anyone have any experience in the negative impact of this? Is it reliable & robust? This is the part that matters, as much as security. I tested with latest stable version 2.5.4. The Database gets corrupted in case of ungraceful DB disconnection. I test by running the DB+app from memory stick and disconnected without properly closing the application. When running the application after the improper disconnect, it gives error but it repairs itself, but if it was repeated couple of times (let's say many times) is there any chance that the DB gets corrupted and become unrecoverable? Please note, I am aware that gfix/gbak or some additional tools can do the trick, but I need to imitate the normal user (client) course of action when using Firebird DB, and face one compelling situation, similar to my test. ---------------------------------------------------------------------- miab3 · 2015-05-31 12:30 @JZS, Quote from: JZS on May 31, 2015, 01:06:41 pmFirebird has no security whatsoever. The moment you get hold of the DB file you can extract whatever you need, without effort. In Firebird 3 this is no longer so easy. Michal -- I recommend: Zeos7.2-beta branches_ testing_Revision 3628: http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ (Firebird/InterBase, MariaDB/MySQL, PostgreSQL, SQLite, Oracle, Sybase, MSSQL, ADO(Windows); Lazarus, Delphi 7-XE8, CodeTyphon; 32/64-bit). Zeos7.3-beta branches_ testing_Revision 3630(test version): http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.3/ (Firebird/InterBase, MariaDB/MySQL, PostgreSQL, SQLite, Oracle, Sybase, MSSQL, ADO(Windows), OLE DB-MSSQL(Windows); Lazarus, Delphi 7-XE8, CodeTyphon; 32/64-bit). http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&start=338 http://zeoslib.sourceforge.net/viewtopic.php?f=40&p=44589#p44589 ---------------------------------------------------------------------- taazz · 2015-05-31 15:11 Quote from: miab3 on May 31, 2015, 02:30:43 pm@JZS, Quote from: JZS on May 31, 2015, 01:06:41 pmFirebird has no security whatsoever. The moment you get hold of the DB file you can extract whatever you need, without effort. In Firebird 3 this is no longer so easy. Michal -- I recommend: Zeos7.2-beta branches_ testing_Revision 3628: http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ (Firebird, MariaDB/MySQL, PostgreSQL, SQLite, Oracle, Sybase, MSSQL, ADO(Windows); Lazarus, Delphi, CodeTyphon; 32/64-bit). Zeos7.3-beta branches_ testing_Revision 3630(test version): http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.3/ (Firebird, MariaDB/MySQL, PostgreSQL, SQLite, Oracle, Sybase, MSSQL, ADO(Windows), OLE DB-MSSQL(Windows); Lazarus, Delphi, CodeTyphon; 32/64-bit). http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&start=338 1) 3 has not been released yet. 2) any links to see what you are talking about? ---------------------------------------------------------------------- rvk · 2015-05-31 16:01 Quote from: JZS on May 31, 2015, 01:06:41 pmThe Database gets corrupted in case of ungraceful DB disconnection. I test by running the DB+app from memory stick and disconnected without properly closing the application. Do you know of any proper DB-engine, reliable and robust, that can run from a memory stick and can recover itself from disconnecting without problems??? Did you "tune" Firebird to use direct-write options and not buffer things in memory? When doing this the corruption should be much less (or non-existing) than what you are experiencing now. So when using a Firebird-DB from memory-stick you should always do this. Can I use Firebird database directly from a USB flash disk? You might want to use the solution of copying the database to local- drive and working on that copy. When done you could copy the DB back. When something goes wrong you could signal the user (on startup the next time) there is already a local copy present and ask if he wants to use that copy (instead of copying a fresh copy to local). ---------------------------------------------------------------------- miab3 · 2015-05-31 16:10 @taazz, Quote from: taazz on May 31, 2015, 05:11:35 pm1) 3 has not been released yet. 2) any links to see what you are talking about? For half a year I use it with ZEOS without special problems. http://firebird3.com/ http://www.firebirdsql.org/en/snapshot-builds/ http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird- 3.0.0_Beta1-ReleaseNotes.pdf Michal ---------------------------------------------------------------------- exdatis · 2015-05-31 17:03 I tried it, looks good, security offers through an installation, also Flamerobin and EMS Firebird(Ibsqlmanager) works nice. Thanks for an information. Regards p.s. What do you think about Zeos, is it fast and stable enough? ---------------------------------------------------------------------- miab3 · 2015-05-31 17:30 Firebird 3, for simple queries, is similar speeds up to 2.5. Here is what is currently being corrected: http://tracker.firebirdsql.org/browse/CORE/fixforversion/10586?selecte d=com.atlassian.jira.plugin.system.project:version-summary-panel Michal ---------------------------------------------------------------------- JZS · 2015-05-31 17:55 Quote from: miab3 on May 31, 2015, 02:30:43 pmIn Firebird 3 this is no longer so easy. I will try it. Quote from: rvk on May 31, 2015, 06:01:16 pmQuote from: JZS on May 31, 2015, 01:06:41 pmThe Database gets corrupted in case of ungraceful DB disconnection. I test by running the DB+app from memory stick and disconnected without properly closing the application. Do you know of any proper DB-engine, reliable and robust, that can run from a memory stick and can recover itself from disconnecting without problems??? Did you "tune" Firebird to use direct-write options and not buffer things in memory? When doing this the corruption should be much less (or non-existing) than what you are experiencing now. So when using a Firebird-DB from memory-stick you should always do this. Can I use Firebird database directly from a USB flash disk? You might want to use the solution of copying the database to local- drive and working on that copy. When done you could copy the DB back. When something goes wrong you could signal the user (on startup the next time) there is already a local copy present and ask if he wants to use that copy (instead of copying a fresh copy to local). Do you consider SQLite a proper DB-Engine? SQLite does not suffer anything. Isn't forced write/direct-write the default mode? Although I tested from memory stick, but I have no intention to use any. That was just for simulating sudden disconnection (test environment). ---------------------------------------------------------------------- rvk · 2015-05-31 19:36 Quote from: JZS on May 31, 2015, 07:55:19 pmDo you consider SQLite a proper DB-Engine? SQLite does not suffer anything. Well... mmmm... yes and no (Woops... I walked right into that one ) I do consider SQLite a DB-Engine... (kind of...) but with a very specific purpose. Maybe you could rather call it a DB-Library instead of a DB-Engine because it has no server-component. I don't think you can compare SQLite and Firebird (or any other real client/server DB- system) with each other. If you really did your research and find SQLite to be an option, then you probably should go with that. But if you didn't research this you need to do this first. SQLite itself doesn't claim to be comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, SQL Server or Firebird: Appropriate Uses For SQLite QuoteSQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.(read-up on it there if you haven't done so already) So... if you need a real client/server option with high traffic, with multiple users, SQLite isn't for you. If you create a DB with many thousands of records (multi GB databases), SQLite isn't for you. If you just need a small DB-engine with some tables, simple distribution, SQLite might be for you. I would expect SQLite not to have much problems with sudden disconnects because it's setup (internally) is entirely different. But you should read-up on the downsides of SQLite. I, for one, already got bit once because of the fact that SQLite is typeless but that can also be an upside if you want this (and know about it). If you want to grow and want to implement a client/server architecture at some point in the future, you better take that into account now instead of converting to it later. (Also... the foreign key-option needs to be activated manually during the connection before you can use it. It's not on by default. That also shows it's not really a RDBMS like the other ones) ---------------------------------------------------------------------- lazjump · 2015-06-01 01:48 I used Firebird around 2006-2008 (so what I say below might be obsolete) for some projects. Here are 2  cents of mine: QuoteIs it flexible?It depends on what we define as "flexible". To me it is not flexible because things like this: create a table with a field of type VARCHAR(50). And then try to change it to VARCHAR(40). It will refuse to do that even on a newly created table that is 100% empty, even you are the owner of the table and you are the only one that is currently accessing the table. QuoteIs it reliable & robust?Among MySQL and PostgreSQL, I think Firebird is the most reliable in case of ungraceful connection and power outages. In my cases, I didn't have to do anything to repair. And I have seen broken InnoDB tables beyond repair because of a power outage. PostgreSQL is somewhere in the middle. I even have a "war story" about the reliability of Firebird. Once upon a time, lightning struck the electrical installation on one of our project site, where we implemented a system with Firebird. The lightning killed the server (a regular PC) and 4 client PCs. It was a quite a horror story for the employees because it happened at working hour. We were contacted for help "to salvage whatever could be salvaged". Long story short, we replaced the circuit board of the hard disk (it was a Maxtor hard disk) and managed to copy the FDB file to another computer. And upon connecting to the database file... it just worked like nothing ever happened! The data is 100% there. ---------------------------------------------------------------------- Groffy · 2015-06-01 07:00 I never understood this special "MySQL magic" or however you will name it. MySQL was for a long time no serious competitor against the only true opensource & free databases PostgreSQL and Firebird. MySql had only one transactional engine - InnoDB - and this was comercial. They hired the brain of Interbase/Firebird - Jim Starkey to bring their own solid database engine. But unfortunately the Falcon engine never was released, MySQL sold to Ocacle. Firebird is ROCK solid, easy to configure (mostly you don't need to touch the config file at all) and easy to handle. The question is what flexibility means for you concerning a RDBMS? You want to have the complete database + server on your stick? Here we go : put the Firebird embedded client together with a handfull of dll on your stick and carry your complete database system whereever you like. The same is possible on a cd/dvd - mark the database as readonly and run the embedded server directly from cd/dvd. That is what I call flexible... Furthermore I suggest to read : http://www.firebirdsql.org/file/documentation/papers_presentations/htm l/paper-fbent-capable.html Best regards - Ulrich ---------------------------------------------------------------------- JZS · 2015-06-01 12:34 Quote from: rvk on May 31, 2015, 09:36:35 pmQuote from: JZS on May 31, 2015, 07:55:19 pmDo you consider SQLite a proper DB-Engine? SQLite does not suffer anything. Well... mmmm... yes and no (Woops... I walked right into that one ) I have good experience using SQLite, but this time it does not fit what I need. So am using Firebird. I am aware of the difference. Also I have read the mailing-list discussion about SQLite vs Firebird. I am not going to restart that either So with Firebird, as am still finding my ways around (although i have tried it couple of times before, but never used it in a real project. This would be my first), I just noticed those few things and wanted to get more details from experienced users/experts. ---------------------------------------------------------------------- JZS · 2015-06-01 13:03 Quote from: lazjump on June 01, 2015, 03:48:17 amI used Firebird around 2006-2008 (so what I say below might be obsolete) for some projects. Here are 2  cents of mine: QuoteIs it flexible?It depends on what we define as "flexible". To me it is not flexible because things like this: create a table with a field of type VARCHAR(50). And then try to change it to VARCHAR(40). It will refuse to do that even on a newly created table that is 100% empty, even you are the owner of the table and you are the only one that is currently accessing the table. Still the same in ver. 2.5.4, has the same difficulty when rearranging of fields. Quote from: lazjump on June 01, 2015, 03:48:17 amQuoteIs it reliable & robust?Among MySQL and PostgreSQL, I think Firebird is the most reliable in case of ungraceful connection and power outages. In my cases, I didn't have to do anything to repair. And I have seen broken InnoDB tables beyond repair because of a power outage. PostgreSQL is somewhere in the middle. I even have a "war story" about the reliability of Firebird. Once upon a time, lightning struck the electrical installation on one of our project site, where we implemented a system with Firebird. The lightning killed the server (a regular PC) and 4 client PCs. It was a quite a horror story for the employees because it happened at working hour. We were contacted for help "to salvage whatever could be salvaged". Long story short, we replaced the circuit board of the hard disk (it was a Maxtor hard disk) and managed to copy the FDB file to another computer. And upon connecting to the database file... it just worked like nothing ever happened! The data is 100% there. Thanks for sharing your experience. ---------------------------------------------------------------------- JZS · 2015-06-01 13:12 Quote from: Groffy on June 01, 2015, 09:00:42 amI never understood this special "MySQL magic" or however you will name it. MySQL was for a long time no serious competitor against the only true opensource & free databases PostgreSQL and Firebird. MySql had only one transactional engine - InnoDB - and this was comercial. They hired the brain of Interbase/Firebird - Jim Starkey to bring their own solid database engine. But unfortunately the Falcon engine never was released, MySQL sold to Ocacle. Firebird is ROCK solid, easy to configure (mostly you don't need to touch the config file at all) and easy to handle. The question is what flexibility means for you concerning a RDBMS? You want to have the complete database + server on your stick? Here we go : put the Firebird embedded client together with a handfull of dll on your stick and carry your complete database system whereever you like. The same is possible on a cd/dvd - mark the database as readonly and run the embedded server directly from cd/dvd. That is what I call flexible... Furthermore I suggest to read : http://www.firebirdsql.org/file/documentation/papers_presentations/htm l/paper-fbent-capable.html Best regards - Ulrich By flexibility, I meant controlling your DB (structure and data, i.e SQL). Jim Starkey "the wolf", new info. for me. Thank you. I will read that link. ---------------------------------------------------------------------- rvk · 2015-06-01 13:33 Quote from: JZS on June 01, 2015, 03:03:50 pmQuote from: lazjump on June 01, 2015, 03:48:17 amI used Firebird around 2006-2008 (so what I say below might be obsolete) for some projects. Here are 2  cents of mine: QuoteIs it flexible?It depends on what we define as "flexible". To me it is not flexible because things like this: create a table with a field of type VARCHAR(50). And then try to change it to VARCHAR(40). It will refuse to do that even on a newly created table that is 100% empty, even you are the owner of the table and you are the only one that is currently accessing the table. Still the same in ver. 2.5.4, has the same difficulty when rearranging of fields. Expanding a VARCHAR field is not a problem. Shrinking it, however, could result in a loss of data. That's why it's not permitted. The engine could have checked if all data fits into the new one but it doesn't do that. You need to create a new field, transfer the data with the decision with what you want to do if the strings don't fit, and then drop the old field. Yeah... maybe not flexible in that way but for me that doesn't happen a lot (or at all). ---------------------------------------------------------------------- Groffy · 2015-06-01 14:11 Quote from: JZS on June 01, 2015, 03:12:36 pmBy flexibility, I meant controlling your DB (structure and data, i.e SQL). There are some DDL operations which are blocked, thats also a question of the database design which should start before start the modelling. It always can happen that you want to change the datatype of a field, that out of any reasons for example a smallint is better than a float, but this is not a permanent process and in any case you can rename the old field, implement the new field, transfer the data and delete the old field definition (as rvk described it already). "Controlling" the structure of your database is also a question of the tools you want to use. Thats a point where the default Firebird installation has not much to offer. For a more complex database, especially with some complex triggers and stored procedures you should use a third party tool for creation and testing. Some are free, some are comercial - depending also under which platform you want to design your database. Furthermore Firebird has no such build in functionality like replication, full text search, (both as external solutions) ,  XML or JSON datatypes. ---------------------------------------------------------------------- exdatis · 2015-06-02 09:38 I have a few app with Firebird2.5(embedded and server-client) and I have not a problem, so, it's quite well server solution. Also it working so fast, in my opinion good solution. p.s. For a many years I used postgreSQL and Firebird , at last my choice is the Firebird. ---------------------------------------------------------------------- JZS · 2015-06-03 12:22 I must say Firebird 3 is very promising to be up to the many-required- features in any DB. Many thanks Michal. I already deployed the project using Firebird 3, after it has proved OK (read the post subject) under several tests. Let's see how it goes after sometime. Regards, J ---------------------------------------------------------------------- avra · 2015-06-04 07:55 Firebird is very flexible. There even used to be a Oracle compatible Firebird incarnation called Fyracle: http://www.janus-software.com/fb_fyracle.html It was good enough even at v1.5 to run Compiere (big oracle based ERP+CRM application). Unfortunately development has stalled long time ago. Provided link shows rough features db comparison chart at that time. Firebird stood pretty good, and that has probably only improved. I used first Interbase and then FB from v1.0. Not much problems since day one. It can even be embedded. If you need max speed and performance possible on the market and you are ready to invest in software and db administrator(s) then ORACLE and MSSQL would be recommendation (from my corporate world db experience), but if you need close to zero administration and just want to simply use it then FIREBIRD and POSTGRESQL are probably the best choices in free db servers range. ---------------------------------------------------------------------- valdir.marcos · 2015-06-04 14:26 Firebird 3.0 Beta 2 on it’s way http://www.firebirdnews.org/firebird-3-0-beta-2-on-its-way/ http://www.firebirdsql.org/en/roadmap/ http://www.firebirdsql.org/file/community/conference- 2014/pdf/01_firebird_devplanning.pdf http://en.wikipedia.org/wiki/Firebird_%28database_server%29 ---------------------------------------------------------------------- valdir.marcos · 2015-06-04 14:39 I used to use Interbase on Novel Netware, Windows and Linux almost 2 decades ago. And then I have been using Firebird (on Linux and Windows) since its first alpha in 2000 with great success both for small and big projects. I am already using Firebird 3 beta 1 very successfully, with some care of course because it is not a final release yet. ---------------------------------------------------------------------- miab3 · 2015-06-06 11:08 Quote from: exdatis on May 31, 2015, 07:03:51 pmp.s. What do you think about Zeos, is it fast and stable enough? I missed your question about ZEOS. ZEOS 7.2beta r3628(currently): http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ seems to be stable. ZEOS 7.3beta r3632(currently) (with added OLE DB): http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.3/ similarly. These are the most stable, fast and versatile versions ZEOS ever. Absence of publication the final version (especially 7.2), primarily due to lack of time developers. Here I added (maybe less interesting Lazarus users only) package for Delphi XE8: http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=3795&start=345#p44 036 Michal ---------------------------------------------------------------------- exdatis · 2015-06-06 12:43 Thank you very much, miab3! ---------------------------------------------------------------------- Graeme · 2015-06-06 20:25 I would opt to use Firebird 2.5.x over MySQL/MariaDB and SQLite any time of the day! Our company did extensive testing for a few months before we opted to use Firebird for all our products. We tested MySQL, MS SQL Server, Oracle, PostgreSQL MS Access. SQLite is NOT a RDBMS so excluded that immediately. MySQL and MariaDB was a total joke, and nobody should consider that. SQL Server and Oracle were good, but totally out of our target audiences price range. PostgreSQL was good, but required a lot of config fiddling to get any good performance. Firebird was small and simple, and rock solid. We simulated power failures during read/write etc and Firebird always recovered. Moving the DB to different OSes were a breeze and there are plenty of tools to use with Firebird. We have dozens of 10GB+ Firebird databases which have served us very very well over the last 8 years. Local/Embedded mode is a bonus, the new server side tracing support is fantastic and really looking forward to the v3 release. I can show you examples of why we thought MySQL and MariaDB are a joke - if you are interested. ---------------------------------------------------------------------- Hansvb · 2015-06-07 07:11 Why is mariadb not good? I recently saw an article where they said that google was leaving oracle and got mariadb ---------------------------------------------------------------------- JD · 2015-06-07 07:34 Quote from: Graeme on June 06, 2015, 10:25:46 pmI can show you examples of why we thought MySQL and MariaDB are a joke - if you are interested. Firebird is also my favourite DB but to say MariaDB is a "joke"? I would like to know what bad experiences you had with it. They could help me.  PostgreSQL in my opinion is up there with Firebird & I'll choose it over Firebird for a client with a DB Administrator requiring DB security because like you said it needs some tweaking for top performance. ---------------------------------------------------------------------- JZS · 2015-06-08 09:31 The lack of publicity is not giving Firebird its due value. I knew Firebird long ago, but never knew its capabilities until posted here. Thank you all. Quote from: Graeme on June 06, 2015, 10:25:46 pmI can show you examples of why we thought MySQL and MariaDB are a joke - if you are interested. I am interested. I do have some projects using MySQL and MariaDB. ---------------------------------------------------------------------- sin_dragan · 2015-06-08 10:09 What tools did you use with Firebird during your testing? Can you name them all? Thats a good thing about FB, I also use it in my projects. ---------------------------------------------------------------------- exdatis · 2015-06-08 11:33 See: https://www.ibphoenix.com/download/tools/admin http://www.ibexpert.net/ibe/ {* excellent! *} http://www.flamerobin.org/   {* excellent! *} http://www.sqlmanager.net/en/products/ibfb/manager {* excellent! *} ---------------------------------------------------------------------- Graeme · 2015-06-08 12:38 Quote from: JD on June 07, 2015, 09:34:01 amFirebird is also my favourite DB but to say MariaDB is a "joke"? I would like to know what bad experiences you had with it. They could help me.  Follow the link below. I suggest you read the whole thread for lots of surprises. But if you don't have the time, at least read the first message. MySQL simply ignores DB rules and makes up its own as it goes along. The case sensitivity of the file system also plays a role on how your SQL will function (or not). It's all just a bit crazy. http://forum.lazarus.freepascal.org/index.php/topic,20826.msg121187.ht ml#msg121187 Disclaimer: As I mentioned, we did extensive testing years ago. At that point MariaDB did not exist. But considering that MariaDB is just a community fork of MySQL, I'm making the assumption that they are still pretty much the same. ---------------------------------------------------------------------- lazjump · 2015-06-08 13:50 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 · 2015-06-08 14:15 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 · 2015-06-08 14:35 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? QuoteThe 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 · 2015-06-08 14:45 Having such an option is just wrong!Having that option disabled by default is even worseMySQL 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 · 2015-06-08 16:10 Quote from: lazjump on June 08, 2015, 03:50:17 pmIn 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 · 2015-07-05 10:57 It seems that it's already Firebird 3.0 RC1 (r31920): http://tracker.firebirdsql.org/browse/CORE/fixforversion/10586?selecte d=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#p4458 9 http://svn.code.sf.net/p/zeoslib/code-0/branches/testing-7.2/ Michal ---------------------------------------------------------------------- gour · 2015-08-31 15:29 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... Quote from: Graeme on June 06, 2015, 10:25:46 pmI 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 · 2015-08-31 19:16 Quote from: JZS on June 03, 2015, 02:22:02 pmI 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 · 2015-09-01 02:12 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 · 2015-10-19 00:30 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 · 2015-10-19 22:23 Quote from: gour on August 31, 2015, 09:16:26 pmit 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 · 2015-10-23 18:47 Ummmm... SQLite does have a server facility. It's called SQLitening and it's authors are real nice guys. Hope that helps ---------------------------------------------------------------------- JZS · 2015-10-23 23:42 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 · 2015-10-24 00:52 Quick look, that can't be used with Lazarus or FPC. Sources are in basic!!! ---------------------------------------------------------------------- bobl · 2015-10-24 08:55 >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.