drush sql dump and table prefixes

I might also add that drush sql dump example.com target.com, and its predecessor, drush sql load, will have potentially disastrous effects if the source and target use table prefixes (may migrate more than intended), and won't work at all if 'source' and 'target' have different table prefixes.

This is kind of a hard problem. Since I am not fond of table prefixes myself, I don't imagine that I will have time to tackle it myself in the near future, but I admit that it would be a desirable feature to have.

(@albert: Disclaimer: I am not a drush maintainer, I just submit sql patches here and there.)

I was just discussing table prefixes with dman in another thread, and on some reflection have decided that it probably isn't so hard if you presuppose that the source and target must be preconfigured to have the same table prefix settings. The hardest part is getting the table prefix settings. The best way to do that would be in drush_sitealias_build_record_from_settings_file and also in drush sq conf --all. If the table prefix settings are stored in an array, then call 'show tables' and filter against the array values in a simple loop. After that, you handle the result as if it were just specified via --tables-list, and the import phase will take care of itself.

@dman: Assigning to myself, but I'm not opposed to you taking this over if you want to.

my initial thoughts on prefixing

I do have a strong need for prefixing instances. For mid-range hosts that provide a only web UI but not backend Admin to CREATE DATABASE, I still want to create sites on the fly via the web. Recycling a multisite DB with prefixes does that for me. Project approach described a little over here
If I were able to run aegir-style root admin, then yeah separate DBs are good, but not for what I'm being asked to do on limited, shared platforms.

It's entirely rude and fragile, but the code that's worked for me to remap prefixes (including between non-prefixed and prefixed instances) is in my CVS and has a lump in the middle looking like

  // IF $old_prefix is set, then we should convert from that prefix to this one!
  // TRANSCODE ON THE FLY!
  // Note isset(), not is_empty() because maybe we need to go from no-prefix to a-prefix;
  if (isset($old_prefix) && $old_prefix != $new_prefix) {
    // Scary regexp may be hazardous to your health
    // newlines just to prettify the display.
    drush_print("{$WARNING}Caution{$_WARNING} {$ACTION}Transcoding database{$_ACTION} on the fly to rename tables with a old_prefix='$old_prefix' to prefix='$new_prefix' ");
    $transcode = "\\\n | sed  -e 's/TABLE `{$old_prefix}\\([^`]*\\)`/TABLE `{$new_prefix}\\1`/g' ";
    $transcode .= "\\\n | sed  -e 's/TABLES `{$old_prefix}\\([^`]*\\)`/TABLES `{$new_prefix}\\1`/g' ";
    $transcode .= "\\\n | sed  -e 's/TABLE IF EXISTS `{$old_prefix}\\([^`]*\\)`/TABLE IF EXISTS `{$new_prefix}\\1`/g' ";
    $transcode .= "\\\n | sed  -e 's/INSERT INTO `{$old_prefix}\\([^`]*\\)`/INSERT INTO `{$new_prefix}\\1`/g' ";
    $transcode .= "\\\n";
  }

  $command = "mysql $local_mysql_args -e \"truncate {$LOCAL_DB_PREFIX}watchdog; truncate {$LOCAL_DB_PREFIX}cache;  truncate {$LOCAL_DB_PREFIX}cache_menu;  \"; ";
  // mysqldump requires LOCK TABLES unless we use --single-transaction
  $command .= "mysqldump --single-transaction $local_mysql_args $table_list > $backupfilepath ";

I do it on the cli as an OS pipe to avoid stressing out PHP memory, DB dumps are routinely huge.
Is not at all clever enough to skip-data on certain tables like drush sql does, just avoids cache and watchdog.

Of course, this is not great code. But has been speeding up my development several-fold over the last year.

Nearby, I have been dropping to the commandline to get the tables list

  // If using prefixes, we need to filter only them
  $table_list = '';
  if (!empty($LOCAL_DB_PREFIX)) {
    exec("mysql $local_mysql_args -e 'show tables' | grep '^$LOCAL_DB_PREFIX' ", $tables);
    $table_list = ' --tables '. join(' ', $tables);
  }
  else {
    $LOCAL_DB_PREFIX = '';
  }

Again, not elegant, but short and working.

Biggest beware is mixing a non-prefixed instance into the same database as other prefixed ones (just don't do that - or if you do, don't use a prefix that could be confused with a core table!!).

Further thoughts on prefixing.

I've been starting to work in prefix-awareness to sql.drush.inc and it certainly is a headache. I found I had to change function signatures all over the place - which is a very bad sign.
Realizing there is usually an easier way to do it, I had a brain-fart.

How about if prefixes were notated as part of the db-url?

'db-url' => 'mysqli://dbuser:dbpass@localhost/drupal6_multisite#subsite_'

Use the URL fragment as the prefix.
... It's almost natural. The named (prefixed) subsection of the identified resource (database)

In all instances I can see, the tools (even in the wider Drupal universe) correctly uses parse_url() to break down $db_url into its components. My proposal extends mysql connection strings in an unexpected but legal way.
This approach has no impact on existing conventions. That I can see.

If this is not a batshit-crazy idea, then it means that all the drush_sitealias*() and drush_sql*() functions I've been wrestling with can continue to just pass around the $db_url and keep working.

For that matter, this approach could make sense to be passed back to drupal-core. It may make Drupal core 'prefix' exceptions less strange. Am I the first to think this?
If Drupal database prefixing is part of Drupal and to be supported (Which I firmly believe and utilize) then finding a really low-impact way of inserting it into the system - without making things more complex for folk who choose not to use prefixes - could be a big win.

If you don't use prefixes at all, you may not see the value. But prefixes have a lot of utility (for me at least) and should be supported.

Am I mad? Are there any real downsides apart from possibly getting someone who's not using parse_url($db_url) to think harder and update their home-made regular expression?
What would this extension to the convention break?
<brainstorm class="open">...

Makes sense to me.

As for passing back to core, that train has left the station. D7 uses an associative array called $databases. db_url is legacy code at this point. I don't think $databases array includes prefix though it could.

One thing to watch out for here are shared tables between sites. Tables that are shared between sites should probably be included in the site dump.

If you're copying a site to another server, odds are you meant to copy e.g. the shared_users table as well. Otherwise you've got tables that reference UIDs that might not exist.

subscribing

Has there been any progress on this issue? I have about 15 sites using database prefixes that I would like to unsnarl.

This issue was marked closed (won't fix) because Drush has moved to Github.

If this feature is still desired, you may copy it to our Github project. For best results, create a Pull Request that has been updated for the master branch. Post a link here to the PR, and please also change the status of this issue to closed (duplicate).

Please ask support questions on Drupal Answers.