RatingService tries to insert journal entry for missing games

[Askaholic]

I think the root cause of this issue is that sometimes game_player_stats entries fail to get created due to the mean column being unsigned and players having negative mean.

ERROR    Sep 03  20:51:44 RatingService                  Failed rating request GameRatingSummary(game_id=12564137, rating_type=<RatingType.GLOB
AL: 'global'>, teams=[TeamRatingSummary(outcome=<GameOutcome.DEFEAT: 'DEFEAT'>, player_ids={373960, 251122, 373173, 3773}), TeamRatingSummary(o
utcome=<GameOutcome.VICTORY: 'VICTORY'>, player_ids={367849, 370825, 237235, 367855})])
Traceback (most recent call last):
  File "/code/server/rating_service/rating_service.py", line 87, in _handle_rating_queue
    await self._rate(summary)
  File "/code/server/rating_service/rating_service.py", line 116, in _rate
    summary.game_id, summary.rating_type, old_ratings, new_ratings, outcome_map
  File "/code/server/rating_service/rating_service.py", line 286, in _persist_rating_changes
    await conn.execute(journal_insert_sql)
  File "/code/src/aiomysql/aiomysql/sa/connection.py", line 173, in _execute
    await cursor.execute(str(compiled), post_processed_params)
  File "/code/src/aiomysql/aiomysql/cursors.py", line 239, in execute
    await self._query(query)
  File "/code/src/aiomysql/aiomysql/cursors.py", line 457, in _query
    await conn.query(q)
  File "/code/src/aiomysql/aiomysql/connection.py", line 428, in query
    await self._read_query_result(unbuffered=unbuffered)
  File "/code/src/aiomysql/aiomysql/connection.py", line 622, in _read_query_result
    await result.read()
  File "/code/src/aiomysql/aiomysql/connection.py", line 1105, in read
    first_packet = await self.connection._read_packet()
  File "/code/src/aiomysql/aiomysql/connection.py", line 593, in _read_packet
    packet.check_error()
  File "/usr/local/lib/python3.7/site-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.IntegrityError: (1048, "Column 'game_player_stats_id' cannot be null")

Source code:

server/server/rating_service/rating_service.py

Lines 269 to 286 in ccf349e

	await conn.execute(gps_update_sql)
	rating_type_id = self._rating_type_ids[rating_type.value]
	journal_insert_sql = leaderboard_rating_journal.insert().values(
	leaderboard_id=rating_type_id,
	rating_mean_before=old_rating.mu,
	rating_deviation_before=old_rating.sigma,
	rating_mean_after=new_rating.mu,
	rating_deviation_after=new_rating.sigma,
	game_player_stats_id=select([game_player_stats.c.id]).where(
	and_(
	game_player_stats.c.playerId == player_id,
	game_player_stats.c.gameId == game_id,
	)
	),
	)
	await conn.execute(journal_insert_sql)

I think we should be checking the rowcount for the gps_update_sql statement to see how many rows were affected (hopefully you get this for free with the sqlalchemy ResultProxy object) and if it's 0 then we can break out of this function early, maybe issue a warning.

[Brutus5000]

Shouldn't this be solved by https://github.com/FAForever/db/blob/develop/migrations/V105__fix_rating_signedness.sql ?

[Askaholic]

Yes and no. The problem will still exist if the player stats entries were failed to be created for some other reason. The code here can still be made more robust.

[Askaholic]

The issue is that this sub query:

    game_player_stats_id=select([game_player_stats.c.id]).where( 
         and_( 
             game_player_stats.c.playerId == player_id, 
             game_player_stats.c.gameId == game_id, 
         ) 
     )

could fail, which will then cause the insert statement to fail. But we can already use the rowcount from the previous query to know whether or not this subquery will fail before we try it, so I don't see a reason why we shouldn't check that rowcount.