CiviCRM Community Forums (archive)

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...

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.

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

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,

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

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

$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.

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

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.