This is the D7 request of #728940: Support of db_prefix.

The way that this module lets you specify what table and what table data to include/exclude is difficult to work with in an environment where you have multiple installations in the same database.

Use case:
Test server: I normally show sites in development to my clients on a subdirectory of our own website. This website is hosted on shared hosting, allowing to create just 1 database. So I *must* use prefixes to distinguish the several projects. To ease the migration of data between local and test, I use the same prefix on our local development server.

Current situation:
- To specify what tables to exclude I get a list of all tables in the database. I have to select all tables for which the prefix is not that of the current project. With 70+ tables per project this leads to extreme long multi-selects, in which it is hard to scroll and multi-select
- To specify what table data to exclude I get again a list of all tables in the database. Difficult to scroll and select the correct cache tables.

Requested situation:
- The settings screen should allow to define an alternative way to specify tables to include and table data to exclude. (So it is in addition to the current way of working, not replacing it)
- specify tables to include: a textarea where you can specify (using SQL wildcards) what tables to backup
- specify data to exclude: a textarea where you can specify (using * wildcard) what table data to exclude.

To further clarify this, pleaese find below the help as I added to the 6.x patch posted in the issue mentioned above.
Specify what tables to include:
A list of tables to be included to (or excluded from) the backups. Add one table specification per line. Syntax = [+|-]table_name, where + = include (default), - = exclude, table_name may include the SQL wildcard %. The set of resulting tables is calculated each time the backup is executed and is the outcome of processing the lines one by one, top to bottom, getting existing tables from the (default) database using the SQL statement "show tables like 'table_name'". The default value is based on the value of $db_prefix (settings.php) and should be fine for normal use.

Exclude the data from the following tables:
The specified tables will have their structure backed up but not their contents. This is useful for excluding cache data to reduce file size. Add one table specification per line. Syntax = table_name, where table_name may include the wildcard * and should *not* be prefixed according to $db_prefix (settings.php). The default value is based on a set of tables (from core and some popular contributed modules) known to have temporary, historical or reconstructible data. Tables that are included in this list, but not included in the backup will be ignored.

- on creating a backup: the set of tables and exclude data is computed based on the selected settings and passed to the function that will do the actual backup

Are you interested in this feature? If so, I can provide a patch for it.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

magnusk’s picture

This is a problem in my workflow as well.

I think the proposed include/exclude lists with wildcard matches (similar to e.g. visibility of blocks) should replace the current interface. Multi-selects and checkboxes are cumbersome interfaces for this kind of configuration.

Ludo.R’s picture

I think there could be an easy way to separate multi-site tables.

Based on DB prefix, B&M should only backup tables with this DB prefix, so each site would have its own backup.

I know you can already do this by excluding tables, but what if we add a new module that comes with new tables?
Then we should come back to B&M settings to exclude these new tables.

DB prefix is the key!

fietserwin’s picture

It is, but note that the db prefix declaration may be an array, allowing to define different prefixes for different tables.

Moreover, I recently found that when you uninstall modules these tables will remain to exist on a target database (if they already existed over there, due to e.g. a previous backup and restore). Not sure, whether the current full specified list of tables takes this into account though (probably not, so different issue). The prefix could be used to delete all tables with that prefix, but only if it is a string, not an array)...

Ludo.R’s picture

I think, if Drupal is able to handle multiple prefixes - I myself use multiple prefixes, one for all the tables of the project, and one for sharing users tables - B&M should be able too.

Let's say we have this :

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'my_projects',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => array(
        'default'   => 'website1_',
        'users'     => 'shared_',
        'sessions'  => 'shared_',
        'authmap'   => 'shared_',
	    ),
    ),
  ),
);

It would be awesome if we could tell B&M to backup all project's tables (starting with website1_) and, if necessary, exclude users tables (starting with shared_).

Doing so, it wouldn't require that you exclude manually tables starting with website2_.

fietserwin’s picture

It is indeed possible, as my D6 patch shows...

Ludo.R’s picture

Great!

Then this could be the starting point to port this to D7 version.

fietserwin’s picture

Well, there's never been 1 reaction from the maintainer. He doesn't look too active in this queue, but is also not looking for a co-maintainer. So, if we want to develop a solution for it, we better try to do so in a sub-module, though my experience in D6 was, that a the changes are spread over many places, making it difficult to place it in a sub-module.

Ludo.R’s picture

Yes, I can imagine how difficult it would be to add this functionnality into a submodule.

However, this functionnality would be a great enhancement for anyone who uses multi-site in a single database instance with a single prefix for each website.

Hopefully the maintainer will show up here and tell us if he(she)'s interested to add this to the main module.

eFeS’s picture

Subscribing

gisle’s picture

Status: Active » Needs review
FileSize
8.82 KB

To use, test and review, please apply the attached patch to commit 50ae9afe of 7x.2.x branch (current HEAD of the 7.x-2.x branch).

Some shared hosting plans only allow users to have a single database. If somebody want to host more than a single project on such a site, using a database table prefix is neccessary. The current version of Backup and Migrate does not support the default database table prefix that Drupal let users specify during installation. This patch fixes this.

Here is a summary of the changes:

  1. Logic is added to function _get_tables (destinations.db.mysql.inc) to filter tables based upon the default database table prefix.
  2. A checkbox is added to the module configuration form in function backup_settings_form (destinations.db.inc) to activate this filter. The checkbox appears on the advanced page, under the backup options. The default is to not filter (same as today). This checkbox does not appear if the default database table prefix is not set, or empty. To make the function _get_tables pay attention to this settings, it was also necessary to modify some function parameter lists to pass on the status of this setting.
  3. The list of default tables whose data can be ignored in function backup_settings_default (destinations.db.inc) is modified to use the database table prefix.

Please review.

PS: This patch only handles the default database table prefix (as requested in the original feature request). I believe this accounts for 99 % of the use cases. If you're using multiple database prefixes (re. comments #3 and #4 above, and Share a single database across multiple sites), then, you need to disable the prefix filter and go back to micro-managing the backup configuration through the menu/checkboxes (just as you do today). Comment #4 requests a more advanced solution capable of handling multiple prefixes. I haven't bothered doing this as it falls outside my own use cases.

ronan’s picture

Thanks for the patch gisle. I don't use db prefixes much so I'm going to leave this for a community review before applying.

gisle’s picture

OK, ronan, I'll just have to be patient then.

The project has moved ahead sine September 6, and the above patch no longer applies cleanly to the current HEAD. I've re-rolled the patch against the latest dev-version, and have posted the updated version in this thread. I hope somebody finds the time to test this so that it can be committed before it has to be re-rolled again :-(.

To use, test and review, please apply the patch named backup_migrate-database_table-1064694-10.git_.patch to commit 14f4d209 (Fri Nov 1 11:52:53 2013 -0500) of 7x.2.x branch.

PS: I am using this on eight production sites, and I think it is stable. Also please note that if the site doesn't use a default prefix, it will not even show in the UI. I am on shared hosting and uses the prefix on all my sites. Without the ability to filter on the prefix B&M is really cumbersome to use on shared hosting.

So if someone can find the time to review this to move it ahead to RTBtC - it would be much apprecaited.

fietserwin’s picture

Status: Needs review » Needs work
+++ b/includes/destinations.db.inc
@@ -70,35 +70,36 @@ class backup_migrate_destination_db extends backup_migrate_destination_remote {
+        $dbase_prefix . 'cache',

If the prefix is an array this code will fail. So if you do not support it, make sure you ignore it. This code cannot make it into a module with such an enormous user base.

So your local variable $db_prefix should be the empty string if the global variable is an array:

    $dbase_prefix = $GLOBALS['databases']['default']['default']['prefix'];
    if (is_array($db_prefix) {
      $db_prefix = '';
    }
+++ b/includes/destinations.db.inc
@@ -112,7 +113,9 @@ class backup_migrate_destination_db extends backup_migrate_destination_remote {
+    global $dbase_prefix;
+    $dbase_prefix = $GLOBALS['databases']['default']['default']['prefix'];

Why make it global? It is only used locally. Repeat the normalization to the empty string.

fietserwin’s picture

I had a look at backup_migrate_files and the default db backup class in backup_migrate self. I think that, unlike what I thought in #7, it should be fairly easy to create your own db backup "destination" module by defining it in a hook and have your class (pointed to in the hook) derive from backup_migrate_destination_db (or backup_migrate_destination_db_mysql not sure). Override the _get_tables_names() and some other methods, e.g. to present your own form settings, and there you are.

Given the spare reactions form the maintainer, this could be a more viable option. It alleviates the need for a reroll on every (internal) update.

gisle’s picture

Status: Needs work » Needs review
FileSize
9.56 KB

fietserwin, thank you for pointing this out.

I've fixed the patch so that if there are multiple prefixes (as opposed to a single default prefix), the prefix is ignored. In this situation, the module will behave just as it does today and not even show the checkbox that allows the administrator to filter on the default prefix.

As for creating a sub-module to do this filtering, I am not prepared (at least not now) to do this development.

The patch works for me. I still hope that it will (eventually) reach RTBtC and be committed to the 7.x-branch.

But even if it is not commited by ronan, it will still continue to work for me. It is slightly annoying to have to reroll it from time to time, but this is not very time consuming (git is great for keeping track of diffs between branches).

To use, test and review, please apply the patch named backup_migrate-database_table-1064694-15.git_.patch to commit 14f4d209 (Fri Nov 1 11:52:53 2013 -0500) of 7x.2.x branch.

Alain DG’s picture

Hi Ronan,
I see you released a new version some days ago. Any wish to port this patch as part of a stable release soon?
Thanks

gisle’s picture

@Weblights.
see comment #11.

I don't think there is much hope of getting this patch committed until the community shows some interests and actually reviews it. The status has been "needs review" for two years now. I've rerolled it for my own purposes for every new release of the module, but have (just like Ronan, the module maintainer) concluded that this is a feature the community is not interested in having.

couturier’s picture

Status: Needs review » Closed (won't fix)

It has now been 4 years, @gisle. Please re-open if this is still an issue.

gisle’s picture

No, closing is OK with me.
(I've moved on to a better class of hosting provider, and no longer use prefixes.)