5

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}
  • I tried to replicate this on two sites but could not. – Jon G - Megaphone Tech Mar 7 '16 at 13:48
  • I just tried saving this on a brand new install, and it failed to save. Beginning to wonder if caused by PHP level? – Andy Clark Mar 15 '16 at 21:37
  • See above update, I guess a problem with my hosting but don't know what's needed hence still a question – Andy Clark Jan 4 '17 at 11:11
  • Your latest update makes the issue clear. I'll post more below. – Jon G - Megaphone Tech Jan 4 '17 at 19:01
1

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. Note that the 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.

  • 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. – Andy Clark Jan 4 '17 at 19:44
  • 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 – Andy Clark Jan 13 '17 at 14:49
  • 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? – Andy Clark Jan 13 '17 at 15:09
  • 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. – Jon G - Megaphone Tech Jan 13 '17 at 17:18
  • But this works on 4.6.22 on what I think is the same shared server (I will check that this is the case). – Andy Clark Jan 13 '17 at 18:15
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.)

  • 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. – Andy Clark Jan 25 '17 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.