Add support for MySQL views [#1864422]

Backup migrate has an error when it tries to back up a CiviCRM database. In my setup the CiviCRM database is a separate database to the Drupal default database. I've added the CiviCRM DB credentials in the settings.php file in the proper way.

When I run Backup Migrate on my CiviCRM database I get the following error:

Notice: Undefined index: create table in _get_table_structure_sql() (line 295 of [redacted] /www/sites/all/modules/backup_migrate/includes/destinations.db.mysql.inc).

I believe the problem is because CiviCRM has created a couple of MySQL views and these are confusing the code in includes/destinations.db.mysql.inc

The code in question (around line 295):

      $out .= strtr($create['create table'], array("\n" => ' ', '"' => '`'));

Looking at the $create array, in the case of a MySQL view, there is no index 'create table', rather it is 'create view'.

With this in mind, I wonder about the wisdom of using Backup Migrate to move a CiviCRM database intance, as I assume that the MySQL views are required for correct operation.

In this case I'm going to use the command line too mysqldump to migrate my CiviCRM database, so that I am confident that the MySQL views are created correctly on the destination server.

Perhaps a solution would be an error message warning the user that Backup Migrate does not support migrating MySQL views, and that the user should use a different migration strategy.

Comments

Comment #1

nzcodarnoc commented 14 December 2012 at 02:27

Here's what I did to resolve the problem:

My usual Drupal deployment process is to install Drupal on the production server, using git, and then import the database using backup_migrate.

I installed CiviCRM into a separate database and everything was humming along nicely on the development server. However I soon learned that my backup_migrate strategy was not going to work for CiviCRM.

It turns out that as well as creating MySQL views (not supported by backup_migrate), CiviCRM also stores some server configuration data in it's database.

After having a bit of a think and working through the migration guide, and another issue thread I came to settle on this process:

Get the files up on the production server, however - exclude the sites/default/files/civicrm directory - having this in place caused this problem. This is a pretty brute force approach, there's a list of directories to ignore here.
Install Drupal, but don't import anything from backup_migrate yet - I ran into out of memory errors while installing CiviCRM, and not having any modules enabled made the problem go away
Install CiviCRM
Restore Drupal using backup_migrate
Now, create a profile to backup CiviCRM, you'll have to add the CiviCRM credentials into settings.php for the database to be available. The profile should exclude the following tables:
- civicrm_domain
- civicrm_view_case_activity_recent
- civicrm_view_case_activity_upcoming
Also, you'll have to apply this quick-and-dirty workaround.
Restore the resulting file and you're in business