[mythtv-users] PostgreSQL

[Mike]

Danny Brow wrote:
> I was looking for an alternative to mysql for a reason, I've yet to have
> mysql last for longer then a few weeks on any applications that uses it.
> I've used a few different applications that use mysql db, they all have
> the same issue, power outage is the worst thing that can happen to mysql
> and when it does it takes time that I don't have to fix it. Unlike mysql
> PostgreSQL is rock solid, I've had a lot power outages, system
> shutdowns, user error, anything that can take down a server and
> PostgreSQL comes right back up, with no problems. Out of around 200 DB's
> running PostgreSQL over the last few years I've only had to restore
> twice from back up, once for corruption and the other time from hard
> drive failure (system upgrades not included). This is why I want to use
> PostgreSQL. MySQL is only good when a system will never turn off or the
> db is never shutdown. I'm investing into a UPS for my MythTV box for
> just that reason, just hope we don't get a long power outage around
> here.
>
> Dan.
>   

If you have a lock on an ISAM type table and the power drops while its 
doing something in there, you easily might need to recover. Use 
"myisamchk -r *.MYI" under the mythconverg directory and you'll probably 
get them back just fine. You can also use the repair function in mysql 
itself 'if' the table shows up which sometimes it wont. (your probably 
better off with the binary check/repair)

I wouldn't say this is so much of a MySQL problem so much as an ISAM 
problem. Many things have ISAM or run with the same kind of table 
locking resulting in the same sorts of problems. The big reason anybody 
would even do this is because on smaller tables you actually achieve 
faster performance at the cost of reliability not having to lock onto 
each individual record. (bigger tables are a different story) Usually 
its not a problem because *usually* nobody expects the service to kill 
over in the middle of doing something. Not the best for reliability but 
this is part of the reason innodb became the recommended default with 
4.x. (soon to be soliddb now that oracle bought innobase)

Your solution is probably pretty simple actually, convert the tables to 
innodb and you should be able to basically kill the system all day long 
without corrupting the tables. (remember to mysqldump -A first and 
probably run the check/repair before you try to convert) However, you 
still can easily corrupt the individual record just like everything else 
but that nothing like the whole table killing over.

With myth, you might start with the recordedmarkup table since its 
probably your biggest risk during power failure and mythcommflag running 
at the same time...

-Mike