Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am just starting to learn about databases and using mysql. I read that databases are more reliable than having large ascii text files and that ascii text files are more prone to corruption as they get bigger.

Can someone explain why this is the case and how databases safeguard against this in a high level? Thank you.

share|improve this question
add comment

3 Answers 3

up vote 5 down vote accepted

I can only speak to MS SQL Server not MySQL I'm afraid. SQL Server breaks up a large database into small pieces called PAGES that are 8k in size. There are a couple of advantages here. With a large text file any time you make a chance you re-write the entire file. With a SQL Server when you make changes you only write down the PAGES that have changed. This means that instead of writing megabytes an gigabytes or even terabytes you are writing down information in 8K chunks. Less writing means less chance of causing corruption.

So what happens once you have some corruption? Well in a text file you won't realize that the corruption exists until you happen to be looking at the corrupted portion of the file or the file will no longer open. This means that over time more and more corruption will build up over time. In SQL Server each page has a CHECKSUM built in. This CHECKSUM is checked under several conditions. Every time that page is written, when backups are taken with the WITH CHECKSUM option and when DBCC CHECKDB is run. As a side note DBCC CHECKDB checks for a number of different possibilities of corruption not just the CHECKSUM. Because of all this if you (or your DBA) is careful they will find the corruption more quickly.

Which leads us to repairing the corruption. In a text file if your network admin is taking proper backups then you can hopefully restore your file back to before the corruption. If you caught the corruption early enough. If you didn't there may not be a backup old enough. Also your only option is to restore the entire file. This means any changes made to your file since the corruption occurred will be lost. Databases provide a number of restoration methods. If you are taking regular backups you can of course just restore the database. This isn't much different than your text file. However at least in SQL Server there are also options to restore parts of a database even down to the PAGE level. And in fact with certain types of high availability there is even the option of automatically fixing a page as soon as corruption occurs.

Here is some additional reading for you.

SQL Skills postings on page checksums.

Understanding Pages and Extents

DBCC CHECKDB and database integrity

Sorry this is MS SQL Server and not MySQL but I imagine some of the principals are the same or at least very similar.

share|improve this answer
add comment

Databases allow you to enforce constraints, such as:

  • Nullability of columns
  • Data types of columns
  • Column value constraints (range, list of values etc.)
  • Referential integrity

A text file just has text. There aren't any rules in a text file. You can put in anything (or take out anything) that you want. That means you can make mistakes. These mistakes are the corruptions that your books are talking about.

A database has controls that, if you use them correctly, provide a significant amount of protection against recording bad data.

share|improve this answer
add comment

This question seems too broad, but to address the one aspect you're mentioning -- file corruption, with text files you do not have random access to pieces of data within them. Every time you update a single character somewhere in the middle of a text file you essentially have to read the entire file and write a new, updated file. I guess this may increase chances of corrupting it.

However, databases offer you so many other benefits it's hard to decide where to start. As a matter of fact, database files are just as much (or may be just a little bit less) susceptible to corruption as any other files, by the forces outside the database control, such as disk bit rot, system crashes, etc. However, databases are typically instrumented to detect and recover from such corruption.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.