fix: Create index only if not exists during MySQL online store update (#3905)

gee-senbong · web-flow · commit 2f99a617b6a5 · 2024-01-30T12:50:13.000-05:00
Update mysql.py to create index only if not exists during update

Signed-off-by: senbong &lt;senbong@featurebyte.com&gt;
diff --git a/sdk/python/feast/infra/online_stores/contrib/mysql_online_store/mysql.py b/sdk/python/feast/infra/online_stores/contrib/mysql_online_store/mysql.py
@@ -178,18 +178,28 @@ def update(
 
         # We don't create any special state for the entities in this implementation.
         for table in tables_to_keep:
+
+            table_name = _table_id(project, table)
+            index_name = f"{table_name}_ek"
             cur.execute(
-                f"""CREATE TABLE IF NOT EXISTS {_table_id(project, table)} (entity_key VARCHAR(512),
+                f"""CREATE TABLE IF NOT EXISTS {table_name} (entity_key VARCHAR(512),
                 feature_name VARCHAR(256),
                 value BLOB,
                 event_ts timestamp NULL DEFAULT NULL,
                 created_ts timestamp NULL DEFAULT NULL,
                 PRIMARY KEY(entity_key, feature_name))"""
             )
 
-            cur.execute(
-                f"ALTER TABLE {_table_id(project, table)} ADD INDEX {_table_id(project, table)}_ek (entity_key);"
+            index_exists = cur.execute(
+                f"""
+                SELECT 1 FROM information_schema.statistics
+                WHERE table_schema = DATABASE() AND table_name = '{table_name}' AND index_name = '{index_name}'
+                """
             )
+            if not index_exists:
+                cur.execute(
+                    f"ALTER TABLE {table_name} ADD INDEX {index_name} (entity_key);"
+                )
 
         for table in tables_to_delete:
             _drop_table_and_index(cur, project, table)
