Import problems: deadlocks and whatnot

[pasita]

I imported a bunch of my older hands to a clean db, about 39k errors, 200k imports. I believe I didn't get such a result when I did this earlier. I'm wondering if these are known problems? Should I just try to reimport? The import took maybe 90 minutes, the final phases of that it looked like it was seriously stuck with no estimate on end... then suddenly "import finished".


Clip from the log: seems to have prosessed ok the files from several sites, then this

2012/04/25 22:21:58.201 [00002]: [- Main -][ImportStatus] Adjusting session stakes ...
2012/04/25 22:21:58.974 [00773]: [00007FC8][PostgresConnection (Error)] Unable to execute query: BEGIN;INSERT INTO cash_table_session_summary VALUES(10855, 2, 2300, 2334, 2334, 62, 244, '2011/09/07 14:39:59', '2011/09/07 15:17:45', 38, 4, 1219.30, -85.80, 6.86, 13.69, 10.99, 14.37, 54, 15, 203, 0, 'f', 'f', 1.40360000);INSERT INTO cash_table_session_summary VALUES(10876, 2, 2300, 2334, 2334, 20, 236, '2011/09/08 16:23:46', '2011/09/08 16:28:44', 5, 4, 177.73, 46.50, 3.00, 1.16, 1.52, 1.15, 5, 1, 30, 0, 'f', 'f', 1.00000000);UPDATE cash_table_session_summary SET date_start = '2011/09/08 14:05:08', date_end = '2011/09/08 14:08:01', id_limit = 56, cnt_minutes = 3, amt_won = -32.05, amt_pot = 76.50, amt_rake = 0.30, amt_rake_contributed = 0.80, amt_rake_contributed_weighted = 0.80, amt_mgr = 0.80, cnt_hands = 7, cnt_hands_won = 3, cnt_ttl_players = 14, cnt_ttl_players_flop = 0 WHERE id_session = 5363 AND id_player = 2549;INSERT INTO cash_table_session_summary VALUES(10846, 2, 2300, 2549, 2549, 20, 236,
................ 6 more screenfuls of that......
'2011/09/08 15:00:51', 1, 1, 1.00, -0.50, 0.00, 0.00, 0.00, 0.00, 1, 0, 2, 0, 'f', 'f', 1.40440000);DELETE FROM cash_table_session_summary WHERE id_session = 5394;DELETE FROM cash_table_session_summary WHERE id_session = 5397;DELETE FROM cash_table_session_summary WHERE id_session = 5745;DELETE FROM cash_table_session_summary WHERE id_session = 5746;DELETE FROM cash_table_session_summary WHERE id_session = 5744;DELETE FROM cash_table_session_summary WHERE id_session = 5741;DELETE FROM cash_table_session_summary WHERE id_session = 5827;DELETE FROM cash_table_session_summary WHERE id_session = 5832;DELETE FROM cash_table_session_summary WHERE id_session = 5831;DELETE FROM cash_table_session_summary WHERE id_session = 5866;DELETE FROM cash_table_session_summary WHERE id_session = 6006;DELETE FROM cash_table_session_summary WHERE id_session = 6019;DELETE FROM cash_table_session_summary WHERE id_session = 6017;DELETE FROM cash_table_session_summary WHERE id_session = 6030;DELETE FROM cash_table_session_summary WHERE id_session = 6031;DELETE FROM cash_table_session_summary WHERE id_session = 6025;DELETE FROM cash_table_session_summary WHERE id_session = 6087;DELETE FROM cash_table_session_summary WHERE id_session = 6077;DELETE FROM cash_table_session_summary WHERE id_session = 6080;DELETE FROM cash_table_session_summary WHERE id_session = 6086;DELETE FROM cash_table_session_summary WHERE id_session = 6078;DELETE FROM cash_table_session_summary WHERE id_session = 6088;DELETE FROM cash_table_session_summary WHERE id_session = 6100;DELETE FROM
...................and after a screenful........
cash_table_session_summary WHERE id_session = 10577;DELETE FROM cash_table_session_summary WHERE id_session = 10689;DELETE FROM cash_table_session_summary WHERE id_session = 10688;DELETE FROM cash_table_session_summary WHERE id_session = 10779;DELETE FROM cash_table_session_summary WHERE id_session = 10774;DELETE FROM cash_table_session_summary WHERE id_session = 10777;COMMIT;; Reason: Fatal Error (ERROR: deadlock detected DETAIL: Process 27480 waits for ShareLock on transaction 4488998; blocked by process 25676. Process 25676 waits for ShareLock on transaction 4488997; blocked by process 27480. HINT: See server log for query details. )

a load of these (about 15k lines worth):

2012/04/25 22:22:00.461 [00064]: [00007FC8][PostgresConnection (Error)] Unable to execute query: INSERT INTO player VALUES (3794, 2300, 'a players name here', 'a players name here', 0, 'f', 'f');; Reason: Fatal Error (ERROR: current transaction is aborted, commands ignored until end of transaction block )

Later, trying to look at Results|Overview|Advanced|By Date, I get (for some of my nicks only, some work... also the Basic report works)

2012/04/26 00:14:36.383 [00000]: [00006058][Query::QueryThread] Thread [0x00006058]: Begin
2012/04/26 00:14:36.385 [00001]: [00006058][Query] Executing SQL (Thread): SELECT ((case when( cash_hand_player_statistics.val_curr_conv != 0) then cash_hand_player_statistics.val_curr_conv*cash_hand_player_statistics.amt_won else 0.0 end)) as "amt_won_curr_conv", (cash_hand_summary.id_hand) as "id_hand" FROM cash_hand_player_statistics , cash_hand_summary, cash_limit WHERE (cash_hand_summary.id_hand = cash_hand_player_statistics.id_hand) AND (cash_limit.id_limit = cash_hand_player_statistics.id_limit) AND (cash_limit.id_limit = cash_hand_summary.id_limit) AND (cash_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='mynickhere' AND id_site='2300')) ORDER BY (timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '0 HOURS')) asc
2012/04/26 00:14:36.458 [00073]: [- Main -][ptQueryCtrlBase] ptQueryCtrlBase::doLoad(): Begin
2012/04/26 00:14:36.465 [00007]: [- Main -][PostgresConnection (Error)] Unable to execute query: SELECT typrelid FROM pg_catalog.pg_type WHERE typname='cash_cache';; Reason: Fatal Error (ERROR: current transaction is aborted, commands ignored until end of transaction block )
2012/04/26 00:14:36.487 [00022]: [- Main -][StatManager] Unable to use cache due to column: date_played_year_month (date_played_year_month)

[WhiteRider]

If you still have that log file (it is overwritten when you restart PT4 unless you made a copy) then please attach it to a Support Ticket so that we can see the full error messages.
If not, re-run the import - it may only need the files at the end of the import to reproduce the problem.

Please also attach a few of the hand history files which give import errors so that we can see what is wrong with the hands.
You can compress multiple files to do a single upload.

[pasita]

Ticket sent.

I reimported part of the hands (for the nick that couldn't show R|O|A|D (see above). Got similar stuff in the log. Still the same when trying to view the stats. (The red part showing in the lower window, graph seems ok).
2012/04/26 20:53:13.949 [00000]: [00002724][PostgresConnection (Error)] Unable to execute query: SELECT ((date_part('year', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS')) * 100) + date_part('month', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))) as "date_played_year_month", (date_part('month', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))) as "date_played_month", (date_part('year', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))) as "date_played_year", (sum((case when(cash_hand_player_statistics.id_hand > 0) then 1 else 0 end))) as "cnt_hands", (sum((case when(cash_hand_player_statistics.flg_won_hand) then 1 else 0 end))) as "cnt_hands_won", (sum(CAST( (cash_hand_player_statistics.val_curr_conv * cash_hand_player_statistics.amt_won) AS numeric(11,2) ))) as "amt_won_curr_conv", (sum(CAST( (cash_hand_player_statistics.val_curr_conv * cash_hand_player_statistics.amt_expected_won) as numeric(11,2) ))) as "amt_expected_won_curr_conv", (count( distinct cash_hand_player_statistics.id_session)) as "cnt_sessions", (sum( (SELECT (case when( chps_s.amt_won > 0) then 1 else 0 end) FROM cash_table_session_summary chps_s WHERE chps_s.id_session = cash_hand_player_statistics.id_session AND chps_s.date_start=cash_hand_player_statistics.date_played) )) as "cnt_sessions_won", (sum( (CASE WHEN ( cash_limit.amt_bb) <> 0 THEN ((cash_hand_player_statistics.amt_won * 1.0 )/( cash_limit.amt_bb)) ELSE 0 END) )) as "amt_bb_won", (sum( (CASE WHEN ( cash_limit.amt_bb) <> 0 THEN ((cash_hand_player_statistics.amt_expected_won * 1.0 )/( cash_limit.amt_bb)) ELSE 0 END) )) as "amt_expected_bb_won", (cash_hand_player_statistics.id_player) as "id_player", (sum(CAST( ((case when(cash_hand_player_statistics.flg_won_hand) then cash_hand_player_statistics.val_curr_conv * cash_hand_summary.amt_rake * ( (CASE WHEN ( (cash_hand_summary.amt_pot - cash_hand_summary.amt_rake)) <> 0 THEN (( (cash_hand_player_statistics.amt_won + cash_hand_player_statistics.amt_bet_ttl) * 1.0 )/( (cash_hand_summary.amt_pot - cash_hand_summary.amt_rake))) ELSE 0 END) ) else 0 end)) as numeric(11,2)))) as "amt_rake_attributed_curr_conv", (sum(cash_hand_player_statistics.amt_won)) as "amt_won" FROM cash_hand_player_statistics , cash_hand_summary, cash_limit WHERE (cash_hand_summary.id_hand = cash_hand_player_statistics.id_hand) AND (cash_limit.id_limit = cash_hand_player_statistics.id_limit) AND (cash_limit.id_limit = cash_hand_summary.id_limit) AND (cash_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='mynick' AND id_site='2300')) GROUP BY ((date_part('year', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS')) * 100) + date_part('month', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))), (date_part('month', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))), (date_part('year', timezone('UTC', cash_hand_player_statistics.date_played + INTERVAL '-9 HOURS'))), (cash_hand_player_statistics.id_player) ; Reason: Fatal Error (ERROR: current transaction is aborted, commands ignored until end of transaction block )

Then I ran vacuum&analyze-> click refresh ->by Date works fine now.

So... do all the errors during import mean I'm missing hands (the logs say 0 hands, 0 errors, x duplicates when I reimport, even with the "Unable to execute query" right above it)?

[kraada]

We'll look through the full log and be certain - the query in red you highlighted looks like a query for a report grouped by date (Overview - Basic?) so that failing shouldn't have any impact on anything else in the database. But clearly something strange is going on.

[pasita]

Overview-Advanced. Don't see any of this with Basic.

[pasita]

This just in from the other thread where I accidentally posted it:

by Stake, Position, Session and Date all give me the red treatment every second time I click "Refresh". But not when looking at Statistics, only Results.

This on one of my nicks only. Some show the red fail only occasionally (or it can be visible for ½ second before the correct rows kick in). Many of my nicks from the time have not imported at all.

[pasita]

Trying to reimport my Pokerstars hands. I'm getting thousands of duplicates, no Hands, no Errors (even in the log, which says New Hero encounterd... but my nick was already on the Player list). How ever, no cash hands are shown for this nick in any report. One tourney shows up, but apparently I didn't play a hand there either:(

Let me know if any of this is needed for debugging or something, I'm starting to think of complete re-installing of PT4 by tomorrow.

[kraada]

Try the latest beta first just in case that might help.

[pasita]

Updated to .18, removed my db, imported a couple of site's hands to a new db, still the same problem (one site imported fine though), except this time also in the overview-basic view. Added logs and hands to my ticket.

[kraada]

Thanks, we'll look into it and get back to you.