FUNCTION civicrm_strip_non_numeric Does not exist

[Shai]

Herschel and all,

I copied that code from here and pasted it directly into a phpMyAdmin SQL box. I should have pasted it to my text editor before copying and pasting it into the SQL box.

I'm off to a meeting; I'll try that better copy/paste method later and report back. I appreciate the help.

Shai

[kreynen]

I just ran into this too. 

Running /civicrm/menu/rebuild&reset=1&triggerRebuild=1 did NOT resolve the problem.

I could not get the copy of  the query in /CRM/Contact/BAO/Contact.php to run directly like Hershel suggested.

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

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

There must be something happening to the formatting of the query when processed by executeQuery.

[Shai]

I followed @kreynen's advice and now all is good with the phone field!

Thanks so much to @kreynen and @hershel for your support.

Shai

[Coleman Watts]

The pattern here might be that everyone who is experiencing this problem has recently migrated from one server to another. If that is the case, you should note that migrating stored procedures and triggers in mySql is a messy affair if you do not keep the same name for your database. I do not know many details about this, but I got bitten by this as far back as v4.1 when migrating a database (at that time CiviCase was the only component using them so the result was that everything worked except for fatal errors when viewing a contact's case tab).
So what I found is:
Server 1: my_database_name -> Server 2: my_database_name == GOOD
Server 1: my_database_name -> Server 2: some_other_name == TROUBLE

[kreynen]

Should there be a message when running /civicrm/menu/rebuild&reset=1&triggerRebuild=1.  I can't tell if that is broken as well or just doesn't fix the problem.

Should that be achieving the same thing as running those functions with Devel?

[Shai]

In my case I was upgrading civi on the same server. No moving from one server to another.

However, as part of the upgrade process I did need to start from scratch and do a database restore.

I use the method:

• mysqldump
• $ mysql> DROP DATABASE dbname;
• $ mysql> CREATE DATABASE dbname;
• And then use mysql to restore the db via the queries in the sql from the dump on the new database.

So maybe it is that method that is a problem. I always use the same name when creating the new database, but maybe there is still an issue.

Shai

[Hershel]

Does your dump include functions?

[Shai]

@Hershel,

Bingo... that was the problem!

I just learned that I need to add the "--routines" flag to the mysqldump command.

Here is a blog post I found just now on the issue with some simple documentation: http://www.logikdev.com/2011/03/01/how-to-dump-mysql-functions-and-stored-procedures/

Does anyone know if backup_migrate saves the functions ands triggers?

Thanks everyone.

Shai

[Slovak]

It would be also interesting to know whether the drush integration command civicrm-sql-dump includes the functions/routines.

[kreynen]

Look like backup_migrate does NOT include --routines...

http://drupalcode.org/project/backup_migrate.git/blob/refs/heads/7.x-2.x:/includes/destinations.db.mysql.inc#l150

Code: [Select]

$command = 'mysqldump --result-file=%file --opt -Q --host=%host --port=%port --user=%user --password=%pass %db';
That makes sense since Drupal normally wouldn't include that.  Looking at civicrm-sql-dump...

https://github.com/civicrm/civicrm-drupal/blob/7.x-master/drush/civicrm.drush.inc#L958

it ultimately calls drush_sql_build_dump_command...

http://drupalcontrib.org/api/drupal/contributions!drush!commands!sql!sql.drush.inc/function/drush_sql_build_dump_command/7

which doesn't look like it includes --routines either.

[Slovak]

Thanks for that research. Based on that it seems that if either backup_migrate or civicrm-sql-dump is used - best way to restore would be to run a new installation wizard and then load the data from the sql file into the created db.

[Hershel]

That should work, yes, if your dump includes DROP TABLE statements

[Coleman Watts]

Wouldn't visiting .../civicrm/menu/rebuild&reset=1&triggerRebuild=1 after migrating also fix it?

[kreynen]

I don't know why, but it doesn't.

I'm trying to follow this from https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/BAO/SchemaHandler.php#L305 to https://github.com/civicrm/civicrm-core/blob/master/CRM/Core/DAO.php#L1533 to figure out why.  My guess is there is something else missing too and we're just working around that with the Devel/php fix.

Somewhere in this thread I was asking if there should be some confirmation that the menu and triggers where rebuilt?  When I run that on http://drupal.demo.civicrm.org/civicrm/menu/rebuild&reset=1&triggerRebuild=1, I don't see one so it's really unclear if that is doing what it is intended or just skipping it.

[Donald Lobo]

one thing i've noticed when migrating db's is permissions and users come into play a wee bit

there is the definer statement in the mysqldump. in my experiments, i typically struggle and try to get past them (via root super access) to focus on the problem

but i suspect this might be an issue

seems like with backup and migrate, our recommendation might be:

a. Dont backup triggers and stored procedures
b. Do a menu and trigger rebuild on restore

lobo