Compare difference between PT3 database with PT4 database.

[Dutchraise72]

I do have a lot of differences in my tournament results between my PT3 and PT4 database. The rebuys and addon are way off. So i created a access database to compare the 2 database to find errors that i did make by filling in tournament results and backing deals that i calculated in my tournament results. Also did find a few bug with this manner. Its very useful and easy to use.

ComparePT3toPT4.jpg

ComparePT3toPT4 access database

(180.47 KiB) Downloaded 4423 times

If some interested PM me :D

[polk3]

I'm interested in this same thing as I've also seen some fairly big differences between PT3 and PT4 databases. Sometimes due to bugs, but could also very well be due to corrupted data in the PT3 db, and that's what I'm trying to figure out (and correct). While switching over from PT3 to PT4 it also seems like a good time to do a consistency check on the data. Need some automated way to do this as the db is approaching 1M hands in total. Also needs to do this for both Hold'em and Omaha, and for both cash and tourney.

I've been playing around with Python a bit to do this. Doesn't seem to be so difficult really, though there's possibly a fair bit of work involved.

How far have you come? And has anyone else done any work relating to this? I've mainly been using Python and Excel, but I think I also have an old version of Access available somewhere.

[Dutchraise72]

Need to say i have my currency in pt4 in $ and played only tournaments in $. So it does not make a difference for the queries in my access 2007 database.

ComparePt3withPt4.zip

You need a D: Partition on order to extract this file on the right place.

(31.67 KiB) Downloaded 657 times

This access database compares the difference in result by tournament rows. So if you run a query from the access database you get rows that are not the same, This way you have a quick report with tournament id to see where you maybe forget to fill in a some rebuys or add-ons in PT3.

I did fond tournaments in PT4 where the rebuys and addons where correct but the rebuy and addon amount was set to 0. So that affect the netto result. I also found some tournaments where the addon is not counted in PT4 or a wrong count in Rebuys. I found tournaments where the won amount in pt3 was manually set higher and in pt4 its was set to the won amount in the handhistory. I did created a support ticket for these tournaments and expect to need to create more tickets ;)

I am still analyzing and for now i did correct a lot off tournaments in my pt3. As example in the past it was not important if you wrote 3 rebuy and an addon or 4 rebuys as total for the netto won amount. In PT4 its is because if you do an auto detect on your tournaments it will write a addon if its detects it.

I expect a few bugs in PT4 so i wait with the final export and in the meantime i correct the mistakes i made in PT3 compared with the result of PT4.

Access database attached with a read me file.

Edit:
You need US valuta settings in your windows country setting. So a , for 1,000 and a . for 0.80

[polk3]

Ok, thanks. Turns out I only had Access 2003 available so I can't open your db.

[Dutchraise72]

Added the MS access 2003 version

[polk3]

Hey,

Forgot about this thread for a while, but still trying to get my database to convert properly. I've come to a point where it makes little sense to continue with the actual conversion until the import errors are fixed.

I did look at your database, thanks for posting. It also turns out to be a bit too much trouble to get it to work (my D: drive is allocated for one thing!). From what I can tell though, we're trying to achieve similar things.

What I've done instead is use direct SQL queries to the PT3/PT4 databases, and tracking the results manually in Excel. It's a poor man's database if you will, with manual updates. But it works. I've been able to track down, identify and understand the differences. Initially it was just a blur of millions of rows where some differed for unknown reasons. Now I'm fairly clear about two things: 1) the extent of the difference, and 2) where it differs and why.

Using that method I discovered that there were quite a few errors in the PT3 database. Quite a few OnGame sessions were split in two, most of the iPoker sessions had wrong start and end times, many session summaries had been miscalculated, a lot of the limits were different, a foreign currency that I'd never touched was somehow set on a number of sessions, and some other smaller errors that I don't remember now. Quite a bit, but none of these errors really affected display and stats in PT3 all that much so they went undetected for a long time. They are likely due to past bugs that have since been corrected (but the broken data remained in the database) because when importing to a new PT3 database with the latest version the errors are not there. Anyway, with some light database magic I was able to clean up all of that in the source db.

This didn't actually achieve much in terms of converting the database, except that 1) I learned that PT4 gets it mostly right (95-98% overall), and 2) comparing the databases is much more manageable.

That in turn helped me pinpoint two areas (root causes) that seem to have the most impact on the differences:

1. Import errors in PT4 for hands that import fine into PT3.

2. Silent errors that are not reported as errors but where hand data is miscalculated by PT4 (but usually not by PT3)

From a db of ~850k hands I had on the order of 150 import errors. Silent errors are more difficult to quantify exactly, but there are thousands. In terms of results, these errors have impacts between 0% and 2% on the total so from that perspective it's not so bad, but with one site being off a full 10%. This is all for cash games btw, tourneys have differences too but I'm kinda waiting for the import bugs to be fixed before digging into that. And also, I've tried to compare as many of the database fields as reasonably makes sense. Due to the different database structure, some fields make little sense to compare so I've mostly ignored those. Anyway, the bulk of the differences I've found can be traced back to individual hands where something went wrong with the import.

In the end the database queries started to become a bit too extensive to keep track of manually so I've started putting them into a small app instead. Similar to your Access app I guess, but it operates on the PostgreSQL database directly instead of going via Access.

Right now I'm basically waiting for these import errors to be fixed before attempting another db conversion. If I went with my currently converted db I'd still have ~500 sessions with errors that I'd have to find, purge, find the hh file for, and then reimport -- a bit of a nightmare in itself. So I'm keeping the PT3 db as the master and importing hh's to PT4 in parallel.

Tourneys I haven't actually dug into much at all yet. There are way too many differences there for the manual process to work. I'm kinda hoping that fixing the import errors will improve the tourney conversion too, making it easier to compare the data.

And all of this to answer the question, did my data make it to the new version? I guess I really do have too much time on my hands :ugeek:

[kraada]

Do you have any support tickets open with examples of the hand histories that are not converting properly?

[polk3]

Do you have any support tickets open with examples of the hand histories that are not converting properly?

Yes, in #124262. I may have overloaded poor Mike with unstructured data, but it should all be there.

[kraada]

Thanks, I'll do what I can to get a fix expedited for you.

[polk3]

Thank you kind sir.