search

Hanabi-Live / hanabi-live

A web server that allows people to play Hanab, a cooperative card game of logic and reasoning.
https://hanab.live
GNU General Public License v3.0
177 stars 113 forks source link

delete corrupted games from DB, fix DB such that the "fucked IDs" check passes on the entire DB #1416

Open Zamiell opened 4 years ago

Zamiell commented 4 years ago

checklist of remaining tasks:


notes:

games with invalid color clues from the rainbow bug where it included brown clues in a rainbow game:

delete from games where id = 34018;
delete from games where id = 34031;
delete from games where id = 34107;

games with swapped suits or swapped colors that make them invalid, 2149 games in total

DELETE from games where (id < 30100 and variant in (6,28,29)) or (id < 34000 and variant = 159) or (id < 31200 and variant in (20,7));
padiwik commented 4 years ago

im gonna copy my messy notes for the big delete in case i need to look back

https://github.com/Zamiell/hanabi-live/commit/4101306f7d168eccadf51603da861d187d552471

OLD variant:

White & Black
White & Rainbow
Gray & Dark Rainbow

63
64
6
28
29
150
62

some of these are empty, because they were introduced 5 days before they were renamed

NOT:
Black & Dark Rainbow

30085 is OLD
30512 is NEW

sensible cutoff is 30100

DELETE from games where (id < 30100 and variant in (6,28,29)) or (id < 34000 and variant = 159) or (id < 31200 and variant in (20,7));

SELECT id,variant from games where ((id < 30100 and variant in (63,64,6,28,29,150,62)) or (id < 34000 and variant = 159) or (id < 31200 and variant in (20,7))) ORDER BY variant;

----------------------
https://github.com/Zamiell/hanabi-live/commit/2ac1997e9f39690066eaab96d1ed1908796672c4

JUST Gay Pride (new Special Mix) got changed 
must ALSO fix the colors! not anymore, we deleted those games cuz the suits are fucked as well

pink, brown, black -> black, pink, brown

33955 is OLD
34107 is NEW, but now deleted
36083 is NEW and perfectly fine

sensible cutoff is 34000
----------------------

https://github.com/Zamiell/hanabi-live/commit/a179a353e74071f30dcf7b02af03093d49f98cc5

NEW variant:

ambiguous mix 20
dual-color mix 7

(also added Special Mix, but this is irrelevant)

30951 is OLD
31129 is OLD
31163 is NEW!! BUT delete this because the colors are fucked.
31764 is NEW

sensible cutoff is 31200
-------------------------

clue COLORS also got changed

https://github.com/Zamiell/hanabi-live/commit/c8af04e3d8ae18ac7595dc0c9f225ff784f069e8#diff-7ec858dcf9083b6e82bbb24c09a2a5d4

check dual-colors cuz poop
AMBIG MIX 20, we got lucky that theres no games after 31k but before 35k

--------------------
seems like the decks are different for the games with swapped suits, perhaps due to the server aggressively "fixing" the clues?
Zamiell commented 4 years ago

did this one today, this fixes games with truncated seeds, e.g. "p5v2sCarducci G"

UPDATE games SET seed = 'p5v2sCarducci Game 1' WHERE id = 2988;
UPDATE games SET seed = 'p5v2sCarducci Game 2' WHERE id = 3036;
UPDATE games SET seed = 'p5v2sCarducci Game 3' WHERE id = 3037;
padiwik commented 4 years ago

Games with missing actions:

DELETE FROM GAMES where id in (3992, 4213, 4214, 4217, 4219, 4305);

Games where someone did something twice, and wasn't allowed to: These are detrimental characters games that didn't get recorded in the database as such

DELETE FROM GAMES where id in (13057, 13060, 13062, 13063, 13064, 13066, 13067, 13068, 13093, 13084, 13086, 13093, 13094, 13135, 13136, 13138, 13139, 13160, 13174, 13221, 13245, 13269, 13271, 13383, 13386, 13390, 13391, 13415, 13432);
padiwik commented 4 years ago 
UPDATE games SET one_extra_card = True WHERE num_players = 6 AND id < 27153;

(as per issue #1104 . you can also probably resurrect game 27152 if you want, i think you deleted it)

Zamiell commented 4 years ago

can you paste the SQL to resurrect the game?

padiwik commented 4 years ago

uhh idk, where's your backup lol

Zamiell commented 4 years ago

its in your old db, right?

padiwik commented 4 years ago

i would hold off on resurrecting games and do them all at once till stuff is fixed and we have a reliable method to remove corrupted games

Zamiell commented 4 years ago

ok, but this one will be a special case because the actual entry in the games table needs to be restored (as opposed to just a new set of game_actions)

padiwik commented 4 years ago

exactly why it's annoying to do it for just one game

padiwik commented 4 years ago

anyways, i'll log this command which we already did before, in case we plan on restoring all the games from the old db: (delete games which for some reason are missing a player)

DELETE FROM games WHERE num_players != (SELECT COUNT(id) FROM game_participants WHERE game_id = games.id);
padiwik commented 4 years ago
padiwik commented 4 years ago

Games where an old yellow (third normal color) clue was given in a Rainbow (3 Suits) game:

DELETE FROM GAMES where id in (16517, 16542, 16552, 16554, 16558);

You can find these by running 

SELECT game_actions.* FROM game_actions, games WHERE game_actions.game_id = games.id AND games.variant = 19 AND game_actions.action like '%Yellow%';

on the old db

The bug was introduced in 0d59981 and fixed soon after, probably

Zamiell commented 4 years ago

done

padiwik commented 4 years ago

Games with deck plays that had deck_plays = False:

UPDATE games SET deck_plays = True WHERE id IN (20253, 20269, 20270, 20388, 20446, 20509, 20568, 20580, 20599, 20641, 20643, 20709, 20712, 20801, 21009, 21028, 21102, 21118, 21159, 21161, 21180, 21325, 21390, 21525, 21553, 21573, 21994, 22003, 22232, 22242, 22243, 22250, 22264, 22265);

There were others, but they were removed because they were from bad variants like Rainbow & White. The query on old db was

SELECT games.* FROM game_actions, games WHERE game_actions.game_id = games.id AND games.deck_plays = False AND game_actions.action like '%from the deck%';
Zamiell commented 4 years ago

done

Zamiell commented 4 years ago

the first game with deck_plays = FALSE is 13444

padiwik commented 4 years ago 
DELETE FROM games WHERE name LIKE '!preset%' OR name LIKE '!deal%';

on my end, these games are:

   id   |          name           
--------+-------------------------
   3232 | !preset showmatch-bga-1
   3233 | !preset showmatch-bga-2
   3234 | !preset showmatch-bga-3
   3258 | !preset showmatch-bga-4
   3259 | !preset showmatch-bga-5
   9571 | !deal showmatch-deal6
   9572 | !deal showmatch-deal7
   9573 | !deal showmatch-deal8
  10529 | !deal showmatch-deal8
  11418 | !deal showmatch-deal9
  11419 | !deal showmatch-deal10
  11420 | !deal showmatch-deal11
 130581 | !deal fey1
 130593 | !deal fey1
 130595 | !deal fey1
 130596 | !deal fey1
 130597 | !deal fey1
 131719 | !deal fey1
 131731 | !deal fey1
 132918 | !deal fey2
 134137 | !deal fey2
 134280 | !deal fey2
 134288 | !deal fey3
 137940 | !deal fey1
 137943 | !deal fey3
 137976 | !deal fey4
 137987 | !deal fey4
 137991 | !deal fey5
(28 rows)
Zamiell commented 4 years ago

done

hanabi=> DELETE FROM games WHERE name LIKE '!preset%' OR name LIKE '!deal%';
DELETE 28
padiwik commented 4 years ago

edit: but tell zam how many games a query would affect

padiwik commented 4 years ago

Fix games that got mistakenly marked as variant 127:

UPDATE games SET variant = CAST((regexp_match(seed, 'v(.*)s', 'i'))[1] AS INT) WHERE seed NOT LIKE '%v'||variant||'s%';

Here's the corresponding SELECT if you want to verify before updating:

SELECT id, variant, seed, CAST((regexp_match(seed, 'v(.*)s', 'i'))[1] AS INT) FROM games WHERE seed NOT LIKE '%v'||variant||'s%';
Zamiell commented 4 years ago 
hanabi=> UPDATE games SET variant = CAST((regexp_match(seed, 'v(.*)s', 'i'))[1] AS INT) WHERE seed NOT LIKE '%v'||variant||'s%';
UPDATE 37
padiwik commented 4 years ago

Run this again, the above uncovered some more Special Mix games:

DELETE from games where id < 34000 and variant = 159;
padiwik commented 4 years ago

Strangest "variant" i've seen, run this query:

DELETE FROM games WHERE variant in (36, 5) AND id > 31200 AND id < 36415;

Notes:

new https://github.com/Zamiell/hanabi-live/commit/a179a353e74071f30dcf7b02af03093d49f98cc5#diff-7ec858dcf9083b6e82bbb24c09a2a5d4R705
old https://github.com/Zamiell/hanabi-live/commit/a179a353e74071f30dcf7b02af03093d49f98cc5#diff-7fd495a7edd5ba133f55f8aae992d665L748
Apr 20, 2019

these variants become some fucked 6 suit abomination:
* Dual-Color 5 Suits (36)
* Dual-Color & Rainbow 6 Suits (5)

suit        | touched by
----------------------------
teal        blue (green)
lime        (green) yellow
orange      yellow red
cardinal    red (purple)
indigo      (purple) blue
mahogany    red black

31200 (same as in the first swapped suit query, the gap is very broad anyways)
--------------------------------------------------------
https://github.com/Zamiell/hanabi-live/commit/b5627e811e22db7631f3a1f8aa85d102276fd600
May 7, 2019

These same variants get fixed
* Dual-Color 5 Suits (36)
* Dual-Color & Rainbow 6 Suits (5)

36416 is fixed, so that's our upper bound
--------------------------------------------------------
https://github.com/Zamiell/hanabi-live/commit/8c1bd79e5ce48e37eb35e56c23842268143f7c0e
Mar 24, 2020

This variant can be correctly remapped, yay!
OLD:
gpnotm  by br bk yr yk rk
tloci   bg gy yr rp pb
gpo

NEW:
opmgtn  yr br rk by yk bk
oltic   ry yg gb bp pr
opg

as color:
b: 123
y: 146
r: 246
k: 356

r: 123
y: 146
b: 246
k: 356
padiwik commented 4 years ago

Games from before the special-rank special suit interaction got changed:

DELETE FROM games WHERE id in (99695, 100874, 102692);

poop: (bugs happen if run out of time before any actions happen?)

DELETE FROM games WHERE id = 16190;

i don't even know wtf happened here, looks like the server got confused and thought both p5 and m5 were played:

DELETE FROM games WHERE id = 16851;

Somehow this game had a repeated action, apparently this isn't checked server-side??

DELETE FROM game_actions WHERE game_id = 203648 AND turn = 13;
UPDATE game_actions SET turn = 13 WHERE game_id = 203648 AND turn = 14;

Status: add some exceptions and everything looks fixed! (left to do is characters urgh and, separately, fixing #1398)

SELECT id from games where id in (13447, 13448, 13483, 13490, 13564, 13606, 13644, 13701, 13703, 13711, 13725, 13739, 13746, 13748, 13807, 13808, 13815, 13828, 13833, 13836, 13848, 13863, 13941, 13948, 14074, 14076, 14078, 14149, 14191, 14236, 14238, 14259, 14262, 14300, 14304, 14581, 14586, 14588, 14636, 15047, 16190, 16379, 16851, 18752, 20117, 20252, 20254, 30047, 30059, 30070, 30072, 30074, 30079, 30973, 36930, 38471, 39544, 44592, 44594, 44609, 45015, 49074, 49818, 50570, 50571, 50909, 51886, 53791, 53793, 56286, 61073, 71844, 77384, 78374, 78379, 78381, 80613, 80614, 81889, 90229, 90423, 90472, 91059, 91077, 91229, 92441, 92839, 94667, 96529, 96545, 96551, 97198, 97210, 97778, 98102, 98612, 98673, 99069, 99695, 100874, 102692, 102843, 104019, 108716, 108731, 110424, 111503, 115317, 115946, 116212, 119277, 119336, 119627, 122182, 123635, 125078, 125208, 125487, 126806, 126847, 126870, 128144, 130748, 131133, 132986, 132988, 132990, 132993, 133238, 134315, 135363, 135376, 137538, 138732, 140962, 141378, 141398, 142473, 143025, 143062, 143723, 145221, 145375, 145623, 145671, 146429, 146672, 146706, 146859, 147752, 147784, 147834, 150286, 150316, 150520, 152475, 153263, 159793, 160788, 161124, 162154, 163105, 163277, 163354, 163404, 163441, 163495, 166007, 166147, 166162, 167853, 167930, 168461, 170648, 171686, 171916, 172698, 172819, 174072, 176915, 178115, 178118, 178286, 182292, 184097, 185283, 187669, 190612, 193416, 194395, 194484, 194703, 194726, 195546, 196849, 197357, 198936, 198944, 200759, 200967, 201972, 202622, 202782, 202865, 203634, 203671, 203672, 205059, 205734, 206252, 206311, 207569, 207588, 207614, 207631, 207744, 207824, 208925, 209504, 209791, 209793, 209797, 209802, 209806, 210237, 210277, 210347, 210378, 211673, 211676, 213026, 215174, 215891, 216474, 217592, 219871, 219973, 220641, 220641, 220965, 220965, 221913, 221913, 225475, 225475, 226685, 226685, 226716, 226716, 226728, 226728, 226742, 226742, 227497, 227497, 227859, 227859, 231174, 231174, 232206, 232206, 232221, 232221) and detrimental_characters = False and name NOT LIKE '!replay%';
Zamiell commented 4 years ago 
hanabi=> DELETE from games where id < 34000 and variant = 159;
DELETE 30
Zamiell commented 4 years ago 
hanabi=> DELETE FROM games WHERE variant in (36, 5) AND id > 31200 AND id < 36415;
DELETE 6
Zamiell commented 4 years ago 
hanabi=> DELETE FROM games WHERE id in (99695, 100874, 102692);
DELETE 3
Zamiell commented 4 years ago 
hanabi=> DELETE FROM games WHERE id = 16190;
DELETE 1
hanabi=> DELETE FROM games WHERE id = 16851;
DELETE 1
hanabi=> DELETE FROM game_actions WHERE game_id = 203648 AND turn = 13;
DELETE 1
hanabi=> UPDATE game_actions SET turn = 13 WHERE game_id = 203648 AND turn = 14;
UPDATE 1
Zamiell commented 4 years ago

add to TODO list: check all DB for character metadata that is not 0 written to DB on characters not 0, 1, 2, or 3

Zamiell commented 4 years ago

did:

hanabi=> UPDATE game_participants SET character_metadata = character_metadata + 1;
UPDATE 621148
hanabi=> UPDATE game_participants SET character_metadata = 0
hanabi-> WHERE character_assignment != 0 AND character_assignment != 1 AND character_assignment != 2 AND character_assignment != 3;
UPDATE 4157
padiwik commented 4 years ago
padiwik commented 4 years ago

Here's what I have for characters. Let me know if something seems off....

Renumber Follower:

UPDATE game_participants SET character_assignment = 31 WHERE character_assignment = 16;

Impossible Hesitant plays:

DELETE FROM games WHERE id IN (13447, 13564, 13644, 13746, 13748, 14149, 14191, 14300, 16379);

Bugged Insistent: (fixed in 2f6e54c1108a9adc5b179b9f33d49db15359a1c2 which fits the timing of the last bugged game)

DELETE FROM games WHERE id IN (13448, 13483, 13606, 13701, 13863, 14074, 14636, 20117, 30079, 36930, 44594, 44609, 50909, 53791, 71844, 80613, 90229, 99069, 111503, 125487, 126847, 128144, 130748, 146859, 147752, 147784, 167930, 170648);

Deleted Gambler: (rip)

DELETE FROM games USING game_participants WHERE games.id = game_participants.game_id AND game_participants.character_assignment = 20;

Bugged Color-Blind:

DELETE FROM games WHERE id IN (13828, 13836, 14076, 14259);

Bugged Stubborn:

DELETE FROM games WHERE id IN (38471, 44592, 53793, 61073, 78379, 80614, 92441, 92839, 94667, 96529, 96545, 96551, 97210)

Bugged Compulsive + Forgetful?

DELETE FROM games WHERE id = 150316;

Genius gives clue to different people:

DELETE FROM games WHERE id = 162154;

Genius died mid-way:

DELETE FROM games WHERE id IN (56286, 94094, 145671, 150520);

After deleting those genius games, run this python script (takes ~1s) https://github.com/padiwik/hanabi-live/blob/genius_swap/scripts/python/genius_swap.py

(It might also be the case that some of these Genius games are bugged, hence they were terminated: 162154, 163495, 163105, 166007, 166162, 167853, 171916)

Zamiell commented 4 years ago

the following fixes old misplays (where "failed=true" wasn't part of the "discard" action)

hanabi=> DELETE FROM game_actions WHERE game_id < 34000;
DELETE 1053295
hanabi=# ALTER TABLE game_actions DISABLE TRIGGER ALL;
ALTER TABLE
hanabi=# \copy game_actions(game_id, turn, type, target, value) FROM '/tmp/early_actions.tsv';
COPY 1059001
hanabi=# ALTER TABLE game_actions ENABLE TRIGGER ALL;
ALTER TABLE
Zamiell commented 4 years ago 
hanabi=> UPDATE game_participants SET character_assignment = 31 WHERE character_assignment = 16;
UPDATE 14
hanabi=> DELETE FROM games WHERE id IN (13447, 13564, 13644, 13746, 13748, 14149, 14191, 14300, 16379);
DELETE 9
hanabi=> DELETE FROM games WHERE id IN (13448, 13483, 13606, 13701, 13863, 14074, 14636, 20117, 30079, 36930, 44594, 44609, 50909, 53791, 71844, 80613, 90229, 99069, 111503, 125487, 126847, 128144, 130748, 146859, 147752, 147784, 167930, 170648);
DELETE 28
hanabi=> DELETE FROM games USING game_participants WHERE games.id = game_participants.game_id AND game_participants.character_assignment = 20;
DELETE 15
hanabi=> DELETE FROM games WHERE id IN (13828, 13836, 14076, 14259);
DELETE 4
hanabi=> DELETE FROM games WHERE id IN (38471, 44592, 53793, 61073, 78379, 80614, 92441, 92839, 94667, 96529, 96545, 96551, 97210);
DELETE 13
hanabi=> DELETE FROM games WHERE id = 150316;
DELETE 1
hanabi=> DELETE FROM games WHERE id = 162154;
DELETE 1
hanabi=> DELETE FROM games WHERE id IN (56286, 94094, 145671, 150520);
DELETE 4
padiwik commented 4 years ago

run this to prune actions:

DELETE FROM game_actions WHERE NOT EXISTS(SELECT NULL FROM games WHERE games.id = game_id);

This deleted 8386 rows for me, which seems reasonable

Zamiell commented 4 years ago 
After deleting those genius games, run this python script (takes ~1s)
https://github.com/padiwik/hanabi-live/blob/genius_swap/scripts/python/genius_swap.py

I've run this, how can i verify that it worked before proceeding?

padiwik commented 4 years ago

do games with genius load correctly? (example https://hanabi.live/replay/13808 ) then it worked!

padiwik commented 4 years ago

oh wait thats an old one

padiwik commented 4 years ago

https://hanabi.live/replay/91077

Zamiell commented 4 years ago

ok, so it worked! horray

padiwik commented 4 years ago

one nice thing about this is it's reversible, if you run it twice you get backed to where you started lol

Zamiell commented 4 years ago

to prune orphaned game participants / game actions / game tags:

hanabi=> DELETE FROM game_actions WHERE NOT EXISTS(SELECT NULL FROM games WHERE games.id = game_id);
DELETE 5706
hanabi=> DELETE FROM game_participants WHERE NOT EXISTS(SELECT NULL FROM games WHERE games.id = game_id);
DELETE 0
hanabi=> DELETE FROM game_tags WHERE NOT EXISTS(SELECT NULL FROM games WHERE games.id = game_id);
DELETE 0
Zamiell commented 4 years ago

early_actions.tsv.gz

here are the early actions that I uploaded

padiwik commented 4 years ago

There are two undeleted game ids with dupe characters: 13458, 13468

I hope this isn't indicative of character ids having changed

Zamiell commented 4 years ago 
hanabi=> DELETE FROM games where id IN (13458, 13468);
DELETE 2
padiwik commented 4 years ago

i didn't say delete...

Zamiell commented 4 years ago

its not legal to have 2 dupe characters, so they should be deleted (unless they had character that got swapped?)

padiwik commented 4 years ago

ok, it seems to have been fixed here https://github.com/Zamiell/hanabi-live/commit/d9542a4ab7d8764c7762ca7263fcaeaf4bfc5ba7#diff-c891e0aecd36e977b9c0f2930a8de4beR223 so i buy it that the dupes actually happened

but then again, shouldnt there be more by the laws of probability?

Zamiell commented 4 years ago

can you make a SQL query to check for dupe chars?