Are there any tips to keeping a MySQL database intact after a power loss, other than the obvious (a big UPS)?
I’ve had two separate events where the server PC lost power, and Travis helped me get MySQL database tables going again the first time. Travis helped me convert the database tables to MyISAM (more resilient) as part of this repair.
The second time I had to “repair” the MySQL program installation and make a new connection to get going again. Luckily, the data tables appeared to be unharmed the second time, apparently because of the MyISAM conversion. Thanks Travis!
There appears to be two separate but related power fail issues: Database integrity, and Database program integrity.
Back in the days of OS/2, (I’m showing my maturity here), I used IBM DB/2 and weathered many power failures without incident.
Is Postgres or other free databases more resilient at handling power failures? Do you have to use “heavy iron” databases to get some power fail protection?
Your experiences and ideas would be greatly appreciated!
Even a small ups that gives your server enough juice to shutdown can mitigate this in the short term. A little 1500VA one(250$ish) is more than enough.
Actually, InnoDB is normally considered more “resilient” than MyISAM. MyISAM gets corrupted easily with power failures, but it’s relatively easy to repair. In fact, there’s a startup parameter that will tell mysql to repair it automatically (which seems kind of strange, but seems to work out more or less- this is probably what Travis set up for you). InnoDB, due to the fact that it’s a transaction engine, which involves various locking and check pointing mechanisms, rarely seems to get corrupted. Not to say I haven’t seen it, and when it does it can be much harder to repair, but seems to be rarer.
Certainly a UPS could help significantly. Also, depending on the size of your project, you may be able to use SQLServer Express for free- I’ve never really heard of much of a problem with SQLServer in this regard.
One last thing though - you said the actual application got corrupted some how? If this is true, there’s something else going on, and you’re not going to be able to escape it by changing engines/dbs. Out of curiosity, does this machine have RAID’ed hard drives?
A real server with raid is something I’ve been trying to convince the client to purchase, but budget limitations have put that option on hold. I do have another IA site with that configuration, but it DOES have UPS power, so I don’t know to what degree raid is helping.
I agree, UPS is the simplest solution, but in some installations I’ve seen, client personnel will find a way to mess it up, like plugging a floor buffer into the UPS!
Short-term I will be making sure a UPS is installed for this PC, but it still begs the question of which DB is the most tolerant of power fails. I’m tempted to look at DB2 Express-C because of my previous success with DB/2.
Well, I was actually asking about RAID because it can also lead to more problems
In fact, I think one of the only times I’ve seen an innodb table get corrupted was due to the raid controller not handling certain things well or something strange like that. That’s why I asked… (I should say that the benefits of raid far outweigh the random hardware problems that come up from time to time).
You can certainly look at DB2. I’m not sure how the express restrictions on that compare to the express version of SQL Server. If they’re similar, though, I’d go with SQL Server (only on the basis of personal experience & install base with our software).
SQL Server 2008 Express looks like a good choice: IA native driver support, huge MS user community, etc.
Just curious: Do you know the percent breakdown of DB usage for IA users. I saw the poll request in the forum, but no results. Does the typical IA user just default to MySQL?
Yeah, most new users default to MySQL because we include it in the installer and they either think it’s the better choice or don’t really pay attention.
In terms of the break down amongst users who actually care what the database is, I’d say it’s probably split between mysql and sql server, or actually a bit more on the sql server side (a large percentage of IT departments seem to have MS SQL licenses for other purposes, so if the project is originating in IT or they’re heavily involved, the usually just use that).
Then we have a small percentage of users using Oracle, DB2, and Postgres. Oracle doesn’t have nearly the presence in manufacturing that you might imagine- the cases I can think of are mostly government systems.
And then there are the wild cards, like a few people logging data to Firebird or EnterpriseDB (a wrapped up postgres), or some other off-the-wall system I can’t think of right now…
I use MySQL, had a few power outages, only corrupted one row once. MyISAM is a very lazy database engine, it doesnt even try to ensure the data gets stored. InnoDB or Falcon would be a better choice. If you want to use InnoDB, try the 5.4 beta, I alpha tested it, and its performance is significantly better then 5.1, although 5.1 is also a good choice.
Also, I have had no issues with 6.0 yet, and the backup system is pretty good.
