1

We need to switch every individual contact's phone type to mobile. I have an SQL query that I could run manually, but for the civicrm_phone_before_update trigger using a function that doesn't get defined when running manual queries.

FUNCTION [db].civicrm_strip_non_numeric does not exist is the error that pops up when I try to run the query.

And I had no luck trying to edit the trigger so it would temporarily associate with a different table. I'd DROP the trigger, but it seems important for day-to-day function, and I'm not confident I'd be able to manually restore it once I've finished transforming over 1,000 records from landline to mobile values. What's the right way to do this?

Addendum ...

The SQL statement triggering the error:

UPDATEcivicrm_phone SETlocation_type_id="3" WHEREphoneIS NOT NULL;

Improve this question

1 Answer 1

Reset to default
2

You might be running the manual query under a different mysql user than the one that owns the function, so you can check who the "definer" is using show create function civicrm_strip_non_numeric;.

If it really is "missing", then you can also manually recreate the function - it's defined in CRM_Contact_BAO_Contact: https://raw.githubusercontent.com/civicrm/civicrm-core/7f8858ced29deeabc720e1c37c42e1b7d59d8962/CRM/Contact/BAO/Contact.php

[While I first did not succeed in recreating the function, the suggestions below led directly to the desired outcome. -- Prónay]

Improve this answer
9
  • When I run that statement in phpMyAdmin, it gives me the same error: #1305 - FUNCTION civicrm_strip_non_numeric does not exist
    – Prónay
    Commented Aug 18, 2017 at 15:32
  • Ok. Try the second part by recreating the function using the SQL found at the provided link (I'm not posting the SQL here since it may change in the future - the source code is the best source for it).
    – Demerit
    Commented Aug 18, 2017 at 15:35
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
    – Prónay
    Commented Aug 18, 2017 at 16:18
  • That's what I got when running the statement to CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8) etc.
    – Prónay
    Commented Aug 18, 2017 at 16:20
  • What version of mysql do you have? And what version of Civi is installed?
    – Demerit
    Commented Aug 19, 2017 at 0:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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