Double insert of same primary key for Game-Search

[Avasam]

If a player with at least 2 WR for the same category (I think this is because of sub-categories), and that category didn't yet exist in game-search, the app will try to insert twice, at the same time in two different threads.

Solution 1:

• Include subcategories as part of PRIMARY key
  I would actually like to see sub-categories in Game-Search, so this doubles as both a fix and a new feature

Solution 2:

• Batch create/update AFTER all threads are done running
  Simply adding to a list then batch updating afterward should be fine. In fact we can push the game-list update to the end and not update game list if there's thread errors. As the user update will have to be run again.

Error:

UnhandledThreadException: Unhandled exception in thread
Please report to: https://github.com/Avasam/Global_Speedrunning_Scoreboard/issues

Not uploading data as some errors were caught during execution:
Traceback (most recent call last):
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 590, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 478, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '76rxml46-5dw911l2' for key 'PRIMARY'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/Avasam/mysite/services/user_updater.py", line 169, in set_points_thread
    GameValues.create_or_update(
  File "/home/Avasam/mysite/models/game_search_models.py", line 29, in create_or_update
    return GameValues.create(game_id, category_id, platform_id, wr_time, wr_points, mean_time, run_id)
  File "/home/Avasam/mysite/models/game_search_models.py", line 56, in create
    db.session.commit()
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/scoping.py", line 162, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1027, in commit
    self.transaction.commit()
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 494, in commit
    self._prepare_impl()
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 473, in _prepare_impl
    self.session.flush()
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2470, in flush
    self._flush(objects)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2608, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 2568, in _flush
    flush_context.execute()
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 422, in execute
    rec.execute(self)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 586, in execute
    persistence.save_obj(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 239, in save_obj
    _emit_insert_statements(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1084, in _emit_insert_statements
    c = cached_connections[connection].execute(statement, multiparams)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1252, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    self.dialect.do_execute(
  File "/usr/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3.8/site-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 590, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/usr/lib/python3.8/site-packages/mysql/connector/connection.py", line 478, in _handle_result
    raise errors.get_exception(packet)
sqlalchemy.exc.IntegrityError: (mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry '76rxml46-5dw911l2' for key 'PRIMARY'
[SQL: INSERT INTO [...] {
  'game_id': '76rxml46',
  'category_id': '5dw911l2',
  'run_id': 'm3rr224m',
  'platform_id': 'jm95z9ol',
  'wr_time': 2745,
  'wr_points': 24,
  'mean_time': 2832
}]
(Background on this error at: http://sqlalche.me/e/gkpj)