|
Accessing DBMS remotely: MySQL? FireBird?
Hi,
Some of our customers have remote offices. I was wondering if it'd be safe to have a DBMS running at their central office, and have our client application running on hosts in the branches connect to it through a VPN via the Net?
What happens if the connection goes south while a branch office was making changes? Does the DBMS just rollbacks changes automatically after a time-out?
Should we set up some kind of replication instead?
Also, are there compeling reasons to go for Firebird instead of MySQL? I don't know enough about the capabilities of each DBMS today to make an educated choice.
Thank you for any tip
Fred
Saturday, May 1, 2004
"I don't know enough about the capabilities of each DBMS today to make an educated choice."
You might wanna read up then. MySQL is just getting most standard DBMS features (like transactions) and doesn't yet have triggers or stored procedures. I don't know too much about Firebird. PostgreSQL is an excellent open source database, you might want to look into it.
Almost Anonymous
Saturday, May 1, 2004
If we're talking about paying clients and their data, you need to hire either a consultant or a DBA to do this for you - if you're asking questions like this you should NOT be making the decision.
Seriously - get a consultant in to at least help with the setup; use that as a screen to decide if you want to keep them around longer term to help build and manage it.
Philo
Philo
Saturday, May 1, 2004
Speaking as someone who has done a considerable amount of work with all three DBMS mentioned, I'd like to warn you: that they all have serious problems in a number of departments. Firebird is the less problematic in terms of stability and bugs, but it's 1983's design, and feature-wise, it's a joke. Unless you're building something very simple, or a busy website (think MySQL), go with something different.
Egor
Saturday, May 1, 2004
Egor, care to elaborate on your problems with PostgreSQL?
Matt Conrad
Saturday, May 1, 2004
1. Speed. In my case it was prohibitively. Getting total number of records in a table took forever, among other things.
2. Bad, inflexible query planner. PostgreSQL has several types of joins (hash join, and others). If the planner chooses a suboptimal one for a part of the query (which it does far too often), the only way you can change that is to disable this join type for the _whole_ query, which can make it even slower, because for another pair of tables it may well be best. Adding a LIMIT clause can actually _slow down_ the query a great deal. Why is that, even the developers couldn't explain.
3. Buggy, features not working as advertised. I gave up on it after learning that absolute cursor positioning only works on "simple" queries, giving unpredifctable results on others. I was also getting repeated error message in the log, which text I don't recall, but it had to do with storage internals.
Egor
Sunday, May 2, 2004
Thank you for the input. BTW, we're only talking a few megabytes of data, nothing major :-) I'd just like to make sure that performance and stability is OK when accessing over the Net.
Fred
Sunday, May 2, 2004
Consider slapping a web service on top of MySQL (or any other database, for that matter) to abstract out the backend technology as well as the database schema.
Just give the clients the spec for the web service and you're ready to roll...
dir at badblue com
Sunday, May 2, 2004
Thanks, Egor.
Matt Conrad
Sunday, May 2, 2004
>> Firebird is the less problematic in terms of stability and bugs, but it's 1983's design, and feature-wise, it's a joke.
Care to explain why its feature set is a joke?
KillYourTV
Sunday, May 2, 2004
>> Firebird is the less problematic in terms of stability and bugs, but it's 1983's design, and feature-wise, it's a joke.
Care to explain why Firebird should be discounted because it's 1983's design? For same reason some say Unix should be discounted because it's 1970's design?
It's not like Interbase/Firebird haven't gone through many versions of updates and modifications. One major recent example could be the change from "Classic" to "Superserver" architecture.
Herbert Sitz
Sunday, May 2, 2004
Never allow remote connection to a database via public Internet. This is strictly no-no.
VPN will serve you nicely.
Use latest version of MySQL as a server.
If connection goes down:
1) if there were no active transactions, just reconnect
2) if you had running transaction, it will be rolled back
3) there is no substitute for smart programming and basic DBA understanding
Alex Tronin
Sunday, May 2, 2004
2) if you had running transaction, it will be rolled back
Only if you remember to use inno-db.
MR
Sunday, May 2, 2004
I'd have to agree with dir at badblue com. Wrapping up the data extraction process will allow you to change the implementation later if need be (add a layer of authentication, etc).
We have a php script on top of a series of data tables with one of our products. Everything is served up in php, depending upon the parameters passed in to the request. Works like a charm.
You're asking for BIG headaches if you allow clients to directly connect to the database.
Yep.
Monday, May 3, 2004
One more thing; MySQL has been rock-solid for us. No problems in the past year or so of production usage.
Yep.
Monday, May 3, 2004
Egor, why is Firebird "a joke"?
It is a serious, full-featured, SQL DBMS. That certainly can't be said of MySQL.
What "modern" features do you think are missing from Firebird?
HeWhoMustBeConfused
Monday, May 3, 2004
"Also, are there compeling reasons to go for Firebird instead of MySQL?"
You could ask this question in forums dedicated to each DB. Take a look at the level of support you get, what solutions you're presented with. This would give you a good idea about the community involved in each product.
Then, you could try some of those potential solutions, instead of just depending on someone else's experience, which include its own biases and level of ignorance.
Just looking around in dedicated forums may help you decide. E.g., in 1999/2000, I had a go at PHP. I wasn't even able to build it, because I wanted to run it on Solaris, with Netscape server and Informix. From what I could see on the PHP forums, the few people that had my problem were going nowhere. So, I ditched it, and told my boss we should go with JSPs (the other option we were evaluating at that time).
"I don't know enough about the capabilities of each DBMS today to make an educated choice."
I second Philo's suggestion - you should get someone with more DB knowledge to work with you on this.
Paulo Caetano
Monday, May 3, 2004
It's been a while since I last used Firebird, so I'm not going to come up with the full list of what it's been lacking. But one thing I do recall is its ridiculously small maximum key length, something on the order of < 100 bytes. Bye-bye compound indexes and practically any others on Unicode string fields. In my case I just couldn't migrate the DB that had unique index on city name field in Cyrillic (not even Unicode). There's an explanation for that in the documentation which says it's a limitation of obsolete design which can't be fixed without on-disc format change which is going to happen no one knows when.
Egor
Monday, May 3, 2004
So far as I know, the limit to index size in Firebird and Interbase is 250 and has been for several years. I find it hard to believe that that's a major limitation.
Also, both have supported UNICODE for some time. I think there was some issue that they wouldn't let you modify the collation settings, don't know whether that still exists or not.
In any case, it sounds wacky to me to imply that Firebird and Interbase are somehow an antiquated "1983" design.
Herbert Sitz
Monday, May 3, 2004
If it really is for a simple database with 'only a few megabytes of data' then I really can't see it making much difference whether you go with Firebird, MySQL or anything else. My personal recommendation based on 3 years development & production experience with it would be MySQL but it really doesn't sound like it will make an enormous amount of difference.
Unless you need any of the advanced features / speed / etc, which it sounds like you don't, go with whatever is easiest.
James U-S
Monday, May 3, 2004
Firebird is free for commercial applications.
MySql isn't in general. You can do some back-flips to get around the licensing, but it's generally more trouble than it's worth.
Chris Tavares
Monday, May 3, 2004
Let me rephrase that. If you want to distribute the database engine with your application, and it's a commercial app, you can do this with Firebird, where MySQL will require extra licensing.
Chris Tavares
Monday, May 3, 2004
Thx everyone for the input :-) I'll run a test app from a remote location against FB and MySQL (since PostgreSQL isn't available for Windows), and see how it performs over an ADSL + VPN link.
Fred
Monday, May 3, 2004
"Let me rephrase that. If you want to distribute the database engine with your application, and it's a commercial app, you can do this with Firebird, where MySQL will require extra licensing. "
I think MySQL goes even further than that with their claim.
Last I checked they said that if I built a front-end application specifically to work with MySQL and only MySQL (i.e., same code would not work with another database), then a commercial license was required for commercial deployment whether or not MySQL was installed separately by the client or not. Crazy -- and not a valid interpretation of the GPL as I understand it -- but I do believe this is MySQL's position.
Herbert Sitz
Monday, May 3, 2004
Here's explanation on Firebird key length issue from Interbase developer: http://www.mers.com/IBINDEXLENGHT.HTML It's 255 "behind the scenes", but in practice you're getting much less to work with.
MySQL is fine and all, but a few days ago it just stopped to insert records in a simple InnoDB table. Any attempt to do that would just restart the daemon. Repair tools were of no help. Luckily, I still could SELECT from the table, so I just duplicared the table as MyISAM table, and it worked again. Draw your own conclusions.
Egor
Tuesday, May 4, 2004
Egor, thanks for the info; it was certainly my mistake in thinking it was 250. I still don't think it's a major limitation, unless you need to index some very long strings. But I understand how it could nix Firebird/Interbase for some situations.
By the way, did you know that the MERS search site uses Interbase as it's back-end database? I believe it uses same setup as that at http://www.fulltextsearch.com (where MSFT newsgroups are also indexed), where Rubicon -- a proprietary full text indexing system -- is used to do searches on very large volumes of newsgroup records. For some, this would be a good way around need to index long strings, plus the full text indexing adds lots of other functionality.
Herbert Sitz
Tuesday, May 4, 2004
 Recent Topics
Fog Creek Home
| 
