aspose file tools*
The moose likes JDBC and the fly likes What database should I use? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "What database should I use?" Watch "What database should I use?" New topic
Author

What database should I use?

Ann Basso
Ranch Hand

Joined: Jul 26, 2007
Posts: 48
Hi,

I am working on a desktop application which requires really robust database support. By robust, I mean it should remain consistent even if the power cord of the PC is taken out all of a sudden. Of course, the transaction that was in the process of being written to the files may be lost but at least it should not get corrupt and upon restart it should be in a sane state.

So, is this even a valid expectation? Which free database would you suggest for this purpose ( as of now I am thinking of Mysql) ? Any thoughts on the approach that I should adopt to increase the robustness? Or any other pointers/pitfalls that I should be aware of?

In one of my other applications, I have a similar robustness requirement but the data is very small. So I store it in a simple file. For updates, I just update the file, flush the data, and then copy the file to a backup file. So in the worst case, at least one of the two files (main file or the backup file) is always in a consistent state. This approach has been working fine. But now, the data is quite big and varied and a database is required.


thanks for your help.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

MySQL.

As for redundancy, that's more about having good backup procedures and failover systems. If you want truly secure 100% bullet-proof systems (for payment processing for example), you need something like Oracle which is far from free.


My Blog: Down Home Country Coding with Scott Selikoff
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61419
    
  67

MySQL is popular. So is PostgreSQL.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ann Basso
Ranch Hand

Joined: Jul 26, 2007
Posts: 48
Originally posted by Scott Selikoff:
MySQL.

As for redundancy, that's more about having good backup procedures and failover systems. If you want truly secure 100% bullet-proof systems (for payment processing for example), you need something like Oracle which is far from free.


Thanks, Scott and Bear!
The down time is not an issue. It is ok if the app is down for a while (obviously, that would be the case when there is no power). So there is no need for a fail over. Only requirement is that the db should not get corrupt upon a random power off situation.

Do you think (or know or observed) that Mysql handles such situation well? i.e. if the mysql executabale is suddenly killed or the filer goes down while in the middle of a transaction, can it come back up without any failure (except the unfinished transaction)?

I am thinking that it would be a fair expectation from any database. Right?

How about on a *nix system? I remember reading somewhere that if a *nix system is not shutdown propertly, its file sytem gets corrupt and you have to reinstall the OS again. Is that really true? If so, the database can't really do much about it.

Has anybody encountered such situation?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

Unless your database is made on stone tablets, they can all get corrupted on a power surge. Imagine if a power surge blew the computer up for example, wouldn't matter what software you were using, the computer would be on pieces in your office. Consider also that all harddrives corrupt data at one time or another; it's not a matter of if but when. Other times they just die all together.

Preventing corrupted files really has nothing to do with the database; you need to backup often for any database. There are some systems that have better recovery for incomplete transactions such as oracle but unless you're doing tons of $ processing, its not really an issue. Still though, most (if not all?) transactional database engines support recovery.
Ann Basso
Ranch Hand

Joined: Jul 26, 2007
Posts: 48
Originally posted by Scott Selikoff:
Unless your database is made on stone tablets, they can all get corrupted on a power surge. Imagine if a power surge blew the computer up for example, wouldn't matter what software you were using, the computer would be on pieces in your office. Consider also that all harddrives corrupt data at one time or another; it's not a matter of if but when. Other times they just die all together.

Preventing corrupted files really has nothing to do with the database; you need to backup often for any database. There are some systems that have better recovery for incomplete transactions such as oracle but unless you're doing tons of $ processing, its not really an issue. Still though, most (if not all?) transactional database engines support recovery.

Thank you for your insight Though my situation is a little different from what you have described for the following reasons -
1. There is no power surge, only a power off and I am not contemplating hardware failure, but only a logical failure of the s/w to read the logical data on the file system or database.
2. "Backing up often" is a general solution for a hardware failure or lost data for any other reason and does not apply to the situation I am talking about. Nightly backups etc. are all fine but are not relevant here. You are not suggesting to backup the db after every transaction, are you?
3. I have repeatedly mentioned that an ongoing incomplete transaction may be lost and that is fine. There is no need to recover an incomplete transaction. Only requirement is to make sure that the database is consistent upto the last committed transaction. (Is that what you meant by recovery?)
4. Preventing corrupted files has indeed nothing to do with database but it defintely is an important aspect in preventing corrupted database since it depends on the file system.

Anyway, I hope I have described the problem clearly. I was just wondering if it is a common situation that any database will handle. If not, if there are any standard approachs to solve this problem.

thank you.
Ann Basso
Ranch Hand

Joined: Jul 26, 2007
Posts: 48
This is interesting - http://forums.mysql.com/read.php?22,161846,163047
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: What database should I use?