From f4f835ed225bb18bc78f4e32006733b028938240 Mon Sep 17 00:00:00 2001
From: Jitendra Purohit <jitendra@fuzion.co.nz>
Date: Mon, 26 Jun 2017 16:55:19 +0530
Subject: [PATCH] Add check for existing key index in table

---
 CRM/Core/BAO/SchemaHandler.php       | 19 ++++++++++++++++++-
 CRM/Utils/Check/Component/Schema.php | 24 +++++++++++++++++++++---
 api/v3/System.php                    |  3 ++-
 3 files changed, 41 insertions(+), 5 deletions(-)

diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php
index 1f4a93c171..c4f8c221ef 100644
--- a/CRM/Core/BAO/SchemaHandler.php
+++ b/CRM/Core/BAO/SchemaHandler.php
@@ -723,6 +723,23 @@ MODIFY      {$columnName} varchar( $length )
 
     // Compare
     $missingSigs = array_diff($requiredSigs, $existingSigs);
+
+    //CRM-20774 - Get index key which exist in db but the value varies.
+    $existingKeyIndices = array();
+    $existingKeySigs = array_intersect_key($missingSigs, $existingSigs);
+    if (!empty($existingKeySigs)) {
+      $missingSigs = array_diff_key($missingSigs, $existingKeySigs);
+      foreach ($existingKeySigs as $sig) {
+        $sigParts = explode('::', $sig);
+        foreach ($requiredIndices[$sigParts[0]] as $index) {
+          if ($index['sig'] == $sig) {
+            $existingKeyIndices[$sigParts[0]][] = $index;
+            continue;
+          }
+        }
+      }
+    }
+
     // Get missing indices
     $missingIndices = array();
     foreach ($missingSigs as $sig) {
@@ -734,7 +751,7 @@ MODIFY      {$columnName} varchar( $length )
         }
       }
     }
-    return $missingIndices;
+    return array($missingIndices, $existingKeyIndices);
   }
 
   /**
diff --git a/CRM/Utils/Check/Component/Schema.php b/CRM/Utils/Check/Component/Schema.php
index 6c8f7608e7..caf8558f22 100644
--- a/CRM/Utils/Check/Component/Schema.php
+++ b/CRM/Utils/Check/Component/Schema.php
@@ -37,11 +37,29 @@ class CRM_Utils_Check_Component_Schema extends CRM_Utils_Check_Component {
    */
   public function checkIndices() {
     $messages = array();
-    $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices();
-    if ($missingIndices) {
+    list($missingIndices, $existingKeyIndices) = CRM_Core_BAO_SchemaHandler::getMissingIndices();
+    if ($existingKeyIndices) {
+      $html = '';
+      foreach ($existingKeyIndices as $tableName => $indices) {
+        foreach ($indices as $index) {
+          $fields = implode(', ', $index['field']);
+          $html .= "<tr><td>{$tableName}</td><td>{$index['name']}</td><td>$fields</td>";
+        }
+      }
+      $keyMessage = "<p>The following tables have an index key with a mismatch in value. Please delete the key indices listed from the below table and then click on 'Update Indices' button. <p>
+        <p><table><thead><tr><th>Table Name</th><th>Key Name</th><th>Fields</th>
+        </tr></thead><tbody>
+        $html
+        </tbody></table></p>";
+    }
+    if ($missingIndices || $existingKeyIndices) {
+      $message = "You have missing indices on some tables. This may cause poor performance.";
+      if ($keyMessage) {
+        $message = $keyMessage;
+      }
       $msg = new CRM_Utils_Check_Message(
         __FUNCTION__,
-        ts('You have missing indices on some tables. This may cause poor performance.'),
+        ts($message),
         ts('Performance warning: Missing indices'),
         \Psr\Log\LogLevel::WARNING,
         'fa-server'
diff --git a/api/v3/System.php b/api/v3/System.php
index b711032972..b4b4974bf0 100644
--- a/api/v3/System.php
+++ b/api/v3/System.php
@@ -408,6 +408,7 @@ function civicrm_api3_system_updatelogtables() {
  * This adds any indexes that exist in the schema but not the database.
  */
 function civicrm_api3_system_updateindexes() {
-  CRM_Core_BAO_SchemaHandler::createMissingIndices(CRM_Core_BAO_SchemaHandler::getMissingIndices());
+  list($missingIndices) = CRM_Core_BAO_SchemaHandler::getMissingIndices();
+  CRM_Core_BAO_SchemaHandler::createMissingIndices($missingIndices);
   return civicrm_api3_create_success(1);
 }
-- 
2.25.1