Drush site upgrade doesn't support shared database tables (db_prefix w/ array values)

Could you show the output for drush status and drush sql-conf for both your source and target sites? Showing your database records from settings.php would also be helpful, if sql-conf does not print them with full fidelity.

Yes; it appears that Drush in general, and Drush Site Upgrade in particular do not support "array" values for db_prefix. Patches welcome, although fixes are likely to span both drush_sup and drush projects.

I think the problem here is mysqli and not database prefixing. I'm getting a very similar error but am not using shared tables. Here's my error:

You will destroy data from lchc_d7 and replace with data from lchc_drupal.

You might want to make a backup first, using the sql-dump command.

Do you really want to continue? (y/n): y
The syntax of the command is incorrect.
Table 'lchc_d7.access' doesn't exist                    [warning]

query: SELECT 1 FROM access WHERE type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0,
1 database.mysqli.inc:134
Table 'lchc_d7.cache' doesn't exist                     [warning]

query: SELECT data, created, headers, expire, serialized FROM cache
WHERE cid = 'variables' database.mysqli.inc:134
Table 'lchc_d7.variable' doesn't exist                  [warning]

query: SELECT * FROM variable database.mysqli.inc:134
Table 'lchc_d7.cache' doesn't exist                     [warning]

query: UPDATE cache SET data = '', created = 1342477674,
expire = 0, headers = '', serialized = 0 WHERE cid =
'variables' database.mysqli.inc:134
Table 'lchc_d7.system' doesn't exist                    [warning]

query: SELECT name, filename, throttle FROM system WHERE type =
'module' AND status = 1 AND bootstrap = 1 ORDER BY weight
ASC, filename ASC database.mysqli.inc:134
Table 'lchc_d7.url_alias' doesn't exist                 [warning]

query: SELECT COUNT(pid) FROM url_alias database.mysqli.inc:134
Table 'lchc_d7.system' doesn't exist          [error]

query: SELECT name, filename, throttle FROM system WHERE type =
'module' AND status = 1 ORDER BY weight ASC,
filename ASC in C:\Users\Kelley\Stash\Web\LCHC.d6\includes\module.inc
on line 63.
Table 'lchc_d7.system' doesn't exist                    [warning]

query: SELECT * FROM system WHERE type = 'theme'
database.mysqli.inc:134
Table 'lchc_d7.cache' doesn't exist                     [warning]

query: SELECT data, created, headers, expire, serialized FROM cache
WHERE cid = 'theme_registry:' database.mysqli.inc:134
Table 'lchc_d7.cache' doesn't exist                     [warning]

query: UPDATE cache SET data = 'a:0:{}', created =
1342477674, expire = 0, headers = '', serialized = 1 WHERE
cid = 'theme_registry:' database.mysqli.inc:134

These are all the same tables generating errors for Oleksa, with the addition of theme_registry. I also got that "The syntax of the command is incorrect." error at the beginning.

Since I wasn't using prefixing, I tried to figure out what else our situations had in common, and noticed that you were also using mysqli. When I changed my settings.php in both the source and destination sites to $db_url = 'mysql:' [etc], the process made it past step 12-a without errors.

Note: I modified the issue name but left the original included because I know this is linked from the project page, and it's of course possible we have similar errors with different causes. It sounded as if Oleska fixed his/her error by removing the db prefix, so let me know if you want me to file a separate issue.

I think that #4 is a completely different issue with similar error messages.

Can you use sql-sync to copy your database from one copy of the site to the other? site-upgrade is just calling through to Drush sql-sync here; if there is a problem with mysqli, it should be fixed in Drush.

Sorry to compound two different issues. I'll try a sql-sync test using mysqli later — if that fails should I file a Drush issue?

Yes, please -- but search the issue queue for duplicates first, this may have come up before.

In my case drush sql-sync works perfectly. I am having problems when I try the site-upgrade:

drush site-upgrade @mysite @onward
Table 'rollthew_drupal.roll_access' doesn't exist [warning]
query: SELECT 1 FROM roll_access WHERE type = 'host' AND LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1 database.mysqli.inc:134
Table 'rollthew_drupal.roll_cache' doesn't exist [warning]
query: SELECT data, created, headers, expire, serialized FROM roll_cache WHERE cid = 'variables' database.mysqli.inc:134
Table 'rollthew_drupal.roll_variable' doesn't exist [warning]
query: SELECT * FROM roll_variable database.mysqli.inc:134
Table 'rollthew_drupal.roll_cache' doesn't exist [warning]
query: UPDATE roll_cache SET data = '', created = 1363958174, expire = 0, headers = '', serialized = 0 WHERE cid = 'variables'
database.mysqli.inc:134
Table 'rollthew_drupal.roll_system' doesn't exist [warning]
query: SELECT name, filename, throttle FROM roll_system WHERE type = 'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename ASC
database.mysqli.inc:134
Table 'rollthew_drupal.roll_url_alias' doesn't exist [warning]
query: SELECT COUNT(pid) FROM roll_url_alias database.mysqli.inc:134
Table 'rollthew_drupal.roll_system' doesn't exist [error]
query: SELECT name, filename, throttle FROM roll_system WHERE type = 'module' AND status = 1 ORDER BY weight ASC, filename ASC in
/Applications/MAMP/htdocs/includes/module.inc on line 63.
Table 'rollthew_drupal.roll_system' doesn't exist [warning]
query: SELECT * FROM roll_system WHERE type = 'theme' database.mysqli.inc:134
Table 'rollthew_drupal.roll_cache' doesn't exist [warning]
query: SELECT data, created, headers, expire, serialized FROM roll_cache WHERE cid = 'theme_registry:' database.mysqli.inc:134
Table 'rollthew_drupal.roll_cache' doesn't exist [warning]
query: UPDATE roll_cache SET data = 'a:0:{}', created = 1363958174, expire = 0, headers = '', serialized = 1 WHERE cid =
'theme_registry:' database.mysqli.inc:134
Command site-upgrade needs the following modules installed/enabled to run: update. [error]
The drush command 'site-upgrade @mysite @onward' could not be executed.

I think you've run into the same limitation as #2. Note that I have upgraded a single site with a database prefix, so I don't think that is the full extent of the problem, but it is related.

Patches welcome, and sorry for the inconvenience.

I was getting errors like:

Table 'wcadv7db.Arrayaccess' doesn't exist                        [warning]
query: SELECT 1 FROM Arrayaccess WHERE type = 'host' AND
LOWER('127.0.0.1') LIKE LOWER(mask) AND status = 0 LIMIT 0, 1
database.mysql.inc:135
Table 'wcadv7db.Arraycache' doesn't exist                         [warning]
query: SELECT data, created, headers, expire, serialized FROM Arraycache WHERE
cid = 'variables' database.mysql.inc:135
Table 'wcadv7db.Arrayvariable' doesn't exist                      [warning]
query: SELECT * FROM Arrayvariable database.mysql.inc:135
Table 'wcadv7db.Arraycache' doesn't exist                         [warning]
query: UPDATE Arraycache SET data = '', created = 1364507456, expire =
0, headers = '', serialized = 0 WHERE cid = 'variables'
database.mysql.inc:135
Table 'wcadv7db.Arraysystem' doesn't exist                        [warning]
query: SELECT name, filename, throttle FROM Arraysystem WHERE type =
'module' AND status = 1 AND bootstrap = 1 ORDER BY weight ASC, filename
ASC database.mysql.inc:135
Table 'wcadv7db.Arrayurl_alias' doesn't exist                     [warning]
query: SELECT COUNT(pid) FROM Arrayurl_alias database.mysql.inc:135
Table 'wcadv7db.Arraysystem' doesn't exist            [error]
query: SELECT name, filename, throttle FROM Arraysystem WHERE type =
'module' AND status = 1 ORDER BY weight ASC, filename ASC in
/home/wcadvlists/wcadvlists.org/includes/module.inc on line 63.

In my settings.php my $db_prefix value started like this:

$db_prefix = array(
  'civicrm_acl'                              => 'wcadv_civicrm.',
  'civicrm_acl_cache'                        => 'wcadv_civicrm.',
  'civicrm_acl_contact_cache'                => 'wcadv_civicrm.',
  'civicrm_acl_entity_role'                  => 'wcadv_civicrm.',
  'civicrm_activity'                         => 'wcadv_civicrm.',
  'civicrm_activity_assignment'              => 'wcadv_civicrm.',
  'civicrm_activity_target'                  => 'wcadv_civicrm.',
. . . 

The attached patch allows the upgrade to go forward and logs an error. The error does not show up on the screen however suggesting I should not be using drush_log.

Until array-style db prefix records are supported, it would be better to halt the upgrade via return drush_set_error(...);. The upgrade does not work correctly when the $db_prefix is an array, does it?

As for your drush_log not showing up, the default log type is 'notice', which does not show up by default (need to be running in --debug or --verbose mode to see those). When printing log messages that are not fatal, use the 'warning' log type, which is always displayed.

In my case, this works as well as I expect. All the entries in db_prefix were related to CiviCRM and so they did not affect the upgrade. I guess in most cases, people would not be so lucky.

I did not expect site-upgrade to also upgrade CiviCRM - although maybe it could chain to the upgrade tools the CiviCRM folks have built into drush. But actually a CiviCRM upgrade is such an involved and fragile process I would not recommend it even if theoretically possible.

So maybe you are correct. On finding an array, it should just stop and tell the human to fix it. I did much of my migration after backing up settings.php and deleting $db_prefix. But then I got thinking about where Array came from and that led to this simplistic patch.

So I agree, while in my case, this behavior was better, in the general case, it would be better to stop the upgrade with a message. I do think providing an alternative to abandoning Site Upgrade would be valuable. Maybe something like:

The $db_prefix variable (likely in settings.php) is an array.  Site Upgrade can only upgrade sites where $db_prefix is not set or is set as a string.  Removing $db_prefix may allow Site Upgrade to proceed.  The prefixed tables will be copied to the new database, but data in the prefixed tables will generally not be upgraded. 

The message in #12 looks good. It is unlikely that I will ever have time to support $db_prefix here, but would certainly accept patches from anyone who wanted to tackle it.

code tag added