search

matt-yml / pk

PK's Public system to track issues and update on work
0 stars 0 forks source link

nearby cell pokemon choaking db #54

Closed matt-yml closed 1 year ago

matt-yml commented 1 year ago

this was perturbed by the ssd failure, and may have been the root cause. related to: https://github.com/mattlesnake/gore/issues/439 https://github.com/mattlesnake/pk/issues/52

after the new ssd still getting errors

rdm  | 2022-12-17T14:29:54.721929069Z [ERR]  [POKEMON] Failed to execute query 'insert' of wild pokemon id '8653700324793148068' - cell id '9920519225913376768'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.721957782Z [ERR]  [POKEMON] Failed to execute query 'insert' of nearby_stop pokemon id '12039886487847895163' - cell id '9824223099904589824'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.722465409Z [ERR]  [POKEMON] Failed to execute query 'insert' of encounter pokemon id '18410500935956750238' - cell id '9920561885676044288'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.722603593Z [ERR]  [POKEMON] Failed to execute query 'insert' of nearby_stop pokemon id '4767006313118851658' - cell id '9824217329616027648'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.722667516Z [ERR]  [POKEMON] Failed to execute query 'insert' of nearby_stop pokemon id '14421334779586775906' - cell id '9920553712353280000'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.723040087Z [ERR]  [POKEMON] Failed to execute query 'insert' of wild pokemon id '1340621835906833167' - cell id '9823493275439333376'. (1205 - Lock wait timeout exceeded; try restarting transaction)
rdm  | 2022-12-17T14:29:54.724363533Z [ERR]  [POKEMON] Failed to execute query 'insert' of nearby_stop pokemon id '10298446541861174173' - cell id '9920570724718739

looking into a configuration issue of the scanner or db.

matt-yml commented 1 year ago

Could also be the drive controller asic, as one is realtek and the other is jmicron.

matt-yml commented 1 year ago

controller okay. needed a new 100% drive. will see how this handles after slh tonight and new spawn points from asheville.

will close if both are okay.

spawn points have significantly changed in Asheville.
this was the last area to be scanned before the migration, but hasn't been regularly scanned since.

will need to do blank scanning of all areas, remove spawn points that don't meet 'last seen criteria'.
matt-yml commented 1 year ago

looks good. no further issues.

matt-yml commented 1 year ago

issue still happens during slh and cd. looks like people with large databases are seeing this too. https://discord.com/channels/456432530531549195/496313484112560128/1058072378673999902

Image

triggers apparently lock the table for too long and the db can't keep up.

per @fabio advice dropping those triggers since they're only for the stats table. https://discord.com/channels/456432530531549195/496313484112560128/1059496576453718096

there's an existing pr to rework how stats are gathered and to lower the impact. https://github.com/RealDeviceMap/RealDeviceMap/issues/439

have run this to drop the triggers and exported the triggers as a sql file on rdmdb01. \\len01\deve\pk\volumes\rdmdb\disabled_trigs.sql

DROP TRIGGER rdmdb.pokemon_inserted;
DROP TRIGGER rdmdb.pokemon_updated;

triggers dropped and will test during the 3jan slh.

matt-yml commented 1 year ago

success!!! set mariadb sql events to turn off triggers before slh. will see about scheduling for cds.

still need to write sql to add them back in so the stats table is consistent. best match so far: https://github.com/RealDeviceMap/RealDeviceMap/blob/development/resources/migrations/71.sql

matt-yml commented 1 year ago

Works swimmingly for CD. Will open a lower level issue for db trigger restores until pr from upstream.