Skip to content

fix: remove EXPRESSION column dependency in get_indexes_sql() #1437

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
dimitri-yatsenko wants to merge 1 commit into
base: master
Choose a base branch
from
+32 −5
Open

fix: remove EXPRESSION column dependency in get_indexes_sql() #1437

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
17 changes: 16 additions & 1 deletion src/datajoint/adapters/mysql.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -737,8 +737,10 @@ def parse_foreign_key_error(self, error_message: str) -> dict[str, str | list[st
def get_indexes_sql(self, schema_name: str, table_name: str) -> str:
"""Query to get index definitions.

Note: For MySQL 8.0+, EXPRESSION column contains the expression for
Note: For MySQL 8.0.13+, EXPRESSION column contains the expression for
functional indexes. COLUMN_NAME is NULL for such indexes.
On MySQL < 8.0.13 and MariaDB, the EXPRESSION column does not exist;
heading.py falls back to get_indexes_sql_fallback() in that case.
"""
return (
f"SELECT INDEX_NAME as index_name, "
Expand All @@ -751,6 +753,19 @@ def get_indexes_sql(self, schema_name: str, table_name: str) -> str:
f"ORDER BY index_name, seq_in_index"
)

def get_indexes_sql_fallback(self, schema_name: str, table_name: str) -> str:
"""Fallback index query for MySQL < 8.0.13 and MariaDB (no EXPRESSION column)."""
return (
f"SELECT INDEX_NAME as index_name, "
f"COLUMN_NAME as column_name, "
f"NON_UNIQUE as non_unique, SEQ_IN_INDEX as seq_in_index "
f"FROM information_schema.statistics "
f"WHERE table_schema = {self.quote_string(schema_name)} "
f"AND table_name = {self.quote_string(table_name)} "
f"AND index_name != 'PRIMARY' "
f"ORDER BY index_name, seq_in_index"
)

def parse_column_info(self, row: dict[str, Any]) -> dict[str, Any]:
"""
Parse MySQL SHOW FULL COLUMNS output into standardized format.
Expand Down
20 changes: 16 additions & 4 deletions src/datajoint/heading.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -551,10 +551,22 @@ def _init_from_database(self) -> None:

# Read and tabulate secondary indexes
keys = defaultdict(dict)
for item in conn.query(
adapter.get_indexes_sql(database, table_name),
as_dict=True,
):
try:
index_rows = conn.query(
adapter.get_indexes_sql(database, table_name),
as_dict=True,
)
except Exception:
# Fall back for MySQL < 8.0.13 / MariaDB (no EXPRESSION column)
index_rows = (
conn.query(
adapter.get_indexes_sql_fallback(database, table_name),
as_dict=True,
)
if hasattr(adapter, "get_indexes_sql_fallback")
else []
)
for item in index_rows:
# Note: adapter.get_indexes_sql() already filters out PRIMARY key
# MySQL/PostgreSQL adapters return: index_name, column_name, non_unique
index_name = item.get("index_name") or item.get("Key_name")
Expand Down
Loading