DBError and Network Error when saving phone numbers to contacts

Question

In our organization's new civi database, we are unable to add phone numbers to contacts. I've tried keying in the number and importing a contact with a number. (It took me hours to figure out that it was the phone numbers that were breaking the import!)

There are two error messages we get, depending on the context:

In the View Contact view of the data, when I try to save a phone number, we get this error message:

Network Error

Unable to reach the server. Please refresh this page in your browser and try again.

In the Edit Contact view, or when trying to import a record with a phone number, we get this error message:

Sorry but we are not able to provide this at the moment.

DB Error: unknown error

Return to home page.

Log is below, and seems to suggest it has something to do with stripping non-integer characters from the field. However, adding a phone number without any dashes 5554443333 also produces the error. Any ideas?

Jul 02 12:28:40 [info] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => 
CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8)
  RETURNS VARCHAR(255) CHARACTER SET utf8
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE output   VARCHAR(255) CHARACTER SET utf8 DEFAULT '';
  DECLARE iterator INT          DEFAULT 1;
  WHILE iterator < (LENGTH(input) + 1) DO
    IF SUBSTRING(input, iterator, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') THEN
      SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
    END IF;
    SET iterator = iterator + 1;
  END WHILE;
  RETURN output;
END [nativecode=1419 ** You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)]
[type] => DB_Error
[user_info] => 
CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8)
  RETURNS VARCHAR(255) CHARACTER SET utf8
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE output   VARCHAR(255) CHARACTER SET utf8 DEFAULT '';
  DECLARE iterator INT          DEFAULT 1;
  WHILE iterator < (LENGTH(input) + 1) DO
    IF SUBSTRING(input, iterator, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') THEN
      SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
    END IF;
    SET iterator = iterator + 1;
  END WHILE;
  RETURN output;
END [nativecode=1419 ** You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="
CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8)
  RETURNS VARCHAR(255) CHARACTER SET utf8
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE output   VARCHAR(255) CHARACTER SET utf8 DEFAULT '';
  DECLARE iterator INT          DEFAULT 1;
  WHILE iterator < (LENGTH(input) + 1) DO
    IF SUBSTRING(input, iterator, 1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9') THEN
      SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
    END IF;
    SET iterator = iterator + 1;
  END WHILE;
  RETURN output;
END [nativecode=1419 ** You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)]"]

)

Answer 1

Your error log says:

** You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)]

If you turn off logging at Administer > System settings > Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.) can you save phone numbers?

You can find information on the MySQL privileges needed for CiviCRM at http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements

Not all hosting companies provide these privileges without a specific request to do so (and even then some will refuse to do so).

Answer 2

I spent hours trying to fix this probably-same problem. I could edit other contact fields, but phone number always choked.

What finally fixed it for me:

$ mysql_upgrade -u root -p

The key clue was a mention of "mysql.proc" in a backtrace, which led to a mysql forum. Apparently, you're supposed to run that command whenever you upgrade mysql.

Answer 3

Not sure what the problem was, but it seems to have related to our host. We've switched hosts and the problem is solved.

Answer 4

For Joomla users, the file located at /administrator/components/com_civicrm/civicrm/CRM/Contact/BAO/Contact.php requires a semicolon at line 42:

const DROP_STRIP_FUNCTION_43 = "DROP FUNCTION IF EXISTS civicrm_strip_non_numeric;";

I hope this helps.

Answer 5

I had this same problem, caused when DreamHost upgraded from MySQL 5.7 to 8. Trigger functions aren't supported anymore unless you pay an extra $15/mo for MySQL VPS. For details on my problem see: https://chat.civicrm.org/civicrm/pl/75p1thb8w3fq9gx9rwsirphy6a I'm going to look into CiviHosting.com