civicrm_strip_non_numeric does not exist

[developer]

Hi Experts,

Im trying to use Phone API to store the phone number. but I'm getting this error,

nativecode=1305 ** FUNCTION civicrm_db.civicrm_strip_non_numeric does not exist]",
   "debug_info":"INSERT INTO civicrm_phone (contact_id , is_primary , phone ) VALUES ( 67761 ,  1 , '07777777777' )  [nativecode=1305 ** FUNCTION civicrm_db.civicrm_strip_non_numeric does not exist]",

can anyone please tell me how to get rid of this. I'm using 4.3.3 version

Many Thanks

[xavier]

Hi,

Did you search the forum? it's likely not a problem with the api, but an install issue, where a mysql function hasn't been created.

http://forum.civicrm.org/index.php?topic=28652.0

The easiest is probably to add it manually, but would be good to know what happened in the upgrade so it wasn't properly created, might have other sql command that weren't properly run?

X+

[developer]

Thanks for your time to reply Xavier. I did search that link before posting this. I understand that there is problem with moving my existing installation to new location. it is working fine in my old location but not in new location. can you please tell me how can i fix this???

Many Thanks

[xavier]

Hi,

You'll need to create that function by hand (eg. from phpmyadmin) as shown in the link I posted. This being said, it might point out to other issues (eg not enough rights/different version of mysql?). But one step at at time, try to add the missing function manually.

X(avier, not lobo)

[lesley]

Sorry if I'm in the wrong place or should have started a new post.
My host migrated my site and I am now encountering this problem, which I gather is caused by how they migrated the database.

I have read all the posts which offer various solutions and now don't know which one to choose:
Would upgrading from 4.3.4 to 4.3.5 fix it?
Or I'm happy to run something in PhpMyAdmin (although it seems that the function Xavier posted doesn't work?)
Many Thanks

[kreynen]

This error was caused by not exporting stored procedures when the site was migrated.  In most cases, upgrading does not fix the error.  Instead, the stored procedure needs to be recreated.  As we discussed in http://forum.civicrm.org/index.php/topic,28652.msg123599.html#msg123599, one option is to run the following PHP in Drupal using a tool like the Devel module's execute PHP function or the core PHP filter.

Code: [Select]

civicrm_initialize( );
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)");

[Donald Lobo]

wonder if a trigger rebuild rebuilds the stored procedures also? If not, we might want to consider including it. This allows us to bypass mysql's access rules for triggers and stored procedures (the definer clause etc)

lobo

[lesley]

Thanks Kreyren

I've installed the dev module but don't really know what to do next?

"I DID get this to work using Drupal's Devel module, I went to /devel/php and ran this..."

I don't understand this - could you explain please.
Many thanks.

Lesley

[sphinx]

My environment: Joomla 2.5.24, CiviCRM 4.4.6, MySQL 5.5.37, PHP Version   5.4.28

I'm having this same issue with an update to a profile ... database is generating a similar error as others have posted, basically civicrm_strip_non_numeric does not exist.  Not surprising from what I've read, we both migrated to a new server in the past weeks AND upgraded.  Not to mention I've been doing heavy dev work and backing up / restoring databases.  I'm relatively new to Joomla & CiviCRM so didn't realize I may not have been exporting everything out of my database.

I have tried running the query in my database:

Code: [Select]

DELIMITER //
CREATE DEFINER=`oakwoodp_jmladm`@`localhost` 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
DELIMITER ;
I just get "Error" in phpAdmin with DELIMITER, and a syntax error at the first semicolon without it.

I tried to execute the URL http://<yourdomain>/civicrm/menu/rebuild?reset=1&triggerRebuild=1 for my domain but got a category not found (404) error.  Also saw a different version of it in this forum and ran that too, it at least seemed to run (website blinked), but did not resolve the issue.  The only thing I haven't tried is reinstalling / running the database update script that ran for 4.4.6.  Can I just run the update script?  How do I isolate it and run it, what's the filename in the install package?  Is there another way to access the reset / rebuild script?

Thanks for your help!  Loving what CiviCRM can do and I've barely scratched the surface.