PDA

View Full Version : Database Errors



cbi
10-15-2010, 11:33 AM
I have used HM for 400k Hands and never had a problem until my database seemed to go corrupt for no reason.

First the "Hands" part stopped working (not showing any hands at all no matter what settings).

Then I got Database errors like "Failure in Relation xxxx/base blah" (roughly translated) and "couldnt read status of transaction xxx" when i did choose "reports".

The "Sessions" part kept working. The Graphs Part was bringing up a nullpointer exception. Then i discovered that that the database errors always showed up on three specific days and that everything works when i exclude these days in a filter.

I tried everything to either repair the old database (delete the non working days, zero out non working pages, clear bogus transactions, vacuum+reindex, etc..) or export my data into a new one (export to disc from menu, copy hh's to clipboard, "export hands" from db dialog, backup&restore of postgres, creating a new db and trying to move the data into that one with dblink, installing a new server, different postgres versions, etc.) but nothing worked for all kinds of reasons.

So i gave up and started a new database. Bought a gazillion hands to have somewhat accurate data. Everything worked for 2 days and now im stuck with the same kind of database errors. This is a brand new DB created via HM, so the problem has to be on your side?

morny
10-16-2010, 12:06 AM
The 2 most common causes of this problems are 1) powering off your computer or having your computer lose power while the database is doing something and 2) an old drive with bad sectors on it. Basically what happens is a small portion of a file gets slightly corrupt and then when you reboot and windows goes into that checkdsk procedure it finds the bad section and, thinking it is doing a good thing, removes it. Postgres then loads the table and a portion of it is gone so it doesn’t like that and blocks access to the entire table. Normally when this happens it is on a completely useless file that you'll never even notice is now gone and quite often it will happen to an index in the DB which can easily be rebuilt but in your case it happened to one or more of the tables.

So, to protect against this

1) keep you hands histories in case you do need to reimport
2) use a power bar and avoid hard shutdowns (when you flick the power switch on the pc)
3) if your drive is old consider replacing it with a new one. They are cheap and much faster now than even a few years ago.

FWIW ive been using HM for probably 2 years, as part of internal testing we try out internal betas which would have more bugs than the versions we release, i regularly import hands people send that they cant get to import, and i play poker regularly using HM and ive only gotten 1 corruption when my PC lost power during an import about 2 years ago so it is extremely stable and if your having issues its most likely something on your PC

cbi
10-16-2010, 07:32 AM
I dont remember when i had an irregular shutdown the last time and i definately didnt have one in the last 2 days.

The database runs on a SSD Raid. The Discs are about one year old and never contained anything but the HM database.

So unfortunately i dont see how i can fix this or protect against it in the future.

netsrak
10-16-2010, 08:40 AM
Every database requires regular backups.

And as far as i know some types of SSDs are not the best solution for managing databases.

cbi
10-16-2010, 10:23 AM
so you say im at fault for not backing up my database after not even 2 days of use? I cannot expect the database to hold for two days? Are you kidding me? And why and what types of SSDs are not useable for databases? What are you sources for that? Wikipedia for example states that this is one of the main uses for these.

And if you had read my post you would have seen that i have in fact made backups. So before faulting me try to learn how to read first. Thank you.

Patvs
10-16-2010, 06:25 PM
Which SQL version are you using?
Which operation system? 32 or 64 bit?

I'd suggest to:
-1 update SQL to 9.0.1 (64 bit if your OS is 64 bit)
-2 turn off LOGGING in postgresql.conf
-3 assign more memory to SQL (in postgresql.conf)

VACUUM the database every 500.000 hands you import.
(and backup the database every 2 million hands you've imported)


read:
http://forums.holdemmanager.com/manager-general/11194-top-10-hem-performance-increase-tips.html

cbi
10-17-2010, 10:16 PM
I run Windows 7 64-bit with Postgres 8.4

I did what you suggested and it works with a new DB since 1 day so far.

Pulec
10-18-2010, 02:43 AM
The 2 most common causes of this problems are 1) powering off your computer or having your computer lose power while the database is doing something
Basically what happens is a small portion of a file gets slightly corrupt and then when you reboot and windows goes into that checkdsk procedure it finds the bad section and, thinking it is doing a good thing, removes it. Postgres then loads the table and a portion of it is gone so it doesn’t like that and blocks access to the entire table. Normally when this happens it is on a completely useless file that you'll never even notice is now gone and quite often it will happen to an index in the DB which can easily be rebuilt but in your case it happened to one or more of the tables.


That's what exactly happened to me. I rebuilt all DB index but i still have the same problem: Hem doesn't read the hands stored on DB (even if i select "all hands") and doesn't import new hands.
What should i do?

Patvs
10-18-2010, 06:13 PM
Create a NEW empty database--> reimport your hands

cbi
10-20-2010, 03:35 PM
ok database errors are back again with a new DB on Postgres 9

loustic
10-20-2010, 03:50 PM
I know you've got a lot, but do you still have all hand histories ?

You could try to create a new db and import all your hand histories from the beginning (not the exports created by HEM)

cbi
10-20-2010, 04:03 PM
no unfortunately i dont have all my original hh's. I didnt archive them over years of play.

But i already tried a new DB 2 times and have only imported some bought hands and like 2 newly played sessions of mine and the errors kept coming back.

morny
10-21-2010, 12:42 AM
The 2 most common causes of this problems are 1) powering off your computer or having your computer lose power while the database is doing something and 2) an old drive with bad sectors on it. Basically what happens is a small portion of a file gets slightly corrupt and then when you reboot and windows goes into that checkdsk procedure it finds the bad section and, thinking it is doing a good thing, removes it. Postgres then loads the table and a portion of it is gone so it doesn’t like that and blocks access to the entire table. Normally when this happens it is on a completely useless file that you'll never even notice is now gone and quite often it will happen to an index in the DB which can easily be rebuilt but in your case it happened to one or more of the tables.

So, to protect against this

1) keep you hands histories in case you do need to reimport
2) use a power bar and avoid hard shutdowns (when you flick the power switch on the pc)
3) if your drive is old consider replacing it with a new one. They are cheap and much faster now than even a few years ago.

FWIW ive been using HM for probably 2 years, as part of internal testing we try out internal betas which would have more bugs than the versions we release, i regularly import hands people send that they cant get to import and ive only gotten 1 corruption when my PC lost power during an import so it is extremely stable so if your having issues its most likely something on your PC

loustic
10-21-2010, 07:19 AM
I think this is weird that this happens again on a fresh new database.

I can see that with your new databases, you both reimport hands + import hands you were currently playing.

You should try one last thing:

Create new database and don't import any old hand history, only fresh ones to see if that happens again.

Also, you should definitely make a check disk of all the hard drives used by HEM & PostgreSQL, and use a software like CrystalDiskInfo (freeware that you can install or use portable version without install, google it) to check the status of your drives.

cbi
10-21-2010, 11:01 PM
hey morny and netsrak,

thank you for completely ignoring what i write and even pasting the same random non-applicable text twice. That really helps me alot. In case i have forgotten what you have written the first time, i now know it again.

Besides the fact that almost nothing works due to random database errors and nullpointer exceptions i now have a new problem: When I start the auto-import a lot of old duplicate hands from the import dirs are read and imported which means i have to wait 2 hours before the hud works.

It seems like the only solution to this whole mess is PT3.

@loustic Thanks for your suggestion, i will check the discs, but i really dont want to import the next fresh 200k hands and then have the same problem again.

loustic
10-22-2010, 01:58 AM
One key question I forgot to ask you Cbi:

Did these 400k hands you played were done on your actual configuration (Windows 7 64 bits), or did you upgrade anything since the first time you play your 1st hand ?

Regarding the new error since you upgraded to PostgreSQL 9, is it still the same error ? (could not read status of transaction...)

If this error is still the same, it's more likely a database problem than a HEM problem.

Regarding the check of your discs with the utility, you should really do it right now !!!
It will take you 5 seconds. It doesn't even requires you to install the product as there's a portable release.
You will know right now if any of your drive have SMART problems.

You definitely have some great problems with your configuration, if when you launch auto-import, it imports a lot of hands.

You should check every auto-import folder you've set-up to see if there are hand histories, and if yes, a lot ?

As Patvs suggested, did you turn-off autologging ? (in the postgresql.conf file in your data folder, replace logging_collector = on by logging_collector = off).

Also, check in the \data\pg_log directory, because I have the feeling you must have plenty of log files, and you could delete them (zip them first then delete them if you want to keep them, but you probably won't need them).

I think you should consider to uninstall everything, both PostgreSQL + Holdem Manager, and reinstall everything.

I've worked for one of the biggest software editor as a consultant, and I installed softwares and database engines 5 days a week in my whole country, and made maintenance on databases.

What I can tell you from my experience, is that maybe you jumped on the 64 bit version of Windows 7 a bit too fast.

There's no hurry to go to that 64 bit version, because most editors make the majority of their tests on regular configurations, which are 32 bits ones.

And even if the softwares are 64 bits compatible, they're not 64 bits optimized, so you lose the rare benefits 64 bits release of Windows could bring you, and keep all the problems it'll bring you.

Most of the time when you do switch to 64 bit version, you actually work as a beta tester.

And most of the time, beta testers encounter bugs ;)

For my home PC, I'm still on XP SP3 and it works like a charm.

It's true I will probably switch to Windows 7 one of these days, but when I'll do so, I'll install 32 bits version.

Just to let you know, Windows 7 licences allow you to install both 32 and 64 bits version, so you can easily install Windows 7 32 bits version, and switch to 64 bits later when you know most of editors have really worked on the 64 bits version.

So, if I were you, I would do:

- Check status of my drives, both with a SMART utility drive like the one I mentioned, but there are many other freewares like that.

- Launch a chkdsk on all my drives, which will require a reboot, but you must 110% know if you have drive problems or not.
Because if you do have drives problems, you're simply losing time for a problem whose only solution is named "new drive".

- If problem with drive, you know what to do, if no problem around:

- Uninstall & reinstall both PostgreSQL (after PostgreSQL uninstall, launch a .Cmd prompt and type "net user postgres /delete) & HEM on your current Windows 7 configuration and create new database. Don't restore neither your database, neither HEM config / reports or anything by the backup/restore utility. Just start from a fresh config. For your huds, before uninstall, export them as .xml file in a secure place (not the default place as it'll be deleted during uninstall). And after reinstall, you reimport all your huds.

- If problems still occur, I know this is sad and you probably won't like it, but format your computer, and install Windows 7 32 bits, then install all your stuff again. Your Windows 7 licence key work for 32 and 64 bit version so no problem.

People mostly don't like to format their computer, and people are wrong.

Because most of the times, when big problems begin, they spend an incredible time to try to fix them, and only format when they have spent too much time for nothing and see they're still stucked.

And finally when they reformat Windows and reinstall it, they find that the boot time is incredibly fast, which means their old config was dirty.

And they find that the problems they had also disappeared :)

And last but not least... NEVER ever delete definitely your hands history.
Each month, make a .rar or .zip archive with all your month HH. It'll reduce their size by 10. And then you save that on dvd or USB key or whatever...

And I also forgot:
Personnaly, I make backups of my database every day, and keep several days backups in case my last backup is dirty.

As soon as you encounter a problem like the one you're talking about, you stop auto-import right now, and try to figure out what's going on:
- Did your PC crashed during auto-import ?
- Did HEM crashed during auto-import, or during simple use ?
- Did you upgrade anything recently (HEM, PostgreSQL, or anything else) ?
- If you can't figure out, come right now on the forum and ask for help.

By stopping import as soon as problem occurs, and by doing backups every day, you can restore your database + HEM config of the day before your problems started, and see if the database is now fully working.

If the restored database is working fine, don't autoimport right now, but make manual import of the hand histories missing in your database since the last backup, and see if everything is fine.

sariboxer
11-07-2010, 11:17 AM
cbi,

I have a good news for you, I have the exact same problem. (new configuration with Win7, 64 bit)

I tried everything that you did (new postgresql, including 9.0, new HM, even new windows, importing my old hands as many ways as it is possible). and always after a maximum of 2 days my database gets corrupt.

did you figure out what is that and how the problem can be solved?

netsrak
11-08-2010, 04:22 AM
Must be something with your computer hardware or operating system installation.

We can try to identify the problem with a remote session: Please schedule a remote support session with our support. Email support@holdemmanager.net, with a link to this thread and your forum name, so we can setup a Teamviewer (http://www.holdemmanager.net/teamviewer) session.

sariboxer
11-08-2010, 04:40 AM
Must be something with your computer hardware or operating system installation.

We can try to identify the problem with a remote session: Please schedule a remote support session with our support. Email support@holdemmanager.net, with a link to this thread and your forum name, so we can setup a Teamviewer (http://www.holdemmanager.net/teamviewer) session.


hello netsrak, I am already in e-mail conncetion with the given e-mail (morny replied to my letters) and I tried almost everything that I think can be the cause of the problem. I would be very grateful if you could try to help me to detect the problem via teamviewer/skype/whatever.

now i have a few things to do, but from 2 hours from now I'll be available all day long and I live in the same time zone as you do (if u live in Germany)

netsrak
11-08-2010, 08:04 AM
Lets wait for Morny, he has the details and the email conversation. You can reply to his mail to arrange a TV session.

sariboxer
11-08-2010, 08:27 AM
Lets wait for Morny, he has the details and the email conversation. You can reply to his mail to arrange a TV session.


he actually said a TV session is useless, because the problem always occurs after the postgre setup

netsrak
11-09-2010, 04:39 AM
Ok, then you should think about a new installation of the operating system.

sariboxer
11-16-2010, 04:13 AM
ok, for anyone else suffering from the same problem:

it seems (99,99% now) that the whole issue was due to a hardware fault of my ssd drive (corsair, blablabla)

now the interesting part: this drive was the first one I tested (with different softwares), but it received 100% on any test, so by now I was unable to diagnose its exact fault, but since I reinstalled postgre on my other (traditional) drive everything became absolutely ok.