From a22cd68b90ccbf75f6d91073e86ec6bbc49b461c Mon Sep 17 00:00:00 2001
From: Ankush Menat <ankush@frappe.io>
Date: Tue, 25 Feb 2025 12:55:49 +0530
Subject: [PATCH] fix: always persist all indexes added via db.add_index
 (#31177)

* fix: always persist all indexes added via db.add_index

* fix: Add `if not exists` clause for index creation

This allows replica to have same index and master to add it later
without causing SQL error. Just minor DX benefit.

* fix(postgres): don't cache if table doesn't exist

* chore: revert postgres changes

Hopeless to maintain this

(cherry picked from commit 573028ad3fbc59ed375023ede3a8b93cf5d8f1e0)
---
 frappe/commands/site.py                  | 11 -----------
 frappe/core/doctype/recorder/recorder.py |  8 --------
 frappe/database/mariadb/database.py      | 15 ++++++++++++++-
 frappe/tests/test_commands.py            |  4 ++++
 4 files changed, 18 insertions(+), 20 deletions(-)

diff --git a/frappe/commands/site.py b/frappe/commands/site.py
index 9b0d18588b09..e0be38a293f8 100644
--- a/frappe/commands/site.py
+++ b/frappe/commands/site.py
@@ -550,23 +550,12 @@ def format_app(app):
 @pass_context
 def add_db_index(context, doctype, column):
 	"Adds a new DB index and creates a property setter to persist it."
-	from frappe.custom.doctype.property_setter.property_setter import make_property_setter
-
 	columns = column  # correct naming
 	for site in context.sites:
 		frappe.init(site=site)
 		frappe.connect()
 		try:
 			frappe.db.add_index(doctype, columns)
-			if len(columns) == 1:
-				make_property_setter(
-					doctype,
-					columns[0],
-					property="search_index",
-					value="1",
-					property_type="Check",
-					for_doctype=False,  # Applied on docfield
-				)
 			frappe.db.commit()
 		finally:
 			frappe.destroy()
diff --git a/frappe/core/doctype/recorder/recorder.py b/frappe/core/doctype/recorder/recorder.py
index 10ecde4ed260..91b667b5b42e 100644
--- a/frappe/core/doctype/recorder/recorder.py
+++ b/frappe/core/doctype/recorder/recorder.py
@@ -130,14 +130,6 @@ def add_indexes(indexes):
 def _add_index(table, column):
 	doctype = get_doctype_name(table)
 	frappe.db.add_index(doctype, [column])
-	make_property_setter(
-		doctype,
-		column,
-		property="search_index",
-		value="1",
-		property_type="Check",
-		for_doctype=False,  # Applied on docfield
-	)
 	frappe.msgprint(
 		_("Index created successfully on column {0} of doctype {1}").format(column, doctype),
 		alert=True,
diff --git a/frappe/database/mariadb/database.py b/frappe/database/mariadb/database.py
index 9b1fda0a1243..49a68efde01a 100644
--- a/frappe/database/mariadb/database.py
+++ b/frappe/database/mariadb/database.py
@@ -404,14 +404,27 @@ def get_column_index(self, table_name: str, fieldname: str, unique: bool = False
 	def add_index(self, doctype: str, fields: list, index_name: str | None = None):
 		"""Creates an index with given fields if not already created.
 		Index name will be `fieldname1_fieldname2_index`"""
+		from frappe.custom.doctype.property_setter.property_setter import make_property_setter
+
 		index_name = index_name or self.get_index_name(fields)
 		table_name = get_table_name(doctype)
 		if not self.has_index(table_name, index_name):
 			self.commit()
 			self.sql(
 				"""ALTER TABLE `{}`
-				ADD INDEX `{}`({})""".format(table_name, index_name, ", ".join(fields))
+				ADD INDEX IF NOT EXISTS `{}`({})""".format(table_name, index_name, ", ".join(fields))
 			)
+			# Ensure that DB migration doesn't clear this index, assuming this is manually added
+			# via code or console.
+			if len(fields) == 1 and not (frappe.flags.in_install or frappe.flags.in_migrate):
+				make_property_setter(
+					doctype,
+					fields[0],
+					property="search_index",
+					value="1",
+					property_type="Check",
+					for_doctype=False,  # Applied on docfield
+				)
 
 	def add_unique(self, doctype, fields, constraint_name=None):
 		if isinstance(fields, str):
diff --git a/frappe/tests/test_commands.py b/frappe/tests/test_commands.py
index b71460c51f56..c81dce942882 100644
--- a/frappe/tests/test_commands.py
+++ b/frappe/tests/test_commands.py
@@ -833,6 +833,10 @@ def test_build_assets_size_check(self):
 
 
 class TestDBUtils(BaseTestCommands):
+	@skipIf(
+		not (frappe.conf.db_type == "mariadb"),
+		"Only for MariaDB",
+	)
 	def test_db_add_index(self):
 		field = "reset_password_key"
 		self.execute("bench --site {site} add-database-index --doctype User --column " + field, {})