Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.7.27
-
Fix Version/s: 4.7.30
-
Component/s: None
-
Labels:None
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Contributed Code
-
Verified?:No
Description
Multi-select custom fields, because of the separation character, must be searched in SQL using regex. We must also replace certain punctuation marks that have meaning in MySQL (like parentheses). In one place in the code, we do this in a way that works with the MySQL regex library, but not the MariaDB regex library.
MySQL and MariaDB use different regex libraries. Certain non-POSIX regex statements will cause MariaDB to fail to execute a query, crashing CiviCRM. See https://jira.mariadb.org/browse/MDEV-7127 for details.
There's one instance of this happening in CiviCRM - when a multi-select custom field has a left or right parenthesis in the value. Instead of using the non-POSIX
[[.right-parenthesis.]]
we need to use the POSIX-compliant
[(]
I grepped to ensure that this is the only instance of this problem in the codebase:
$ grep -r '[[\.' * jon@zabuntu
Core/BAO/CustomQuery.php: $value = str_replace(array('[:comma:]', '(', ')'), array(',', '[[.left-parenthesis.]]', '[[.right-parenthesis.]]'), $value);
To replicate this problem on a default install:
- Change the custom field "Most Important Issue"'s input type to "Multi-Select".
- Change any of the field options to include a parenthesis in the value.
- Perform an Advanced Search for any records containing the altered field option.
Actual Result:
On MySQL, this works. On MariaDB, this results in an error (see below).
Expected Result:
The report runs successfully.
Oct 12 15:35:18 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)
[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
contact_a.id = civicrm_note.entity_id ) LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id LEFT JOIN civicrm_event ON civicrm_participant.event_id = civicrm_event.id
LEFT JOIN civicrm_value_funding_and_fees_6 ON civicrm_value_funding_and_fees_6.entity_id = `civicrm_event`.id LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id ) LEFT JOIN civicrm_discount discount ON ( civicrm_participant.discount_id = discount.id ) LEFT JOIN civicrm_option_group discount_name ON ( discount_name.id = discount.price_set_id ) WHERE ( civicrm_participant.is_test = 0 AND civicrm_value_funding_and_fees_6.funding_source_42 RLIKE '^Afamily planning - title x [[.left-parenthesis.]]003[[.right-parenthesis.]]^A' ) AND (contact_a.is_deleted = 0) [nativecode=1139 ** Got error 'POSIX collating elements are not supported at offset 28' from regexp]
[type] => DB_Error
[user_info] => SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
contact_a.id = civicrm_note.entity_id ) LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id LEFT JOIN civicrm_event ON civicrm_participant.event_id = civicrm_event.id
LEFT JOIN civicrm_value_funding_and_fees_6 ON civicrm_value_funding_and_fees_6.entity_id = `civicrm_event`.id LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id ) LEFT JOIN civicrm_discount discount ON ( civicrm_participant.discount_id = discount.id ) LEFT JOIN civicrm_option_group discount_name ON ( discount_name.id = discount.price_set_id ) WHERE ( civicrm_participant.is_test = 0 AND civicrm_value_funding_and_fees_6.funding_source_42 RLIKE '^Afamily planning - title x [[.left-parenthesis.]]003[[.right-parenthesis.]]^A' ) AND (contact_a.is_deleted = 0) [nativecode=1139 ** Got error 'POSIX collating elements are not supported at offset 28' from regexp]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND
contact_a.id = civicrm_note.entity_id ) LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id LEFT JOIN civicrm_event ON civicrm_participant.event_id = civicrm_event.id
LEFT JOIN civicrm_value_funding_and_fees_6 ON civicrm_value_funding_and_fees_6.entity_id = `civicrm_event`.id LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id ) LEFT JOIN civicrm_discount discount ON ( civicrm_participant.discount_id = discount.id ) LEFT JOIN civicrm_option_group discount_name ON ( discount_name.id = discount.price_set_id ) WHERE ( civicrm_participant.is_test = 0 AND civicrm_value_funding_and_fees_6.funding_source_42 RLIKE '^Afamily planning - title x [[.left-parenthesis.]]003[[.right-parenthesis.]]^A' ) AND (contact_a.is_deleted = 0) [nativecode=1139 ** Got error 'POSIX collating elements are not supported at offset 28' from regexp]"]
)
Oct 12 15:35:18 [info] $backTrace = #0 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error))
#3 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#4 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...", "DB_Error", TRUE)
#6 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8))
#7 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#8 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...", "DB_Error", TRUE)
#9 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1139 ** Got error 'POSIX collating elements are not supported at offset 28' f...")
#10 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#11 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#12 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2446): DB_common->query("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#13 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1635): DB_DataObject->_query("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#14 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(362): DB_DataObject->query("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#15 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(1357): CRM_Core_DAO->query("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...", TRUE)
#16 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(4863): CRM_Core_DAO::singleValueQuery("SELECT count( DISTINCT civicrm_participant.id ) as rowCount FROM civicrm_co...")
#17 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Event/Selector/Search.php(288): CRM_Contact_BAO_Query->searchQuery(0, 0, NULL, TRUE, FALSE, FALSE, FALSE, FALSE, NULL)
#18 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Selector/Controller.php(233): CRM_Event_Selector_Search->getTotalCount(4, NULL)
#19 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Event/Form/Search.php(371): CRM_Core_Selector_Controller->__construct(Object(CRM_Event_Selector_Search), NULL, NULL, 4, Object(CRM_Event_Form_Search), 1, NULL)
#20 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(447): CRM_Event_Form_Search->postProcess()
#21 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Event_Form_Search), "refresh")
#23 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Event_Form_Search), "refresh")
#24 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("refresh")
#25 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(310): CRM_Core_Controller->run((Array:3), NULL)
#26 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#27 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#28 /var/www/adap/ppncny/public_html/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:3))
#29 [internal function](): civicrm_invoke("event", "search")
#30 /var/www/adap/ppncny/public_html/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:2))
#31 /var/www/adap/ppncny/public_html/index.php(21): menu_execute_active_handler()
#32
{main}