Since I don't know whether this problem is caused by a plain syntax error of me or by SQLAlchemy, MariaDB Connector or Python I ask here:
My application worked well with SQLAlchemy 1.4, MariaDB 1.4 and Python 3.10. In order to migrate to SQLAlchemy 2.0 I made adaptions for the ORM part of SQLAlchemy. Python crash occurs with SQLAlchemy 2.0.23, MariaDB Connector 1.1.8, Python 3.12.0, MariaDB 11.1 (also 10.4), Windows 10 x64 22H2
UPDATE: Thanks to the comment from Gord Thompson below I now know that it works with PyMySQL connector. So the problems seems to be in MariaDB connector.
With this table
CREATE TABLE `accounts` (
`id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(40) NOT NULL COLLATE 'utf8mb3_general_ci',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `name` (`name`) USING BTREE
)
Python crashes with this code inside the MariaDB connector:
db = 'mariadb+mariadbconnector://***:***@localhost/test'
engine = sa.create_engine(db)
Session = orm.sessionmaker(bind=engine)
class Base(orm.DeclarativeBase):
pass
class Account(Base):
__tablename__ = 'accounts'
_id: orm.Mapped[int] = orm.mapped_column('id', sa.Integer, primary_key=True)
_name: orm.Mapped[str] = orm.mapped_column('name', sa.String)
with Session() as sess:
sel = sa.select(Account) \
.where(Account._name == 'My Account')
res = sess.execute(sel).scalars()
for acc in res:
print(acc._name)
when calling sess.execute with the following output:
C:\Users\Jens\Projects\Mails\Python> c: && cd c:\Users\Jens\Projects\Mails\Python && cmd /C ""C:\Program Files\Python\Python312\python.exe" c:\Users\Jens\.vscode\extensions\ms-python.python-2023.20.0\pythonFiles\lib\python\debugpy\adapter/../..\debugpy\launcher 50504 -- C:\Users\Jens\Projects\Mails\Python\test.py "
Debug memory block at address p=000001BDB62F37D0: API 'r'
15 bytes originally requested
The 7 pad bytes at p-7 are FORBIDDENBYTE, as expected.
The 8 pad bytes at tail=000001BDB62F37DF are FORBIDDENBYTE, as expected.
Data at p: 39 31 30 30 33 38 35 00 00 00 00 00 00 00 00
Enable tracemalloc to get the memory block allocation traceback
Fatal Python error: _PyMem_DebugRawFree: bad ID: Allocated using API 'r', verified using API 'm'
Python runtime state: initialized
[...]
Current thread 0x00000edc (most recent call first):
File "C:\Program Files\Python\Python312\Lib\site-packages\mariadb\cursors.py", line 135 in _substitute_parameters
File "C:\Program Files\Python\Python312\Lib\site-packages\mariadb\cursors.py", line 306 in execute
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\engine\default.py", line 922 in do_execute
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1969 in _exec_single_context
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1848 in _execute_context
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1639 in _execute_clauseelement
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\sql\elements.py", line 516 in _execute_on_connection
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\engine\base.py", line 1416 in execute
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\orm\context.py", line 293 in orm_execute_statement
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2190 in _execute_internal
File "C:\Program Files\Python\Python312\Lib\site-packages\sqlalchemy\orm\session.py", line 2308 in execute
File "C:\Users\Jens\Projects\Mails\Python\test.py", line 22 in <module>
[...]
Extension modules: _wmi, sqlalchemy.cyextension.collections, sqlalchemy.cyextension.immutabledict, sqlalchemy.cyextension.processors, sqlalchemy.cyextension.resultproxy, sqlalchemy.cyextension.util, greenlet._greenlet, mariadb._mariadb (total: 8)
It runs without crash when the .where clause in the SQL construct is omitted.
Any help appreciated...
I was finally able to reproduce this bug - crash seems to be caused by a bad reference count when substituting parameters inside the where clause.
I filed a ticket in MariaDB bug tracker and will update my answer once this issue is resolved.
Update: Issue has been fixed and will be available in MariaDB Connector/Python 1.1.9