Hello,

Has anybody ever seen mysql show corrupt or damaged tables? For me it seems to happen several times each year.

Isn't the purpose of a database to keep your data safe? How can a database get into these corrupted states? Don't they have journaled transactions? and do something like write the changes in a journal, then try to commit them, and if there is a problem, roll back the changes, thus leaving the db in a non corrupted state? I think mysql has 2 file types, one is safe, one is easily corrupted, correct? Is the db config a Knoppmyth choice? or is that a myth choice? Maybe I've led a sheltered life, but I've never seen such an easily corrupted db!

OK, while I am on a rant... what about those linux freezes? I mean the whole box freezes and goes dead! Has anybody ever seen that? Is that a linux design feature, with its monolithic kernal? is that a some bug in kernal space freezing the whole box? Could that be a Nvidia bug? I heard that Nvidia was causing a large percentage of the Vista freezes. Perhaps microkernal unixes don't crash so often.

OK, I feel better now. Any chance of getting a more reslient config of mysql or some other db?

Sounds like you have a hardware problem. Its probably causing the mysql corruptions and the freezing...

Not quite. The primary purpose of a database is to keep data in an organized and easily accessible format. After that you start to make engineering decisions about what other properties are important; speed, simplicity, footprint, cost, flexibility, concurrent access, coherency, reliability, safety, ... and often have to trade off one for another. DBs range from the simplest in memory tables to full on enterprise grade relational (or post relational) servers.

A relation DB trades off some speed for increased flexibility versus a simpler type of DB. A multi-user server trades off speed, simplicity, and footprint for concurrent access and coherency. An enterprise grade DB server trades off speed, simplicity, footprint and cost for increased reliability and safety. A lot of the extra cost and complexity of such a server goes into keeping the speed and footprint reasonable while maximizing the reliability and safety.

On the other hand, even the best commercial DB servers aren't magic bullets. You can't just say "OK, we've got a DB now our data is 100% safe!" This is all too common "cargo cult" thinking, like setting you password to "antidisestablishmentarianism" and thinking it _must_ be secure because it's such a long word.(1) There is huge body of tuning and operational practice involved in make that real. For example running in what Oracle calls "archive log mode" so that transactions don't get lost (and you'd be surprised how many people don't understand why this is important...), regular full backups (if you're more than a few days worth of incremental backups away from the last full one you're in for a rude awakening), ... Well the whole list is the stuff of a multi-day mid-level DBA training course...

Even with all this, reliability is still a matter of "9s" with the costs approximately doubling or increasing by an order magnitude for each additional 9. Going from 99.9% (say an unrecoverable failure every 100 days) to 99.99% (every 1000 days) will at least double your costs in time effort and $. For example I added an additional drive (doubling my storage cost) and semi-regularly make an image backup onto it of known good states (extra effort). The next step would be to go to something like RAID-5 with an external backup system which would again more than double my storage costs.

(1) Using any dictionary word for your password is dumber than snake mittens. Just about every script kiddie on the face of the planet knows how to make a dictionary attack. It's kindergarten stuff for black hats.

How did you know my password?
But really, if you look at the threads here, there are many articles on how to search for corrupted tables, how to fix corrupted tables, how to re-seek recordings, how to re-flag commercials, etc. all because mysql seems to be a bit fragile. I don't know if there is any thing that can be done to make it more secure but it is amazing what people can adapt to and accept as normal. It's good to question, or isn't it?

Of course it is good to question. However you should know what it is you are questioning. You seem to be combining security and MySQL's reliability... There are probably things that can be done to ensure's MySQL's data integrity. Off the top off my head, the new "idle" comes to mind. If MythTV is idle, then stop it and the db then check MySQL for errors. This is something that can be ran from cron on a weekly or daily basis. Anyone up to the challenge?

Nothing wrong with questioning, but you have to have realistic expectations.

Another analogy, there's a comedian who does bit about her father's cholesterol problem, for which he has to take Liptor, despite this he continues to eat a diet which will negate any gains and make the problem worse. When she tries to explain this he replies "I don't have a .cholesterol problem, I take Lipitor!" This is a classical case of unrealistic expectations. The medicine can only help if you let it, and avoid making the situation worse, and even then it can have undesirable side effects (TANSTAFL).

We see a lot of complaints about all kinds of disk problems. They're a statistical fact of life, and given a large enough population or a long enough time, inevitable. This goes double when naive people do things like turn off the power without shutting down, or fail to protect against power line glitches, or any of a dozen other things. Since the DB is one of the most critical and central components it's where a lot of these problems will be most obvious. Glitches in recordings, or bad recordings, or corruption in log files that you never read, or ... are generally much less apparent and likely to be dismissed or forgotten.

These types of issues are also more likely in "always on" systems simply due to increased opportunity. If you figure how many hours a year the system runs versus how many glitches occur you'll find that the reliability is actually amazingly high. The better you take care of the system (avoiding thermal issues, preventing power issues, doing periodic maintenance, educating other people what kinds of things not to do, ...) the more reliable you'll find it.

I also get MySQL tables corrupted on occasion, I usually find that the cause is MythTV filling the root partition with log files. I've been thinking for a while now that the solution is to add a small partition (<500mb) solely for log files.

MythTV also crashes for me on occasion, usually it seems to be caused by the OSD; opening or closing the TV Guide in particular.

Oh, I thought I'd add, I found my system to be a lot more stable after doing a complete re-install, setup, and then manually adding MySQL data. The backups might be bringing conflicting configurations forward, either defaults with older versions, or some modification(s) I'd done manually somewhere along the line.

My system was freezing until I got a better power supply, then I didn't have any more problems. I bought a Seasonic 550w. I had three tv cards, including a dual-tuner, a soundcard, 2gb ram, and two hard drives. It mustn't have been enough power.

And of course, if you're having problems, testing your ram is a good idea.

At one point I was re-installing Windows XP - I know, my bad. It got to a certain point of the Text Mode install, copying a particular file, and died. I figured bad CD, and tried a different one - same result. Bad drive cable - changed with same result. Another hard disk - ditto. Different DVD drive - same result. Tried a CD drive, because after all it was a CD -- nope same.

The same machine had worked fine previously. It booted from several LiveCD Linuxs, and Bart's PE. So in my mind, all must have been right with the hardware.

Searched the net for hours, and finally found a mention of a similar problem caused by bad ram. I ran Mtest86+, and it started spitting errors immediately. RMA's the ram, got new and poof, it worked like a charm.

Don't assume that just because the machine works, that it is in perfect order.

Mysql has the myisam files and the innodb files/engines. Knoppmyth uses the mysql myisam files, which are easily corrupted. The mysql innodb engine/files support ACID: "(Atomicity, Consistency, Isolation, Durability) which is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction."

Mysql is the only supported db for myth; but I think both myisam and innodb both work.

Corruption is bound to occur; say you have a bug in some code (Nvidia?) and the whole system freezes. If mysql was changing something in the db, you probably now have a corrupted db. Of course, you may not know it untill a week later, when everything seems hosed.

It would seem prudent to check the myisam files on boot up, boot down, and quite often, or switch to the innodb engine/files.

I will try to add a check to the boot up procedures, anybody want to point me to a good place to insert it? And any thougts on switching to innodb?

Thanks.