ADK MySQL Reserved Keyword Issue in Schema Check Utility

[1753936488]

ADK MySQL Reserved Keyword Issue in Schema Check Utility

1. Bug Description

In the _get_schema_version_impl function of google/adk/sessions/migration/_schema_check_utils.py file, the SQL query statement uses the MySQL reserved keyword key without escaping, causing a syntax error when executing in MySQL database.

2. Problem Code Location

File: google/adk/sessions/migration/_schema_check_utils.py
Function: _get_schema_version_impl
Line: Lines 36-37

result = connection.execute(
    text("SELECT value FROM adk_internal_metadata WHERE key = :key"),
    {"key": SCHEMA_VERSION_KEY},
).fetchone()

3. Error Context

• key is a MySQL reserved keyword used for defining indexes
• Using key directly as a column name in SQL statements causes syntax errors
• Current code uses WHERE key = :key, should be escaped as WHERE \key` = :key`

4. Current Code Issue

text("SELECT value FROM adk_internal_metadata WHERE key = :key")

This line of code does not escape the MySQL reserved keyword key.

5. Fix Solution

Escape the key field name in the SQL query with backticks:

result = connection.execute(
    text("SELECT value FROM adk_internal_metadata WHERE `key` = :key"),
    {"key": SCHEMA_VERSION_KEY},
).fetchone()

6. Complete Fixed Function

def _get_schema_version_impl(inspector, connection) -> str:
  """Gets DB schema version using inspector and connection."""
  if inspector.has_table("adk_internal_metadata"):
    try:
      result = connection.execute(
          text("SELECT value FROM adk_internal_metadata WHERE `key` = :key"),
          {"key": SCHEMA_VERSION_KEY},
      ).fetchone()
      if result:
        return result[0]
      else:
        raise ValueError(
            "Schema version not found in adk_internal_metadata. The database"
            " might be malformed."
        )
    except Exception as e:
      logger.error(
          "Failed to query schema version from adk_internal_metadata: %s.",
          e,
      )
      raise

7. Impact Scope

• All ADK session management functionality using MySQL database
• Database schema version checking functionality
• Database migration functionality
• May affect ADK initialization and connection process

8. Priority

High Priority - This error prevents proper schema version checking in MySQL database environments, affecting core ADK functionality.

9. Environment

ADK Version: 1.22.0
Python Version: 3.12.9
OS: macOS

[wuliang229]

I'm thinking about not using "key" "value" as column names. Stay tuned for the fix.

[wuliang229]

Fixed by commit 94d48fc.