Answers.Body: <p>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 <code>show create function civicrm_strip_non_numeric;</code>.</p>
  
  <p>If it really is "missing", then you can also manually recreate the function - it's defined in CRM_Contact_BAO_Contact:
  <a href="https://raw.githubusercontent.com/civicrm/civicrm-core/7f8858ced29deeabc720e1c37c42e1b7d59d8962/CRM/Contact/BAO/Contact.php" rel="nofollow noreferrer">https://raw.githubusercontent.com/civicrm/civicrm-core/7f8858ced29deeabc720e1c37c42e1b7d59d8962/CRM/Contact/BAO/Contact.php</a></p>
  
  <p>[While I first did not succeed in recreating the function, the suggestions below led directly to the desired outcome. <em>-- Prónay</em>]</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-18T15:32:35.110Z
Answers.Text: When I run that statement in phpMyAdmin, it gives me the same error: `#1305 - FUNCTION civicrm_strip_non_numeric does not exist`
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-18T15:35:50.033Z
Answers.Text: 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).
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-18T16:18:03.417Z
Answers.Text: `#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 `
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-18T16:20:07.333Z
Answers.Text: That's what I got when running the statement to `CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8)` etc.
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-19T00:33:48.953Z
Answers.Text: What version of mysql do you have? And what version of Civi is installed?
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-21T02:36:57.993Z
Answers.Text: MySQL vers. 5.6.34
  
  CiviCRM vers. 4.7.23
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-22T00:13:28.610Z
Answers.Text: Can you try the version at this link? It adds DELIMITER commands: https://pastebin.com/raw/hShnKRva
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-22T00:30:14.820Z
Answers.Text: If you're using phpmyadmin it may not accept DELIMITER, but you can set it a different way - see screenshot here https://stackoverflow.com/questions/16801550/creating-functions-in-phpmyadmin-error-access-denied-you-need-the-super-privi/16802061#16802061
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-23T02:52:33.290Z
Answers.Text: Ran the linked DELIMITER command successfully. Then was able to run that simple SQL query, thereby changing over 1,000 phone numbers of various types to mobile. Thank you, Demerit!
Answers.UserDisplayName: Prónay
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-08-17T17:02:35.620Z
Answers.LastActivityDate: 2017-08-23T08:57:23.303Z
Answers.LastEditDate: 2017-08-23T08:57:23.303Z
Answers.UserDisplayName: Demerit
Body: <p>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.</p>
  
  <p><code>FUNCTION [db].civicrm_strip_non_numeric does not exist</code> is the error that pops up when I try to run the query.</p>
  
  <p>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? </p>
  
  <p>Addendum ...</p>
  
  <p>The SQL statement triggering the error:</p>
  
  <p><code>UPDATE</code>civicrm_phone<code>
  SET</code>location_type_id<code>="3"
  WHERE</code>phone<code>IS NOT NULL;</code></p>
  
Tags: <database><sql>
Title: Make phone type value "mobile" for every individual
ThreadText: # Make phone type value "mobile" for every individual
  
  (Asked by: username_0 on 2017-08-17)
  
  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:
  
  `UPDATE`civicrm\_phone`SET`location\_type\_id`="3"
  WHERE`phone`IS NOT NULL;`
  
  **Tags:** database, sql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-08-17. Score: 2 | Accepted answer
  
  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. *-- username_0*]
  
  > **Comments:**
  > **username_0**: When I run that statement in phpMyAdmin, it gives me the same error: `#1305 - FUNCTION civicrm_strip_non_numeric does not exist`  
  > **username_1**: 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).  
  > **username_0**: `#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 `  
  > **username_0**: That's what I got when running the statement to `CREATE FUNCTION civicrm_strip_non_numeric(input VARCHAR(255) CHARACTER SET utf8)` etc.  
  > **username_1**: What version of mysql do you have? And what version of Civi is installed?  
  > **username_0**: MySQL vers. 5.6.34  CiviCRM vers. 4.7.23  
  > **username_1**: Can you try the version at this link? It adds DELIMITER commands: https://pastebin.com/raw/hShnKRva  
  > **username_1**: If you're using phpmyadmin it may not accept DELIMITER, but you can set it a different way - see screenshot here https://stackoverflow.com/questions/16801550/creating-functions-in-phpmyadmin-error-access-denied-you-need-the-super-privi/16802061#16802061  
  > **username_0**: Ran the linked DELIMITER command successfully. Then was able to run that simple SQL query, thereby changing over 1,000 phone numbers of various types to mobile. Thank you, username_1!  
  
  ---
  
  