Answers.Body: <p>This is a MySQL permissions issue.</p>
  
  <p>When I received a similar error, it was because the MySQL version was 5.7.6 or higher.  In these newer versions of MySQL, you need the REFERENCES privilege to make use of foreign key constraints.  <a href="http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_references" rel="nofollow noreferrer">See here</a>.  Note that the <a href="https://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements" rel="nofollow noreferrer">CiviCRM MySQL Permission Requirements</a> page has been updated to reflect this.  Please speak to your hosting provider (if shared hosting) or your sysadmin (if VPS/private hosting) about adding this permission to the CiviCRM MySQL user.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-04T19:44:42.700Z
Answers.Text: Thanks for the quick response.  The MySQL version is 5.6.34 and it appears that the user has all privileges including 'REFERENCES ON' and yet it fails every time.  I will talk to my shared hosting provider & then update here.
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-13T14:49:18.363Z
Answers.Text: I contacted my hosting company and here's their answer "From the log you have sent I see that you are trying to access the table: information_schema.innodb_sys_tables Unfortunately on the shared hosting you do not have access to that table"   So it is a permissions problem - or rather, my shared hosting doesn't allow this access.
  I guess a workaround (unless I can persuade them to give me access) might be possible, as otherwise none of my clients can use this screen.
  Is this reference to this table strictly necessary?  Can anyone comment
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-13T15:09:59.243Z
Answers.Text: Also,,, just noticed that this problem is peculiar to 4.7  I should have realised that before.  Was this perhaps an 'improvement' in 4.7 that should be removed?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-13T17:18:46.950Z
Answers.Text: The issue isn't related to CiviCRM version - though it might manifest in different places in different versions.  Not having this permission set will cause problems all over the place in Civi.  Fortunately, most folks aren't using MySQL 5.7.6 yet.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-13T18:15:30.170Z
Answers.Text: But this works on 4.6.22 on what I think is the same shared server (I will check that this is the case).
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-13T19:01:46.970Z
Answers.Text: Ah, sorry, I missed one of your two comments.  A question - if you go to **Administer menu » Customize Data and Screens » Search Preferences**, is "InnoDB Full Text Search" set to "yes" or "no"?
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-23T09:06:03.530Z
Answers.Text: "InnoDB Full Text Search"  is set to 'No' in the two cases (both 4.7) where this is a problem, just as it is for my 4.6 systems.  So the problem seems to be that this works in 4.6 but not in 4.7. The code seems to be the same in both releases, and permissions are the same. Puzzling, maybe this is a bug...
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-23T09:07:38.240Z
Answers.Text: By the way, MySQL is at 5.6.34 in all these cases, where it works & where it doesn't.
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-23T14:03:03.870Z
Answers.Text: Yes - I think my initial assessment is incorrect.  Can you post the backtrace?
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-23T15:15:36.900Z
Answers.Text: I can post the backtrace, but don't see where I can add an attachment. Or should I just paste it here?
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-24T03:04:35.640Z
Answers.Text: Editing your original question to include it is best!  Post a comment here so I notice.  If you see the full SQL statement in the error anywhere, please include that too!
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-04T19:06:47.477Z
Answers.LastActivityDate: 2017-01-04T19:06:47.477Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>This is a known bug. See</p>
  
  <p><a href="https://issues.civicrm.org/jira/browse/CRM-19610" rel="nofollow noreferrer">https://issues.civicrm.org/jira/browse/CRM-19610</a></p>
  
  <p>If your environment lacks the privilege to alter the MySQL full-text index setting, saving any settings on that screen will fail.</p>
  
  <p>If you've got command-line access, you can use the following wp-cli or drush commands (you might need to provide additional parameters to supply the CMS user or site URL):</p>
  
  <pre><code>wp cv api Setting.create sequential=1 search_autocomplete_count=25
  </code></pre>
  
  <p>or</p>
  
  <pre><code>drush cvapi Setting.create sequential=1 search_autocomplete_count=25 
  </code></pre>
  
  <p>In CiviCRM 4.6, you can update the setting by directly updating the CiviCRM database. In the civicrm_settings table, look for the key "search_autocomplete_count" in the <code>name</code> column. I don't recommend this. (It looks like those options aren't in the civicrm_settings table any more in 4.7 and above.)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-25T08:37:32.077Z
Answers.Text: Thanks Dan, I was blaming my shared host! I'd be grateful for those instructions as it would be good to set some of those search preferences.
Answers.UserDisplayName: Andy Clark
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-24T23:24:18.393Z
Answers.LastActivityDate: 2017-03-06T11:02:14.933Z
Answers.LastEditDate: 2017-03-06T11:02:14.933Z
Answers.UserDisplayName: Dan O'Brien
Body: <p>On my 4.6.14 system I'd like to change the 'Autocomplete results' to (say) 15, but it won't save.  This is in 'Administer/Customise Data and Screens/Search Preferences'. I've can change the 'Smart Group cache timeout' OK but the Autocomplete results resolutely stays at 10.  I tried the demo system and it works ok, but on my test system at 4.6.14 and another system at 4.6.12 this problem is there.  Have I found a bug?
  <em>Update 4 Jan 17 - I still have this problem on 4.7.14 and it appears to be a privilege problem</em> I extracted this line from the backtrace:</p>
  
  <blockquote>
    <p>{1227 ** Access denied; you need (at least one of) the PROCESS privilege(s) fo..."}</p>
  </blockquote>
  
  <p>Here's the full backtrace from 4.7.15:</p>
  
  <pre><code>12 17:59:43  [info] $backTrace = 
  0 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Error.php(932): CRM_Core_Error::backtrace("backTrace", TRUE)
  1 [internal function](): CRM_Core_Error::exceptionHandler(Object(DB_Error))
  2 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
  3 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error-&gt;__construct("DB Error: unknown error", -1, 16, (Array:2), "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  4 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error-&gt;__construct(-1, 16, (Array:2), "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  5 [internal function](): PEAR-&gt;_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", "DB_Error", TRUE)  
  6 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))  
  7 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR-&gt;__call("raiseError", (Array:7))  
  8 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR-&gt;raiseError(NULL, -1, NULL, NULL,"\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", "DB_Error", TRUE)  
  9 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common-&gt;raiseError(-1, NULL, NULL, NULL, "1227 ** Access denied; you need (at least one of) the PROCESS privilege(s) fo...")  
  10 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli-&gt;mysqliRaiseError()  
  11 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli-&gt;simpleQuery("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  12 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/DataObject.php(2438): DB_common-&gt;query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  13 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/DataObject.php(1627): DB_DataObject-&gt;_query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  14 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/DAO.php(354): DB_DataObject-&gt;query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  15 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO-&gt;query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", TRUE)  
  16 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(187): CRM_Core_DAO::executeQuery("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  17 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(225): CRM_Core_InnoDBIndexer-&gt;findActualFtsIndexNames("civicrm_address")  
  18 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(242): CRM_Core_InnoDBIndexer-&gt;dropIndexSql("civicrm_address")  
  19 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(131): CRM_Core_InnoDBIndexer-&gt;reconcileIndexSqls("civicrm_address")  
  20 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(96): CRM_Core_InnoDBIndexer-&gt;fixSchemaDifferences()  
  21 [internal function](): CRM_Core_InnoDBIndexer::onToggleFts(FALSE, "0", (Array:13), 1)  
  22 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(363): call_user_func((Array:2), FALSE, "0", (Array:13), 1)  
  23 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(272): Civi\Core\SettingsBag-&gt;setDb("enable_innodb_fts", "0")   
  24 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(169): Civi\Core\SettingsBag-&gt;set("enable_innodb_fts", "0")  
  25 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/BAO/Setting.php(213): Civi\Core\SettingsBag-&gt;add((Array:9))  
  26 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/api/v3/Setting.php(276): CRM_Core_BAO_Setting::setItems((Array:11), (Array:1))  
  27 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_setting_create((Array:11))  
  28 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Kernel.php(161): Civi\API\Provider\MagicFunctionProvider-&gt;invoke((Array:9))  
  29 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Kernel.php(92): Civi\API\Kernel-&gt;runRequest((Array:7))  
  30 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/api/api.php(23): Civi\API\Kernel-&gt;runSafe("setting", "create", (Array:10), NULL)  
  31 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Admin/Form/Setting.php(218): civicrm_api("setting", "create", (Array:10))  
  32 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Admin/Form/Setting.php(172): CRM_Admin_Form_Setting-&gt;commonProcess((Array:11))  
  33 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Form.php(435): CRM_Admin_Form_Setting-&gt;postProcess()  
  34 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form-&gt;mainProcess()  
  35 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine-&gt;perform(Object(CRM_Admin_Form_Setting_Search), "next", "Next")  
  36 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next-&gt;perform(Object(CRM_Admin_Form_Setting_Search), "next")  
  37 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller-&gt;handle(Object(CRM_Admin_Form_Setting_Search), "next")  
  38 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page-&gt;handle("next")  
  39 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller-&gt;run()  
  40 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Utils_Wrapper-&gt;run("CRM_Admin_Form_Setting_Search", "Search Preferences", NULL)  
  41 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))  
  42 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))  
  43 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:4))  
  44 [internal function](): civicrm_invoke("admin", "setting", "search")  
  45 /home/powyscon/public_html/acivi.org.uk/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:3))  
  46 /home/powyscon/public_html/acivi.org.uk/index.php(21): menu_execute_active_handler()  
  47 {main}
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-03-07T13:48:45.237Z
Comments.Text: I tried to replicate this on two sites but could not.
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-03-15T21:37:23.913Z
Comments.Text: I just tried saving this on a brand new install,  and it failed to save.  Beginning to wonder if caused by PHP level?
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-04T11:11:12.200Z
Comments.Text: See above update, I guess a problem with my hosting but don't know what's needed hence still a question
Comments.UserDisplayName: Andy Clark
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-01-04T19:01:38.557Z
Comments.Text: Your latest update makes the issue clear. I'll post more below.
Comments.UserDisplayName: Jon G - Megaphone Tech
Tags: <mysql><configuration>
Title: Why can't I save search preferences? Specifically 'Autocomplete Results'
ThreadText: # Why can't I save search preferences? Specifically 'Autocomplete Results'
  
  (Asked by: username_0 on 2016-03-06)
  
  On my 4.6.14 system I'd like to change the 'Autocomplete results' to (say) 15, but it won't save. This is in 'Administer/Customise Data and Screens/Search Preferences'. I've can change the 'Smart Group cache timeout' OK but the Autocomplete results resolutely stays at 10. I tried the demo system and it works ok, but on my test system at 4.6.14 and another system at 4.6.12 this problem is there. Have I found a bug?
  *Update 4 Jan 17 - I still have this problem on 4.7.14 and it appears to be a privilege problem* I extracted this line from the backtrace:
  
  > {1227 \*\* Access denied; you need (at least one of) the PROCESS privilege(s) fo..."}
  
  Here's the full backtrace from 4.7.15:
  
  ```
  12 17:59:43  [info] $backTrace = 
  0 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Error.php(932): CRM_Core_Error::backtrace("backTrace", TRUE)
  1 [internal function](): CRM_Core_Error::exceptionHandler(Object(DB_Error))
  2 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
  3 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  4 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", "DB_Error", TRUE)  
  6 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))  
  7 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))  
  8 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL,"\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", "DB_Error", TRUE)  
  9 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1227 ** Access denied; you need (at least one of) the PROCESS privilege(s) fo...")  
  10 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()  
  11 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  12 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/DataObject.php(2438): DB_common->query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  13 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  14 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/DAO.php(354): DB_DataObject->query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  15 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/DAO.php(1297): CRM_Core_DAO->query("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...", TRUE)  
  16 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(187): CRM_Core_DAO::executeQuery("\n      SELECT i.name as index_name\n      FROM information_schema.innodb_sys...")  
  17 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(225): CRM_Core_InnoDBIndexer->findActualFtsIndexNames("civicrm_address")  
  18 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(242): CRM_Core_InnoDBIndexer->dropIndexSql("civicrm_address")  
  19 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(131): CRM_Core_InnoDBIndexer->reconcileIndexSqls("civicrm_address")  
  20 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/InnoDBIndexer.php(96): CRM_Core_InnoDBIndexer->fixSchemaDifferences()  
  21 [internal function](): CRM_Core_InnoDBIndexer::onToggleFts(FALSE, "0", (Array:13), 1)  
  22 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(363): call_user_func((Array:2), FALSE, "0", (Array:13), 1)  
  23 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(272): Civi\Core\SettingsBag->setDb("enable_innodb_fts", "0")   
  24 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/Core/SettingsBag.php(169): Civi\Core\SettingsBag->set("enable_innodb_fts", "0")  
  25 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/BAO/Setting.php(213): Civi\Core\SettingsBag->add((Array:9))  
  26 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/api/v3/Setting.php(276): CRM_Core_BAO_Setting::setItems((Array:11), (Array:1))  
  27 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_setting_create((Array:11))  
  28 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Kernel.php(161): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))  
  29 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/Civi/API/Kernel.php(92): Civi\API\Kernel->runRequest((Array:7))  
  30 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/api/api.php(23): Civi\API\Kernel->runSafe("setting", "create", (Array:10), NULL)  
  31 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Admin/Form/Setting.php(218): civicrm_api("setting", "create", (Array:10))  
  32 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Admin/Form/Setting.php(172): CRM_Admin_Form_Setting->commonProcess((Array:11))  
  33 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Form.php(435): CRM_Admin_Form_Setting->postProcess()  
  34 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()  
  35 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Setting_Search), "next", "Next")  
  36 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Setting_Search), "next")  
  37 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Setting_Search), "next")  
  38 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")  
  39 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(113): CRM_Core_Controller->run()  
  40 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(284): CRM_Utils_Wrapper->run("CRM_Admin_Form_Setting_Search", "Search Preferences", NULL)  
  41 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))  
  42 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))  
  43 /home/powyscon/public_html/acivi.org.uk/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:4))  
  44 [internal function](): civicrm_invoke("admin", "setting", "search")  
  45 /home/powyscon/public_html/acivi.org.uk/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:3))  
  46 /home/powyscon/public_html/acivi.org.uk/index.php(21): menu_execute_active_handler()  
  47 {main}
  
  ```
  
  **Tags:** mysql, configuration
  
  > **Comments:**
  > **username_1**: I tried to replicate this on two sites but could not.  
  > **username_0**: I just tried saving this on a brand new install,  and it failed to save.  Beginning to wonder if caused by PHP level?  
  > **username_0**: See above update, I guess a problem with my hosting but don't know what's needed hence still a question  
  > **username_1**: Your latest update makes the issue clear. I'll post more below.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2017-01-04. Score: 1 | Accepted answer
  
  This is a MySQL permissions issue.
  
  When I received a similar error, it was because the MySQL version was 5.7.6 or higher. In these newer versions of MySQL, you need the REFERENCES privilege to make use of foreign key constraints. [See here](http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_references). Note that the [CiviCRM MySQL Permission Requirements](https://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements) page has been updated to reflect this. Please speak to your hosting provider (if shared hosting) or your sysadmin (if VPS/private hosting) about adding this permission to the CiviCRM MySQL user.
  
  > **Comments:**
  > **username_0**: Thanks for the quick response.  The MySQL version is 5.6.34 and it appears that the user has all privileges including 'REFERENCES ON' and yet it fails every time.  I will talk to my shared hosting provider & then update here.  
  > **username_0**: I contacted my hosting company and here's their answer "From the log you have sent I see that you are trying to access the table: information_schema.innodb_sys_tables Unfortunately on the shared hosting you do not have access to that table"   So it is a permissions problem - or rather, my shared hosting doesn't allow this access. I guess a workaround (unless I can persuade them to give me access) might be possible, as otherwise none of my clients can use this screen. Is this reference to this table strictly necessary?  Can anyone comment  
  > **username_0**: Also,,, just noticed that this problem is peculiar to 4.7  I should have realised that before.  Was this perhaps an 'improvement' in 4.7 that should be removed?  
  > **username_1**: The issue isn't related to CiviCRM version - though it might manifest in different places in different versions.  Not having this permission set will cause problems all over the place in Civi.  Fortunately, most folks aren't using MySQL 5.7.6 yet.  
  > **username_0**: But this works on 4.6.22 on what I think is the same shared server (I will check that this is the case).  
  > **username_1**: Ah, sorry, I missed one of your two comments.  A question - if you go to **Administer menu » Customize Data and Screens » Search Preferences**, is "InnoDB Full Text Search" set to "yes" or "no"?  
  > **username_0**: "InnoDB Full Text Search"  is set to 'No' in the two cases (both 4.7) where this is a problem, just as it is for my 4.6 systems.  So the problem seems to be that this works in 4.6 but not in 4.7. The code seems to be the same in both releases, and permissions are the same. Puzzling, maybe this is a bug...  
  > **username_0**: By the way, MySQL is at 5.6.34 in all these cases, where it works & where it doesn't.  
  > **username_1**: Yes - I think my initial assessment is incorrect.  Can you post the backtrace?  
  > **username_0**: I can post the backtrace, but don't see where I can add an attachment. Or should I just paste it here?  
  > **username_1**: Editing your original question to include it is best!  Post a comment here so I notice.  If you see the full SQL statement in the error anywhere, please include that too!  
  
  ---
  
  ### Answer by username_2 on 2017-01-24. Score: 1
  
  This is a known bug. See
  
  <https://issues.civicrm.org/jira/browse/CRM-19610>
  
  If your environment lacks the privilege to alter the MySQL full-text index setting, saving any settings on that screen will fail.
  
  If you've got command-line access, you can use the following wp-cli or drush commands (you might need to provide additional parameters to supply the CMS user or site URL):
  
  ```
  wp cv api Setting.create sequential=1 search_autocomplete_count=25
  
  ```
  
  or
  
  ```
  drush cvapi Setting.create sequential=1 search_autocomplete_count=25 
  
  ```
  
  In CiviCRM 4.6, you can update the setting by directly updating the CiviCRM database. In the civicrm\_settings table, look for the key "search\_autocomplete\_count" in the `name` column. I don't recommend this. (It looks like those options aren't in the civicrm\_settings table any more in 4.7 and above.)
  
  > **Comments:**
  > **username_0**: Thanks Dan, I was blaming my shared host! I'd be grateful for those instructions as it would be good to set some of those search preferences.  
  
  ---
  
  