I have built an application as part of my UG thesis that uses mysql database (community edition). Now, my professor wants me to provide fault-tolerance to the database (by parity!!)! I argued that we wouldn't need that. Anyway I searched, and found here that mysql has built-in replication engine (a mechanism to provide fault-tolerance, right?), along with many other techniques to provide reliability. But what I learned from there is that there is a master server and some slave servers to provide the fault tolerance using replication. Now my questions are:

  • What if I have only one database server? Does mysql have any fault-tolerance for single standalone database servers (i.e. no master-slave formation, no cluster, etc.)?

  • Do I need to try to provide any kind of fault-tolerance whatsoever for the data stored in mysql database?

  • What kind of differences are there (in terms of fault-tolerance only) between community edition and enterprise edition of mysql servers?

Somehow I get the feeling that we don't need to do anything for providing fault-tolerance to mysql db, and it is fine by itself. But I need some solid info on the matter.

Bounty edit:

The second question from above again:

  • Do I need to try and provide any kind of fault-tolerance whatsoever for the data stored in mysql database?

  • What is the sensibleness of trying to provide fault tolerance to a mysql database by using parity (bit per byte)?

A little details about the data in the database:

The data to be specific is a collection of about 6500 Unicode strings, less than 1 MB in size, is initialization data, that will never change over time. The only transaction will be to read the data from the database, no update no delete. My application requires full-text search on those strings, and this is the only reason I am using mysql since it provides full-text search. I am aware that I could avoid mysql's FT search by using something like elasticsearch instead.

  • Please note that I know that it is super lame to try to provide so called fault-tolerance to mysql database using simple parity! I need answers so that I can show these as documents to my professor and tell him that we don't need to waste time in such a nonsense. I believe I will get help. – Sнаđошƒаӽ Apr 10 '16 at 8:32

The DBMS can be left to deal with fault detection and recovery (when configured correctly). It would be most unusual to manually implement such behaviour in the application. Indeed, that is what a software stack is for - to remove from applications those features which are common and frequently required, much as an OS looks after memory management and thread scheduling, say.

That said, you could add another column to each table containing the character strings. It will hold a hash of the string. Retrieve the hash along with the string, recalculate and throw an error if the two hashes differ.

Given this is a university thesis the professor may be trying to convey a learning point, aside from any practicality about implementation. Your long-term benefit may be to investigate possible implementations of his suggestion, rather than listing reasons that he's an idiot. Just sayin'.

  • Accepted because of the concise answer, especially the first paragraph, which is exactly what I like to think. – Sнаđошƒаӽ Apr 16 '16 at 9:27
  • What is likely to break?
  • How risk-adverse are you?
  • Do you need to repair the failure, or just discover it?

A "parity" check can only discover failure, hence it is not fault-tolerant.

Many people decide that the main failure point is the disk subsystem, so they use RAID. In that context, you can assume that parity is can be used to repair -- but only because there is some other mechanism to say "this drive failed".

But what if the motherboard dies?

So you use Replication and Master-Slave with the two servers sitting next to each other.

But what if the power to the building goes out? Or a tornado hits the two servers? Or ...

So you put the Slave in another data center.

(I could continue this silly story, but I won't.)

You can put two "instances" of MySQL on the same server, then have one replicate to the other. Then put a lot of spin on how great your solution is. (Never mind that practically any failure will take out both copies.)

Or you could spend a few bucks and rent Amazon space for the other server. Then you can honestly brag about the "fault-tolerance".

After Bounty Edit

Use Engine=InnoDB; this gives you simpler recover from server crash.

After loading the data, take a dump (mysqldump or other) of the static table, and store it elsewhere. This is for "disaster recovery" from floods, meteorites, software glitches, disk failure, etc. Reloading would be manual and take some time (but you have not put limits on that).

Those are simple measures, and effectively cover virtually all disasters. If I were to list other things that can go wrong with a mysql setup, "parity" does not show up as a part of any solution.

To finish the task you have, set up your disk with RAID-5. Three drives is the minimum. You could probably fake it with software raid and partitions of a single drive. However, this would make useless for recovering from any kind of failure; instead it would show the use of "parity".

"Checksums" are more often used for catching (but not correcting) errors. This is typically 4-8 bytes overhead for 512-16KB bytes of data. That is not, technically, "parity", but it is more efficient.

One parity bit per byte gives you error detection, but not error correction. See SECDED for correction. That needs, for example, 8 bits on a 64-bit 'word'. Seymour Cray said "parity is for farmers", but eventually he implemented SECDED in 'core' memories. (This was in the '70s. Does your prof date back that far?)

  • Thank you very much for your answer. Please take a look at the edit to the question. I have also added a bounty! – Sнаđошƒаӽ Apr 10 '16 at 8:37

There are several things here. If you want to assure data integrity, you can use an engine with such a thing built-in. MySQL's defaul engine, InnoDB, calculates a checksum of every page (usually, 16K of data) and will automatically shutdown itself if the checksum fails (in most cases, due to a hardware issue; but it could potentially be a bug itself, or someone manually tampering with the files).

Also, in the event of a crash, InnoDB uses a transaction log to recover lost transactions that may not be fully synced to disk due to memory buffering.

The checksums guarantee the physical consistency of the data, but they do not allow to recover from them; and they do not prevent other issues like logical incorrectness or accidental deletion.

A replication setup allows for service redundancy (and even geographically redundant), and certain data redundancy against data loss due to hardware. However, as replication is in almost-real time, it does not protect against things like accidental user deletion. Also, while replication has, in the latest versions, checksumming of the data on the wire, that is very rarely an issue. However, there is a relatively common issue with a mysql slave- replication errors can arise due to unsafe/non-deterministic queries (those that could return different results on two different servers), different configuration or potential risky operations such as improper filtering. There are third party tools like pt-table-checksum that allows to compare two replicas while data is being written (and also provide user-level checksums).

While something like a delayed slave may be useful to avoid those issues, the most common way to assure data survivability is performing regular backups. In particular, full backups plus binary logs allow point in time recovery, and you can add checksums to verify data is not corrupted while stored. Logical backups are slower than row backups for large datasets, but safer if physical integrity is something one wants to avoid.

Answering directly your questions:

  • With InnnoDB checksums and its transaction log, it is very difficult to have data corruption. Data redundancy can also be achieved at filesystem level by using several disks in a RAID configuration (allowing for extra performance and/or data safety by mirroring or maintaining data parities)
  • Use backups to provide data redundancy, replication to provide service redundancy
  • No difference at all in replication features between community and enterprise, except the paid support and some utilities that may be helpful
  • Please take a look at the edit to the question. I have added a bounty! Try and hunt that bounty. :-D – Sнаđошƒаӽ Apr 10 '16 at 8:38
  • Your questions are a bit vague. If you want a more in-depth answer (Do I need to try and provide any kind of fault-tolerance whatsoever for the data stored in mysql database?), you need to define what faults you expect to have. Is it cosmic rays changing the contents of your memory? Disk failures? Filesystem corruption? Signed data to prevent data taints? You need to define a problem to get a proper solution. Theoretical answers are not useful in engineering (or cannot be answered with as much precision as you may want). – jynus Apr 13 '16 at 18:26
  • 1
    For 6K blobs, you do not need anything complex, you can store the hash of those values and store it next to the values (so you can check it at any time). That is a common procedure in software engineering, but that has nothing to do with databases themselves. – jynus Apr 13 '16 at 18:28
  • Thanks for your concern. I intended to mean them all with the word "whatsoever". From your answer and from others' on this question, (and also from quora) what I gathered is something like what you said in the last comment- hash/checksum. Also I gathered, software means to try to provide reliability to mysql is non-sensical, as mysql has enough, and only (not completely strictly though) scope to try to provide reliability to mysql is with hardware redundancy. I stated the original reason why I wanted answers in the first place, in a comment on the question. – Sнаđошƒаӽ Apr 13 '16 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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