Corrupted Databases!!

[j1nx]

Hi, I read about a lot of database problems lately and offcourse today it happend to me as well.

Just closed HM the normal way, import was still running though but I clicked the OK button to stop it for me. Then i forgot to save my graph so started HM again. To my suprise; everything was empty????? No reports, no sessions, no graphs, nothing.

So I updated to the latest beta version to see if that worked. Started HM, no license found?? Had to go through the whole registering procedure, but I can live with that. Still everything is empty except the session tab. No reports, no graphs what so ever.

The log file looks like this;

The Error: ERROR: 42P01: relation "allinsituations" does not exist

Occurred when running ExecuteNonQuery with this query: Select PlayerHand_ID from allinsituations limit 1

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.02/0.04 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 04' and GT.BigBlind = '$0.02/0.04 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.01/0.02 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 04' and GT.BigBlind = '$0.01/0.02 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.01/0.02 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 03' and GT.BigBlind = '$0.01/0.02 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.02/0.04 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 03' and GT.BigBlind = '$0.02/0.04 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.01/0.02 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 03' and GT.BigBlind = '$0.01/0.02 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.02/0.04 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 03' and GT.BigBlind = '$0.02/0.04 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 22P02: invalid input syntax for integer: "$0.02/0.04 NL"

Occurred when running GetDataTable with this query: select PH.PokerHand_ID,PH.PreFlopPlayerActionType_ID,PH.S treetWentAllin,PH.HandTimeStamp,GT.GameTypeDescrip tion,PH.holecard1int,PH.holecard2int,PKH.FlopCard1 int,PKH.flopcard2int,PKH.flopcard3int,PKH.TurnCard int,PKH.RiverCardint,PH.flopplayerActionType_ID,PH .turnplayerActionType_ID,PH.riverplayerActionType_ ID,PH.NetAmountWon,GT.BigBlind,PH.PositionType_ID, PH.preflopaction_id,PH.didpfr,PH.didvpip,EV.Sklans kyBucks,EV.EquityPct from playerhandscashkeycolumns ph join pokerhands pkh on ph.pokerhand_id = pkh.pokerhand_id left join allinsituations ev on ph.playerhand_id = ev.playerhand_id join players pl on (pl.player_id = ph.player_id) join gametypes gt on gt.gametype_id = ph.gametype_id where (ph.player_id = 4) and (ph.gametype_id = 1 or ph.gametype_id = 11 or ph.gametype_id = 12 or ph.gametype_id = 13) and ph.numberofplayers <'7' and GT.GameTypeDescription = '2008 04' and GT.BigBlind = '$0.02/0.04 NL' order by PH.HandTimeStamp desc limit 100

The Error: ERROR: 42P01: table "temppurge" does not exist

Occurred when running ExecuteNonQuery with this query: DROP TABLE temppurge

The Error: ERROR: 42703: column "icon_id" does not exist

Occurred when running ExecuteNonQuery with this query: Select icon_id from hemplayernotes limit 1

This is annoying and I think nobody can deny the increased corrupted databases lately. I really think something is seriously wrong in the last couple of versions. I'm a way above avarage coputer user, so I know my way around. This happened without doing anything wrong what so ever.

EDIT: Oh and "$0.02/0.04 NL" is NOT an integer! So I seriously hope this integer thing isn't talking about the string "$0.02/0.04 NL"

[j1nx]

To give my own post some extra "power", i Searched the forum for the word "corrupt" and this are some links from only the first page.

http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt
http://208.109.95.123/forum/showthre...hlight=corrupt


They all have corrupted databases. Now i'm not a database expert but I know my way around in SQL as a former web programmer. SQL servers are developed to prevent all this kind of things and in the beginning of HeM nobody had problems.

Now lately more and more people have to start over with new databases and reimport there HH into it. OK, nothing got lost as most people still got there HH files and sometimes it is even possible to still use the export funtion from HeM.

It is just my observation that it can not be any coincidence anymore that more and more people start to have problems with corrupted databases and I start to believe that the whole corruption thing is caused by HeM itself.

As the program get bigger and bigger, this kind of things can happen, but I think we are getting to the point that it need some extra attention, otherwise it may be getting out of hand and before you know it they call HeM "PT3 black edition"

[Allright]

Same problem here, I cannot find any stats from last month anymore. Neither can I make a graph.

However when I make a graph of all my hands the results from last month can be seen in it, which gives me hope not everything is lost?

[j1nx]

Same problem here, I cannot find any stats from last month anymore. Neither can I make a graph.

However when I make a graph of all my hands the results from last month can be seen in it, which gives me hope not everything is lost?

hmm, now you mention it. Indeed all my filters were set to "this month". When i get back at home I will also check the screens with a longer period filter.

[Rvg72]

If you guys want to let me login with teamviewer I can do this and see what I can find. A lot of the "DB Corruptions" are not real corruptions. It is just a case where data gets out of synch for various reasons. I try to auto convert DB's each time I add new functionality and for most people it works. The ones that it doesn't I try to find out what happened and fix it if possible. Most of these are avoidable which is why I really try to get to the bottom of them when possible.

The cases where it shows actual corruption errors and where PG Admin would show the same corruption errors, well those situations are impossible to program against since it is the underlying file system that is doing the damage. You are right that there are way more of these lately but our player base is about 5x bigger than it was 4 or 5 months ago so you'd expect about 5 times as many of these types of posts.

I am going to add the ability to easily backup your DB to HM and of course address the cases from the first section of this post.

Thanks,

Roy

[j1nx]

Roy,

I know HeM is getting a lot more users lately so problems occur more often because of that. That is a good thing as you will get more and more feedback to squeeze that nasty bugs.

I understand that a this can mean that more people will get corrupted databases, but I believe the reason you choosed postgresql as database framework is not only because of multiple client possibilities, isn't it.

SQL databases are very stable and heavily developed for stability. So it is strange that HeM databases become corrupt after a while, and I really don't believe that every one is unplugging there computer in the middle of something to shut it off.

Don't get me wrong, I'm not complaining, it is a part of the developing proccess. But I know for sure that if this kind of thing happen more and more often people will get fed up with it. Functionaity which isn't working because of bugs is one thing, but when you loose data or even loosing everything because of this kind of things people will leave or worst call it PT3 black edition.

Anyway, back to my own problem.

I was trying to get some more info for you, error messages, log messages or anything that could help you finding the cause of this things. So when i opened HeM nothing showed up again, but as all the filters were still on "this month" and of course we were in a new month i changed it to "last month" and to my suprise everything was there!

So trying to figure out what happened I could only come up with the fact that it was the last day of the month and the last part of the day. I know for sure it was still before midnight

So it problably has something to do with timezones and stuff, but haven't found the cause yet. I remember there is some sort of "my day starts at" setting somewhere, so for today; Will look at that, see if that is maybe wrong or could have something to do with it.

I will report back, as more people had the same problem as I.