Sqlalchemy can't reflect schema with foreign key

[MhhhxX]

Dolt

Create a Dolt database with the following tables, and fire up the server (version 1.42.15):

create table Task (TASK_ID int not null, primary key (TASK_ID));

create table Item (task_id int not null, item_no int not null, primary key (task_id, item_no), foreign key (task_id) references Task(TASK_ID));

Python

Install sqlalchemy==1.4 via pip, and then execute the following:

engine = sa.create_engine('mysql+mysqlconnector://[email protected]/<db_name>')
metadata = sa.MetaData(bind=engine)
metadata.reflect()

The python code produces following error:

Traceback (most recent call last):
  File "/home/m/PycharmProjects/src/test.py", line 6, in <module>
    metadata.reflect()
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 4901, in reflect
    Table(name, self, **reflect_opts)
  File "<string>", line 2, in __new__
  File "/home/m/PycharmProjectsvenv/lib/python3.12/site-packages/sqlalchemy/util/deprecations.py", line 375, in warned
    return fn(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 618, in __new__
    with util.safe_reraise():
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 614, in __new__
    table._init(name, metadata, *args, **kw)
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 689, in _init
    self._autoload(
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/sql/schema.py", line 724, in _autoload
    conn_insp.reflect_table(
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 795, in reflect_table
    self._reflect_fk(
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 950, in _reflect_fk
    fkeys = self.get_foreign_keys(
            ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 564, in get_foreign_keys
    return self.dialect.get_foreign_keys(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 2, in get_foreign_keys
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/engine/reflection.py", line 55, in cache
    ret = fn(self, con, *args, **kw)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/base.py", line 2891, in get_foreign_keys
    self._correct_for_mysql_bugs_88718_96365(fkeys, connection)
  File "/home/m/PycharmProjects/venv/lib/python3.12/site-packages/sqlalchemy/dialects/mysql/base.py", line 2970, in _correct_for_mysql_bugs_88718_96365
    fkey["referred_table"] = rec["TABLENAME"]
                             ~~~^^^^^^^^^^^^^
KeyError: 'TABLENAME'

[timsehn]

Interesting...there must be some foreign key syntax we don't support (or don't exactly match MySQL). We'll fix this tomorrow. It's a holiday today. Thanks for the report.

[fulghum]

Hi @MhhhxX, thanks for reporting this one. I am able to reproduce the error. I tracked down the query that SQL Alchemy is executing to this:

select table_schema, table_name, column_name from information_schema.columns where (table_schema, table_name, lower(column_name)) in (('sep3', 'Task', 'TASK_ID'));

This query doesn't return any results, so SQL Alchemy must fall back to the TABLE_NAME placeholder that we see in the error message. It's odd that SQL Alchemy is using the lower() function in the filter expression, but then providing the matching value in upper case (i.e. TASK_ID). Without including the lower() function in the query, this does execute correctly with Dolt, but with it included, no results are returned.

My first guess is that this may come down to differences in collations used in MySQL and Dolt. I'll poke around some more there and see what we can find.

[fulghum]

A couple more clues on this one...

When used directly in an equality condition, the match is treated case insensitively:

select table_schema, table_name, column_name from information_schema.columns where table_name='TAsk' and table_schema='SEP3';
+--------------+------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+--------------+------------+-------------+
| sep3         | Task       | TASK_ID     |
+--------------+------------+-------------+

When used with an IN match, with one matching column, the query works correctly – seems like we probably rewrite that case to be an equality match. When using with an IN match with multiple columns, the query does not execute case insensitively:

select table_schema, table_name, column_name from information_schema.columns where (table_name, table_schema) in (('TAsk', 'SEP3'));
Empty set (0.01 sec)

When the collation is explicitly added after the lower call, the query also works correctly:

 select table_schema, table_name, column_name from information_schema.columns where (table_name, lower(table_schema) collate utf8mb3_tolower_ci) in (('TAsk', 'SEP3'));
+--------------+------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
+--------------+------------+-------------+
| sep3         | Task       | TASK_ID     |
+--------------+------------+-------------+

[jycor]

Hey @MhhhxX, thanks for reporting this issue!
@fulghum was spot on about this being a collation issue, and we've merged the fix for this to GMS.
It's making its way to Dolt, and we should have a release for you out later today.

[MhhhxX]

Awesome thank you very much for the quick help!

[bpf120]

@MhhhxX we'd love to learn what you are building. Feel free to email me or swing by our Discord if you want to share.