Drush needs to re-create skipped structure tables when using --structure-tables

yes, thats correct. structure tables are created but no data is put in them. i don't really understand the request here.

Is the issue here related to what to do if a table is in both skipped and structure-only?

It almost sounds to me as if the request were the opposite of what was stated:

re-set the database to try it out again... the table for that field exists and the site explodes trying to create it again.

If that is the case, then what is desired is for the sql-sync to erase the table that exists in the destination, but not in the source. Structure tables will not do that, but #716412: add a --delete-all-tables-first option for sql-sync ? will.

Maybe close as duplicate?

I just setup two sites and did your experiment and it works fine. The @example.local sites works and has a sessions table.

If you run with --verbose, you should see the key bash statement. It is actually two mysqldump calls where the last one appends to the first one. The second one runs with --no-data option. Note the semicolon in the middle of code below:

mysqldump --result-file /private/tmp/d7.sql.1ez2B4 --single-transaction --opt -Q  d7 --host=localhost --user=root --password= --skip-extended-insert --order-by-primary --ignore-table=d7.cache --ignore-table=d7.cache_filter --ignore-table=d7.cache_menu --ignore-table=d7.cache_page --ignore-table=d7.history --ignore-table=d7.sessions --ignore-table=d7.watchdog; mysqldump --no-data  --single-transaction --opt -Q  d7 --host=localhost --user=root --password= --skip-extended-insert --order-by-primary cache cache_filter cache_menu cache_page history sessions watchdog >> /private/tmp/d7.sql.1ez2B4

Please reopen if you can find more info.

I just got hit by the same issue. If you are using --structure-tables-key with a sql-dump, the effect is just the same as if you issue a --skip-tables-key, which is to say that neither the data nor the schema for the tables specified are present in the sql dump.

Any chance you guys are running this on WIndows? I think it does not recognize semicolon as a delimiter between statements (see #5 ).

We changed those semicolons to ampsersands so hopefully this is fixed in master (and 4.x). Please reopen as needed, with a --debug log.

I've come across this bug but I think problem is when non-existent tables are present in the structure tables array.

In my case I wanted to create a drushrc.php file with all common cache tables added to structure table setting (including some tipical tables from common modules). So I have:

options['structure-tables']['common'] = array('cache', 'cache_form', 'cache_views', 'cache_block', 'cache_bootstrap', 'cache_content', 'cache_field', 'cache_filter', 'cache_image', 'cache_menu', 'cache_page', 'cache_path', 'cache_token', 'cache_variable', 'cache_views_data', 'cache_hierarchical_select', 'cache_rules', 'sessions');

Then, dumping and filtering sql by CREATE TABLE statements I got:

$ drush-dev --debug sql-dump --structure-tables-key=common |grep "CREATE TABLE"| grep cache
Bootstrap to phase 0. [0 sec, 2.49 MB]                               [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 2.67 MB] [bootstrap]
Loading drushrc "/etc/drush/drushrc.php" into "system" scope. [0.01 sec, 2.67 MB]                     [bootstrap]
Include /etc/drush/custom_files [0.01 sec, 2.68 MB]                                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-0-6c41debe3c090b1605904464797932cc [0.02 sec, 2.69 MB]                [debug]
Bootstrap to phase 0. [0.06 sec, 5.8 MB]                                                              [bootstrap]
Bootstrap to phase 0. [0.07 sec, 5.8 MB]                                                              [bootstrap]
Found command: sql-dump (commandfile=sql) [0.07 sec, 5.8 MB]                                          [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [0.11 sec, 5.84 MB]                            [bootstrap]
Initialized Drupal 7.9 root directory at /var/vhosts/icomem [0.14 sec, 8.39 MB]                          [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [0.14 sec, 8.4 MB]                             [bootstrap]
Initialized Drupal site default at sites/default [0.14 sec, 8.4 MB]                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-2-1c82782e4f0daa661b06df029df9c6f1 [0.15 sec, 8.41 MB]                [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [0.15 sec, 8.7 MB]                    [bootstrap]
Cache HIT cid: 5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f [0.15 sec, 8.71 MB]                [debug]
CREATE TABLE `cache_admin_menu` (
CREATE TABLE `cache_update` (
CREATE TABLE `ctools_css_cache` (
CREATE TABLE `ctools_object_cache` (
mysqldump: Couldn't find table: "cache_content"
Database dump failed [1.87 sec, 8.71 MB]                                                              [error]
Command dispatch complete [1.87 sec, 8.68 MB]                                                            [notice]
 Timer  Cum (sec)  Count  Avg (msec) 
 page   1.718      1      1718.31    

Peak memory usage was 9.7 MB [1.87 sec, 8.68 MB]  

(drush-dev is just an alias to drush 5.x-dev)

As you can see, no tables from my structure tables array are created.

But, if I comment that line in drushrc.php I got:

$ drush-dev --debug sql-dump --structure-tables-key=common |grep "CREATE TABLE"| grep cache
Bootstrap to phase 0. [0 sec, 2.49 MB]                               [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 2.67 MB] [bootstrap]
Loading drushrc "/etc/drush/drushrc.php" into "system" scope. [0.01 sec, 2.67 MB]                     [bootstrap]
Include /etc/drush/custom_files [0.01 sec, 2.68 MB]                                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-0-6c41debe3c090b1605904464797932cc [0.02 sec, 2.69 MB]                [debug]
Bootstrap to phase 0. [0.06 sec, 5.79 MB]                                                             [bootstrap]
Bootstrap to phase 0. [0.07 sec, 5.8 MB]                                                              [bootstrap]
Found command: sql-dump (commandfile=sql) [0.07 sec, 5.8 MB]                                          [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [0.11 sec, 5.84 MB]                            [bootstrap]
Initialized Drupal 7.9 root directory at /var/vhosts/icomem [0.14 sec, 8.39 MB]                          [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [0.14 sec, 8.4 MB]                             [bootstrap]
Initialized Drupal site default at sites/default [0.14 sec, 8.4 MB]                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-2-1c82782e4f0daa661b06df029df9c6f1 [0.15 sec, 8.4 MB]                 [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [0.15 sec, 8.7 MB]                    [bootstrap]
Cache HIT cid: 5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f [0.15 sec, 8.71 MB]                [debug]
CREATE TABLE `cache` (
CREATE TABLE `cache_admin_menu` (
CREATE TABLE `cache_block` (
CREATE TABLE `cache_bootstrap` (
CREATE TABLE `cache_field` (
CREATE TABLE `cache_filter` (
CREATE TABLE `cache_form` (
CREATE TABLE `cache_image` (
CREATE TABLE `cache_menu` (
CREATE TABLE `cache_page` (
CREATE TABLE `cache_path` (
CREATE TABLE `cache_token` (
CREATE TABLE `cache_update` (
CREATE TABLE `cache_views` (
CREATE TABLE `cache_views_data` (
CREATE TABLE `ctools_css_cache` (
CREATE TABLE `ctools_object_cache` (
Command dispatch complete [2.17 sec, 8.67 MB]                                                            [notice]
 Timer  Cum (sec)  Count  Avg (msec) 
 page   2.021      1      2021.34    

Peak memory usage was 9.73 MB [2.18 sec, 8.67 MB]  

Here they are the lost tables!

If I reduce my structure tables to tables present in database it works ok:

$options['structure-tables']['common'] = array('cache', 'cache_form', 'cache_views');

$ drush-dev --debug sql-dump --structure-tables-key=common |grep "CREATE TABLE"| grep cache
Bootstrap to phase 0. [0 sec, 2.49 MB]                               [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drush() [0.01 sec, 2.67 MB] [bootstrap]
Loading drushrc "/etc/drush/drushrc.php" into "system" scope. [0.01 sec, 2.67 MB]                     [bootstrap]
Include /etc/drush/custom_files [0.02 sec, 2.68 MB]                                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-0-6c41debe3c090b1605904464797932cc [0.02 sec, 2.69 MB]                [debug]
Bootstrap to phase 0. [0.06 sec, 5.79 MB]                                                             [bootstrap]
Bootstrap to phase 0. [0.08 sec, 5.8 MB]                                                              [bootstrap]
Found command: sql-dump (commandfile=sql) [0.08 sec, 5.8 MB]                                          [bootstrap]
Drush bootstrap phase : _drush_bootstrap_drupal_root() [0.13 sec, 5.84 MB]                            [bootstrap]
Initialized Drupal 7.9 root directory at /var/vhosts/icomem [0.16 sec, 8.39 MB]                          [notice]
Drush bootstrap phase : _drush_bootstrap_drupal_site() [0.17 sec, 8.4 MB]                             [bootstrap]
Initialized Drupal site default at sites/default [0.17 sec, 8.4 MB]                                      [notice]
Cache HIT cid: 5.0-dev-commandfiles-2-1c82782e4f0daa661b06df029df9c6f1 [0.17 sec, 8.4 MB]                 [debug]
Drush bootstrap phase : _drush_bootstrap_drupal_configuration() [0.18 sec, 8.7 MB]                    [bootstrap]
Cache HIT cid: 5.0-dev-commandfiles-3-d970728376beb137057865c63baa822f [0.18 sec, 8.71 MB]                [debug]
CREATE TABLE `cache_admin_menu` (
CREATE TABLE `cache_block` (
CREATE TABLE `cache_bootstrap` (
CREATE TABLE `cache_field` (
CREATE TABLE `cache_filter` (
CREATE TABLE `cache_image` (
CREATE TABLE `cache_menu` (
CREATE TABLE `cache_page` (
CREATE TABLE `cache_path` (
CREATE TABLE `cache_token` (
CREATE TABLE `cache_update` (
CREATE TABLE `cache_views_data` (
CREATE TABLE `ctools_css_cache` (
CREATE TABLE `ctools_object_cache` (
Command dispatch complete [2.07 sec, 8.67 MB]                                                            [notice]
 Timer  Cum (sec)  Count  Avg (msec) 
 page   1.891      1      1890.65    

Peak memory usage was 9.73 MB [2.07 sec, 8.67 MB]                                                        [memory]
CREATE TABLE `cache` (
CREATE TABLE `cache_form` (
CREATE TABLE `cache_views` (

All tables are present, although data skiped tables are shown at the end.

So, I think drush should refuse to create a dump file if a non-existent table is given in structured table setting or it should handle those non-existent tables in strcutured tables settings and dump present tables (without data).

I've tested with drush 4.x-dev and 5.x-dev just downloaded today. Same results with drush 4.x.

@tunic - that does sound like a bug but probably not the cause of everyone else's woes since the 'common' key contains always-present tables. anyway, the easiest fix here is to just error out and refuse to complete the sync since the dump is incomplete.

We actually had a todo in the code to fix #11. Committed to master and 4.x in commit 43003f6.

Sorry to reopen but this issue is still there on the 5.x version.

There is the extract of my drushrc file :

$options['structure-tables']['common'] = array(
  'cache', 'cache_block', 'cache_content', 'cache_filter', 'cache_form', 'cache_gc_map', 
  'cache_gc_smap', 'cache_geonames', 'cache_gmaps_geocode', 'cache_gmaps_map', 
  'cache_gmaps_smap', 'cache_gv_map', 'cache_gv_map_result', 'cache_gv_smap', 
  'cache_gv_smap_result', 'cache_location', 'cache_menu', 'cache_page', 
  'cache_path', 'cache_update', 'cache_views', 'cache_views_data', 
  'boost_cache', 'boost_cache_relationships', 'boost_cache_settings', 
  'boost_clearcache', 'boost_crawler', 'ctools_css_cache', 
  'ctools_object_cache', 'domain_2_cache_block', 'domain_2_cache_menu', 
  'domain_2_cache_views', 'domain_2_cache_views_data', 
  'domain_2_views_object_cache', 'flood', 'search_dataset', 'search_index', 
  'search_node_links', 'search_total', 'sessions', 'views_object_cache', 'watchdog', 
);

There is the generated mysqldump command :
mysqldump --result-file database_2013-02-07-12-57-02.sql --no-autocommit --single-transaction --opt -Q mydatabase --host=localhost --user=mydatabase --password=mypassword --skip-extended-insert --order-by-primary --ignore-table=mydatabase.cache --ignore-table=mydatabase.cache_block --ignore-table=mydatabase.cache_content --ignore-table=mydatabase.cache_filter --ignore-table=mydatabase.cache_form --ignore-table=mydatabase.cache_gc_map --ignore-table=mydatabase.cache_gc_smap --ignore-table=mydatabase.cache_geonames --ignore-table=mydatabase.cache_gmaps_geocode --ignore-table=mydatabase.cache_gmaps_map --ignore-table=mydatabase.cache_gmaps_smap --ignore-table=mydatabase.cache_gv_map --ignore-table=mydatabase.cache_gv_map_result --ignore-table=mydatabase.cache_gv_smap --ignore-table=mydatabase.cache_gv_smap_result --ignore-table=mydatabase.cache_location --ignore-table=mydatabase.cache_menu --ignore-table=mydatabase.cache_page --ignore-table=mydatabase.cache_path --ignore-table=mydatabase.cache_update --ignore-table=mydatabase.cache_views --ignore-table=mydatabase.cache_views_data --ignore-table=mydatabase.boost_cache --ignore-table=mydatabase.boost_cache_relationships --ignore-table=mydatabase.boost_cache_settings --ignore-table=mydatabase.boost_clearcache --ignore-table=mydatabase.boost_crawler --ignore-table=mydatabase.ctools_css_cache --ignore-table=mydatabase.ctools_object_cache --ignore-table=mydatabase.domain_2_cache_block --ignore-table=mydatabase.domain_2_cache_menu --ignore-table=mydatabase.domain_2_cache_views --ignore-table=mydatabase.domain_2_cache_views_data --ignore-table=mydatabase.domain_2_views_object_cache --ignore-table=mydatabase.flood --ignore-table=mydatabase.search_dataset --ignore-table=mydatabase.search_index --ignore-table=mydatabase.search_node_links --ignore-table=mydatabase.search_total --ignore-table=mydatabase.sessions --ignore-table=mydatabase.views_object_cache --ignore-table=mydatabase.watchdog && mysqldump --no-data --no-autocommit --single-transaction --opt -Q mydatabase --host=localhost --user=mydatabase --password=mypassword --skip-extended-insert --order-by-primary cache cache_block cache_content cache_filter cache_form cache_gc_map cache_gc_smap cache_geonames cache_gmaps_geocode cache_gmaps_map cache_gmaps_smap cache_gv_map cache_gv_map_result cache_gv_smap cache_gv_smap_result cache_location cache_menu cache_page cache_path cache_update cache_views cache_views_data boost_cache boost_cache_relationships boost_cache_settings boost_clearcache boost_crawler ctools_css_cache ctools_object_cache domain_2_cache_block domain_2_cache_menu domain_2_cache_views domain_2_cache_views_data domain_2_views_object_cache flood search_dataset search_index search_node_links search_total sessions views_object_cache watchdog >> database_2013-02-07-12-57-02.sql

Then finally there are the results :

$ grep "CREATE TABLE \`cache" database_2013-02-07-12-57-02.sql
CREATE TABLE `cache_mollom` (

$ grep "CREATE TABLE \`session" database_2013-02-07-12-57-02.sql

$ 

I am using the latest drush 5.x dev version under windows 7 and I get no warning or error during the process.

I made a test by running the two mysqldump commands separately like this

mysqldump --result-file tmpdump.sql --no-autocommit --single-transaction --opt -Q mydatabase --host=localhost --user=mydatabase --password=mypassword --skip-extended-insert --order-by-primary --ignore-table=mydatabase.cache --ignore-table=mydatabase.cache_block --ignore-table=mydatabase.cache_content --ignore-table=mydatabase.cache_filter --ignore-table=mydatabase.cache_form --ignore-table=mydatabase.cache_gc_map --ignore-table=mydatabase.cache_gc_smap --ignore-table=mydatabase.cache_geonames --ignore-table=mydatabase.cache_gmaps_geocode --ignore-table=mydatabase.cache_gmaps_map --ignore-table=mydatabase.cache_gmaps_smap --ignore-table=mydatabase.cache_gv_map --ignore-table=mydatabase.cache_gv_map_result --ignore-table=mydatabase.cache_gv_smap --ignore-table=mydatabase.cache_gv_smap_result --ignore-table=mydatabase.cache_location --ignore-table=mydatabase.cache_menu --ignore-table=mydatabase.cache_page --ignore-table=mydatabase.cache_path --ignore-table=mydatabase.cache_update --ignore-table=mydatabase.cache_views --ignore-table=mydatabase.cache_views_data --ignore-table=mydatabase.boost_cache --ignore-table=mydatabase.boost_cache_relationships --ignore-table=mydatabase.boost_cache_settings --ignore-table=mydatabase.boost_clearcache --ignore-table=mydatabase.boost_crawler --ignore-table=mydatabase.ctools_css_cache --ignore-table=mydatabase.ctools_object_cache --ignore-table=mydatabase.domain_2_cache_block --ignore-table=mydatabase.domain_2_cache_menu --ignore-table=mydatabase.domain_2_cache_views --ignore-table=mydatabase.domain_2_cache_views_data --ignore-table=mydatabase.domain_2_views_object_cache --ignore-table=mydatabase.flood --ignore-table=mydatabase.search_dataset --ignore-table=mydatabase.search_index --ignore-table=mydatabase.search_node_links --ignore-table=mydatabase.search_total --ignore-table=mydatabase.sessions --ignore-table=mydatabase.views_object_cache --ignore-table=mydatabase.watchdog

THEN

mysqldump --no-data --no-autocommit --single-transaction --opt -Q mydatabase --host=localhost --user=mydatabase --password=mypassword --skip-extended-insert --order-by-primary cache cache_block cache_content cache_filter cache_form cache_gc_map cache_gc_smap cache_geonames cache_gmaps_geocode cache_gmaps_map cache_gmaps_smap cache_gv_map cache_gv_map_result cache_gv_smap cache_gv_smap_result cache_location cache_menu cache_page cache_path cache_update cache_views cache_views_data boost_cache boost_cache_relationships boost_cache_settings boost_clearcache boost_crawler ctools_css_cache ctools_object_cache domain_2_cache_block domain_2_cache_menu domain_2_cache_views domain_2_cache_views_data domain_2_views_object_cache flood search_dataset search_index search_node_links search_total sessions views_object_cache watchdog >> tmpdump.sql

And it works well.
I looked into drush to see if it was easily patchable but it is not so I hope you could find a good way to close this bug.

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

If desired, you may copy this bug to our Github project and then post a link here to the new issue. Please also change the status of this issue to closed (duplicate).

Please ask support questions on Drupal Answers.

Quite pathetic that support for a Drupal plugin cannot be had on Drupal.org.

Someone needs to fork Drush and bring it back to Drupal.org. This is ridiculous.

I'm not a fan of it either, but let's not pollute this old issue any further. If you feel strong about it please open a new issue in this or some d.o infra queue to figure out what we need to do to draw Drush back. I know semantic versioning was one of the reaons...