Now, I know I've dug my own hole a little here, so I'm probably missing a better way of doing it, but...

We host and sync a large number of disparate sites.
As it's a team environment, I try to put any useful settings tweaks into our global /etc/drushrc.php as best-practices are discovered.

What I got to last month was a few extra default tables added to our structure-tables.
EG, we decided to not bother with the search datasets when taking fast dev copies, as they took up 2/3 of any given DB snapshot and were irrelevant for dev.

$options['structure-tables'] = array(
  'common' => array(
    'apachesolr_search_node',
    'batch',
    'cache', 'cache_filter', 'cache_menu', 'cache_page',
    'cache_form',
    'history',
    'search_dataset', 'search_index', 'search_node_links', 'search_total',
    'sessions',
    'watchdog',
  ),
);
// HEY! the above doesn't work unless we ALSO do this:
$options['structure-tables-key']='common';

So far so good - for that instance.
Now I find that other, simpler sites cannot sql-sync, sql-dump or (as I'm getting in to this week) archive-dump because the attempts at mysqldump throw
mysqldump: Couldn't find table: "apachesolr_search_node". Same sort of story if dumping a site that doesn't even use core search.

And indeed, yeah. That table (and a few others) doesn't always exist on all databases. The generated command is basically invalid.

mysqldump --no-data  --single-transaction --opt -Q  dbname --host=localhost --user=dbuser --password=dbpass apachesolr_search_node batch cache cache_filter cache_menu cache_page cache_form history search_dataset search_index search_node_links search_total sessions watchdog >> /tmp/drush_tmp_1329708238-sitename.sql

Hm. So. The MySQL dump fails, adds a syntax error to the archive *.sql file, and restores from that archive are incomplete.

This is my fault for extending the global list of tables-to-ignore which triggers an error of the form of "I can't ignore that table because it doesn't exist".

Waiter, I'd like a cup of coffee, please, with no cream.
I'm sorry, sir, but we're out of cream. How about with no milk?

So...

Options that I see are:

  • Look-ahead and ensure the table exists before trying to not dump it - which is overly fiddly
  • Go back to having per-db lists of exclusions - which seems cumbersome, tedious and error-prone
  • Change what I'm doing because I don't understand how to use the global list of $options['structure-tables'] correctly. - most likely, and that's where I'm hoping for a glimpse of insight!
  • Add --force to the mysqldump command - which WORKS cleanly as I want ... but may have unforseen side-effects. Cheap and nasty, but works.

Any suggestions on option 3 ? I think I've missed a trick here.

Comments

dman’s picture

dman CreditAttribution: dman commented

I did search earlier, and see similarity with #1268850: Drush needs to re-create skipped structure tables when using --structure-tables but can't see the solution in the response there

greg.1.anderson’s picture

greg.1.anderson CreditAttribution: greg.1.anderson commented

Yeah, this is a problem with the way mysqldump works; unfortunately, drush does not help you very much here.

There is a related issue here: #698264: Better handling of structure-tables and skip-tables options (including cache_* support!). Perhaps this does not do everything you want, but it is along the same lines.

The --force option could be used if we had #518184: Allow Users To Set myqldump Arguments In drushrc.php (for drush sql dump). Really should have committed that a long time ago, but I mostly use postgres, and there hasn't seemed to be too many people trying to push that one forward.

The "drush way" to handle this would be to define your structure tables on a site-by-site bases, in your alias records. You could have a default definition in a drushrc.php file, and override it where necessary in individual aliases. Unfortunately, each alias must re-list the whole list of tables to exclude; there is no "merge" option. (You can always do the merge yourself, though -- since the site alias file is just a PHP file, you could fetch the default list from a location of your own chosing.)

dman’s picture

dman CreditAttribution: dman commented
Status: Active » Postponed

Thanks for the quick reponse!
Yeah I also found #698264: Better handling of structure-tables and skip-tables options (including cache_* support!) in searching, but it looked like it had a way to go there.

#518184: Allow Users To Set myqldump Arguments In drushrc.php (for drush sql dump) is a good move in general, so I'll follow that, but I'm still nervous about what else just 'forcing' things may affect.

Your suggestion of per-site merges is what I was afraid of. I'm trying to provide shortcuts to a team so we can have best-practices "just happen". Someone already tried putting php cleverness into alias files, but it started diverging from documented ways pretty quick and turn into copy&paste - so I'm hoping to pull back on that.
My devs should be able to enable modules and carry on without tweaking the site-alias files.

In theory, sites should have enough context information available to them to sort it out for themselves, but extracting that info is tedious. To do it possibly I'd extend settings to have a grey-list of "cache/temp tables we don't really care about if they exist" and build it all dynamically - but I feel that will mean bootstrapping the site before we know what to filter, and the sql-dump routine currently doesn't need to do that. ... just brainstorming here.

Looks like I may have to pull back on my planned efficiencies a little. Syncing unwanted tables is not harmful, just bulky.
Thanks for clarifying that I'm not just missing something in the expected usage of the tool though.

I'll make do in the meantime.

greg.1.anderson’s picture

greg.1.anderson CreditAttribution: greg.1.anderson commented
Status: Postponed » Closed (duplicate)

I added a pointer to this issue in #698264: Better handling of structure-tables and skip-tables options (including cache_* support!), so I am going to close this as a duplicate of that issue.

Wim Leers’s picture

Wim Leers
Location Ghent 🇧🇪🇪🇺
CreditAttribution: Wim Leers commented
Status: Closed (duplicate) » Active

I'm running into this too today. However… mysqldump doesn't bail on me. I'm not using --force. My mysqldump version:

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.9, for osx10.4 (i386)

Of course, this is kind of risky to rely unless you know all your mysqldump instances work this way. But I agree, #698264: Better handling of structure-tables and skip-tables options (including cache_* support!) (and specifically #698264-26: Better handling of structure-tables and skip-tables options (including cache_* support!)) should make this more elegant & reliable.

Wim Leers’s picture

Wim Leers
Location Ghent 🇧🇪🇪🇺
CreditAttribution: Wim Leers commented

On my server, this works just fine too. Same version:

mysqldump --version
mysqldump  Ver 10.13 Distrib 5.1.49, for debian-linux-gnu (x86_64)
greg.1.anderson’s picture

greg.1.anderson CreditAttribution: greg.1.anderson commented
Status: Active » Closed (duplicate)

Good to know this works better on newer versions of mysql. I'm still going to re-close this as a dup of #698264: Better handling of structure-tables and skip-tables options (including cache_* support!), though.

dman’s picture

dman CreditAttribution: dman commented

FTR, I was going
FROM: mysqldump Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (i486)
TO: mysqldump Ver 10.13 Distrib 5.1.49, for debian-linux-gnu (i486)

It's the old From one that we are moving/migrating away from (can't upgrade), and was throwing the complaints. So yeah, looks like newer is nicer.

dman’s picture

dman CreditAttribution: dman commented

As a follow-up if anyone finds this issue ongoing.
Using two sets of structure tables, and switching between them per site thanks to an option in the site-alias array seems like a solid work-around.

My global, shared, drushrc.php has

$options['structure-tables'] = array(
 'common' => array('cache', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog'),
 'common-with-search' => array('cache', 'cache_filter', 'cache_menu', 'cache_page', 'history', 'sessions', 'watchdog', 'search_index', 'search_dataset'),
);
$options['structure-tables-key']='common';

And usually we get the basic set of exclusions used.

next,
In my site-alias config for sites that DO have search on, we switch to the second set of exclusions.

$aliases['client.live'] = array (
  // ... normal site-alias configs
  'command-specific' => array(
    'sql-sync' => array(
      'structure-tables-key' => 'common-with-search',
    ),
  ),
);

and now when syncing THAT SITE, the list that also includes search exclusions is used.

This is probably straightforward & obvious to folk who use the advanced features of site-alias arrays a lot, but I thought I'd document it here specifically.
It's a pretty good example of using 'command-specific' options in the general case.

dman’s picture

dman CreditAttribution: dman commented
Issue summary: View changes

link to mysqldump --force