FUNCTION civicrm_strip_non_numeric Does not exist

[civichris]

the menu rebuild worked for menu, but if you're curious the actual SQL (from show create ... ) is:

'CREATE DEFINER=`<myciviusername`@`<MySQLServer>` FUNCTION `civicrm_strip_non_numeric`(input VARCHAR(255) CHARACTER SET utf8) RETURNS varchar(255) CHARSET utf8
    NO SQL
    DETERMINISTIC
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'

[JoeMurray]

People often run into problems when trying to define a function or procedure that includes a BEGIN ... END block. The issue is that the Create itself needs to be parsed as a single statement.
The MySQL parser gets confused by the statement delimiter at the end of the statements in the BEGIN ... END block.
To overcome this difficulty, temporarily change the delimiter from a semicolon to something else, typically two forward slash characters, as follows:

DELIMITER //
put your compound create statements in here
DELIMITER ;

[Coleman Watts]

Yes. We don't do that in CiviCRM core because we are using the old php mysql_connect which doesn't support changing delimiters and ignores the semicolons, but if you are entering this query manually you'll have to rewrite it slightly to do as Joe suggests.

[JoeMurray]

I just posted http://wiki.civicrm.org/confluence/display/CRMDOC/Installation+and+Configuration+Trouble-shooting#InstallationandConfigurationTrouble-shooting-civicrm_strip_non_numericdoesnotexisterror

[JoeMurray]

DROP FUNCTION IF EXISTS INSERT_CORRESPONDENCE_ACTIVITY;
DELIMITER //
CREATE FUNCTION INSERT_CORRESPONDENCE_ACTIVITY (
source_contactid INT(10),
activity_type_id INT(10),
status_id INT(10),
file VARCHAR(255),
date_in VARCHAR(255),
date_out VARCHAR(255),
origin VARCHAR(255),
staff_initials VARCHAR(255),
response VARCHAR(255),
archived_in VARCHAR(255),
comments TEXT,
linked_person_id INT(10),
linked_person_2_id INT(10),
issue VARCHAR(255),
position VARCHAR(255),
their_file VARCHAR(255),
file_location VARCHAR(255),
target_contact_id INT(10)) RETURNS INT(10) MODIFIES SQL DATA
BEGIN
  INSERT INTO civicrm_activity (activity_type_id, subject, activity_date_time, status_id, priority_id, medium_id )
    VALUES (activity_type_id, CONCAT('NDP Vote Correspondence ', issue), date_in, status_id, priority_id, medium_id);
  SET @activity_id = LAST_INSERT_ID();
  IF source_contactid IS NOT NULL THEN
    INSERT INTO civicrm_activity_assignment(activity_id, assignee_contact_id) VALUES ( @activity_id, source_contactid);
  END IF;
  INSERT INTO civicrm_value_issue_5 (entity_id, position_56, issue_15, file_18, linked_person_22, linked_person_2_23, their_file__24, archived_in_25, option_34, response_35, file_location_38 ) VALUES (@activity_id, position, issue, file, linked_person_id, linked_person_2_id, their_file, archived_in, origin, response, file_location);
  INSERT INTO civicrm_activity_target (activity_id, target_contact_id) VALUES (@activity_id, target_contact_id);
  RETURN @activity_id;
END//
delimiter ;

[Coleman Watts]

Note that in the latest version of CiviCRM there is automated sanity-checking and rebuilding of sql functions so doing it manually should rarely be necessary.

[sphinx]

How does one access this built in feature?  Is it available in 4.6.6?  I'm running with joomla 2.5.24. I managed to delete my 'civicrm_strip_non_numeric'.  I've been reading this forum to figure out how to restore it but mysql doesn't like the syntax of the SQL statements posted (I overrode delimiters, but still didn't work) and the path for the php file doesn't work on my setup for some reason.  Any thoughts on what to try next would be greatly appreciated!

[Coleman Watts]

CiviCRM 4.4.6 will always check to see if this function exists and rebuild it if necessary when saving any contact's phone number.

[sphinx]

Thanks for responding.  This is not working in my version, for what it's worth.  I'm using 4.4.6 upgraded from 4.3.5.
I'm confident it was there after the upgrade because I didn't receive errors on my pages when we tested.   (Then, when I dumped the test database and refreshed with my backup, I lost the function and things stopped working.)

I managed to get something working by adding the following to my administrator/components/com_civicrm/admin.civicrm.php at bottom of civicrm_initialize():

Code: [Select]

CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::DROP_STRIP_FUNCTION_43);
CRM_Core_DAO::executeQuery(CRM_Contact_BAO_Contact::CREATE_STRIP_FUNCTION_43);
CRM_Core_DAO::executeQuery("UPDATE civicrm_phone SET phone_numeric = civicrm_strip_non_numeric(phone)");
Logging into the admin console, going into civicrm to make sure it initialized, then logging out and removing the code.

This worked to at least create a function definition in my database, but I think it might just be a shell with no code.... under myphpadmin, there's no edit link for it.  Is it supposed to modify the value that gets written to the database or is it just used for processing?   

I'm just wondering if something about the syntax doesn't work with some versions of MySQL; I'm on 5.5.  I saw at least another person got similar syntax errors as I with running the query in MyPHPAdmin -- I was getting bizarre syntax errors at the quote marks, tried different editors, typing directly into the screen, no quotes where they wouldn't be necessary, etc. gave up.  Tried with and without delimiter replacement.)  I also tried creating it within my database manually through the myphpadmin routines screen, but was unsuccessful -- the error was that <myuser>@localhost didn't have permissions, even though the <myuser> is setup on the database with full permissions.

[duczki]

The problem is not fixed in the newest version 4.4.6.
I am troubling with the error if I have Phone field in the registration form for Event.

I tried solution with inserting 3 lines in the php file (administrator folder in Joomla: admin.civicrm.php). It's not working for me.
I tried with MySQL query but with no success.
I tried with the link .../civicrm/menu/rebuild?reset=1&triggerRebuild=1.

None of these solutions worked for me.

[EDIT]
I enabled debuggin:

Code: [Select]

Database Error Code: 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), 1419
Additional 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)]"]
)


Any solution for that please?

[Coleman Watts]

It's a sql permission issue, due to the fact that your server is running an outdated version of mySQL. You could solve it by upgrading mySQL or else give your user SUPER privileges.

[duczki]

Indeed, I found now article saying about it:
https://www.drupal.org/node/1825018

I am digging in the Internet for the answer how to change it but unfortunately I can't find working solution.
I cannot disable binary logging if I am not SUPER user.

Code: [Select]

PURGE BINARY LOGS BEFORE '2014-09-03 09:00:00';
So the only option is to change priviledges to SUPER user.
GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

but this sql is not working for me.

any clue?

[duczki]

Unfortunately I cant be SUPERUSER and I cant disable logging.

How may I rebuild database so it can save the phone number?

Thanks!

[Donald Lobo]

might want to consider migrating to a better host / a civi specific hosting provider

check:

https://civicrm.org/providers/hosting

lobo

[duczki]

I will definitely consider it.

But for now I need to find solution that will fix this bug. anyone?