Exception in all/tracks.geojson - suspected MySQL 8 incompatibility with SQLAlchemy

[obscurerichard]

While doing some exploratory testing on freezingsaddles.org for Bump geojson from 2.5.0 to 3.1.0 #272 I got an exception pymysql.err.OperationalError: (1305, 'FUNCTION freezing.AsBinary does not exist') - this came from deep in the guts of pmysql and sqlalchemy and . We need to triage this more and figure out what upgrades we need to do to get around this, preferably before the start of the 2025 season. This might extend into freezing-sync as well, since that uses some geometry related stuff too.

This is an unintended consequence of #281 .

@obscurerichard has limited knowledge of the MySQL geospatial facilities and the supporting libraries so debugging this may be a challenge... also the tests for this area of freezing-web, freezing-model, and freezing-sync are almost non-existent.

Leads

• sqlAlchemy converts geometry to byte using ST_AsBinary - has an answer talking about subclassing BaseGeometry to override `as_binary = 'ST_asText'
• Working with Geospatial Features in MySQL - a primer on MySQL 8 geospatial facilities, including data types and functions
• Why some spatial functions does not exists on my mysql server? - see this answer for a list of the functions that went away.
• creof/doctrine-2-spatial PR 196 that fixes MySQL 8 compatibility with AsBinary
• Migrate to GeoAlchemy 2

Solution options

• If we're really lucky, disable functionality that depends on this, if it has minimal impact (high risk, low effort)
• Find specific incompatibilities in ancient libraries and patch them (low risk, medium effort)
• Add compatibility functions that map names to modern MySQL names (low risk, medium effort) such as
• Upgrade to modern, supported SQLAlchemy and GeoAlchemy libraries (medium risk, high effort)

Security considerations

We might consider restricting who can call all/tracks.geojson and the related teams .geojson. We definitely should verify that there's no personally identifying information about the athletes in it. It looks like those geojson files could be used to build an awesome heatmap, but there's nothing in the code today that uses these.

Error logs

[2024-11-30 05:17:16,421] ERROR in app: Exception on /api/all/tracks.geojson [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.10/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.10/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1305, 'FUNCTION freezing.AsBinary does not exist')

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 1511, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 919, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 917, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.10/site-packages/flask/app.py", line 902, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)  # type: ignore[no-any-return]
  File "/app/freezing/web/utils/auth.py", line 68, in wrapped_function
    resp = make_response(f(*args, **kwargs))
  File "/app/freezing/web/views/api.py", line 245, in geo_tracks_all
    return _geo_tracks(start_date=start_date, end_date=end_date)
  File "/app/freezing/web/views/api.py", line 209, in _geo_tracks
    for ride_track in q:
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3535, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1124, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
    conn.query(q)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 563, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 825, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 1199, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.10/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.10/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.10/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1305, 'FUNCTION freezing.AsBinary does not exist')
[SQL: SELECT AsBinary(ride_tracks.gps_track) AS ride_tracks_gps_track, ride_tracks.ride_id AS ride_tracks_ride_id, ride_tracks.elevation_stream AS ride_tracks_elevation_stream, ride_tracks.time_stream AS ride_tracks_time_stream
FROM ride_tracks INNER JOIN rides ON rides.id = ride_tracks.ride_id INNER JOIN athletes ON athletes.id = rides.athlete_id
WHERE false = 1]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
Unable to set attribute activity_types on entity <Club id=122521 name='REI' resource_state=2>
Unable to set attribute activity_types_icon on entity <Club id=122521 name='REI' resource_state=2>
Unable to set attribute dimensions on entity <Club id=122521 name='REI' resource_state=2>
Unable to set attribute localized_sport_type on entity <Club id=122521 name='REI' resource_state=2>
Unable to set attribute activity_types on entity <Club id=1324205 name='Freezing Saddles 2025' resource_state=2>
Unable to set attribute activity_types_icon on entity <Club id=1324205 name='Freezing Saddles 2025' resource_state=2>
Unable to set attribute dimensions on entity <Club id=1324205 name='Freezing Saddles 2025' resource_state=2>
Unable to set attribute localized_sport_type on entity <Club id=1324205 name='Freezing Saddles 2025' resource_state=2>

[merlinorg]

@obscurerichard does anything actually use the geo data? It appeared completely unused to me.

[obscurerichard]

There's 2 endpoints that use the geojson data:

1. https://freezingsaddles.org/api/all/tracks.geojson
2. https://freezingsaddles.org/api/teams/<int:team_id>/tracks.geojson for example with the main 2025 team - Freezing Saddles 2025

See the code in freezing.web.views.api

freezing-web/freezing/web/views/api.py

Lines 184 to 256 in 8b773a2

	def _geo_tracks(start_date=None, end_date=None, team_id=None):
	# These dates must be made naive, since we don't have TZ info stored in our ride columns.
	if start_date:
	start_date = arrow.get(start_date).datetime.replace(tzinfo=None)
	if end_date:
	end_date = arrow.get(end_date).datetime.replace(tzinfo=None)
	log.debug("Filtering on start_date: {}".format(start_date))
	log.debug("Filtering on end_date: {}".format(end_date))
	sess = meta.scoped_session()
	q = sess.query(RideTrack).join(Ride).join(Athlete)
	q = q.filter(Ride.private is False)
	if team_id:
	q = q.filter(Athlete.team_id == team_id)
	if start_date:
	q = q.filter(Ride.start_date >= start_date)
	if end_date:
	q = q.filter(Ride.start_date < end_date)
	linestrings = []
	for ride_track in q:
	assert isinstance(ride_track, RideTrack)
	ride_tz = pytz.timezone(ride_track.ride.timezone)
	wkt = sess.scalar(ride_track.gps_track.wkt)
	coordinates = []
	for i, (lon, lat) in enumerate(parse_linestring(wkt)):
	elapsed_time = ride_track.ride.start_date + timedelta(
	seconds=ride_track.time_stream[i]
	)
	point = (
	float(Decimal(lon)),
	float(Decimal(lat)),
	float(Decimal(ride_track.elevation_stream[i])),
	ride_tz.localize(elapsed_time).isoformat(),
	)
	coordinates.append(point)
	linestrings.append(coordinates)
	geojson_structure = {"type": "MultiLineString", "coordinates": linestrings}
	# return geojson.dumps(geojson.MultiLineString(linestrings))
	return json.dumps(geojson_structure)
	@blueprint.route("/all/tracks.geojson")
	@auth.crossdomain(origin="*")
	def geo_tracks_all():
	# log.info("Fetching gps tracks for team {}".format(team_id))
	start_date = request.args.get("start_date")
	end_date = request.args.get("end_date")
	return _geo_tracks(start_date=start_date, end_date=end_date)
	@blueprint.route("/teams/<int:team_id>/tracks.geojson")
	@auth.crossdomain(origin="*")
	def geo_tracks_team(team_id):
	# log.info("Fetching gps tracks for team {}".format(team_id))
	start_date = request.args.get("start_date")
	end_date = request.args.get("end_date")
	return _geo_tracks(start_date=start_date, end_date=end_date, team_id=team_id)

nothing else in the freezing-webpulls on this library.

So from that standpoint we could remove the geojson library and the 2 API calls and have freezing-web be clean. I think you are right about these not being in use now. However, I think I understand what they could be used for and it's pretty exciting: Generate heatmaps for team rides #297.

I'm actually far more worried about the code paths in freezing-sync that use geospatial data. Those paths are not getting exercised properly before the competition start. I'm creating Triage MySQL 8 geospatial query compatibility with freezing-sync #56 to track that.