Wisselen van DB engine InnoDB naar MyISAM? Aangezien we nu die lelijke bug met de typeahead voor 'Adres stembureau' hebben waar we errors krijgen als er speciale karakters worden ingetypt.

[siccovansas]

Ik heb half lelijke code daarover in lokaal staan maar niet gecommit. Zie https://github.com/chris48s/cakephp-searchable/issues/1 en ook https://bugs.mysql.com/bug.php?id=72605.

Update: wellicht gerelateerd. Als je Europeweg-Noord intypt (voor Assen) dan krijg je resultaten voor Europeweg-Zuid te zien. En andersom ook. Vaag. Het geeft iig dezelfde soort error:

Exception on /t/Europaweg- [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'")

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

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 2447, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1952, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1821, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.9/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1950, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1936, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "./app/routes.py", line 409, in perform_typeahead
    results = results.order_by(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2699, in all
    return self._iter().all()
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/query.py", line 2834, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1670, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1521, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1390, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1749, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1930, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1706, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 716, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'")
[SQL: SELECT bag.openbareruimte AS bag_openbareruimte, bag.huisnummer AS bag_huisnummer, bag.huisletter AS bag_huisletter, bag.huisnummertoevoeging AS bag_huisnummertoevoeging, bag.postcode AS bag_postcode, bag.woonplaats AS bag_woonplaats, bag.gemeente AS bag_gemeente, bag.provincie AS bag_provincie, bag.nummeraanduiding AS bag_nummeraanduiding, bag.verblijfsobjectgebruiksdoel AS bag_verblijfsobjectgebruiksdoel, bag.oppervlakteverblijfsobject AS bag_oppervlakteverblijfsobject, bag.verblijfsobjectstatus AS bag_verblijfsobjectstatus, bag.object_id AS bag_object_id, bag.object_type AS bag_object_type, bag.nevenadres AS bag_nevenadres, bag.pandid AS bag_pandid, bag.pandstatus AS bag_pandstatus, bag.pandbouwjaar AS bag_pandbouwjaar, bag.x AS bag_x, bag.y AS bag_y, bag.lat AS bag_lat, bag.lon AS bag_lon, bag.verkorteopenbareruimte AS bag_verkorteopenbareruimte
FROM bag
WHERE MATCH (bag.openbareruimte) AGAINST (%(openbareruimte_1)s IN BOOLEAN MODE) AND bag.gemeente = %(gemeente_1)s ORDER BY bag.woonplaats, CAST(bag.huisnummer AS SIGNED INTEGER), bag.huisletter, bag.huisnummertoevoeging
 LIMIT %(param_1)s]
[parameters: {'openbareruimte_1': '+Europaweg-*', 'gemeente_1': 'Assen', 'param_1': 8}]
(Background on this error at: http://sqlalche.me/e/14/f405)