All migrations fail when using Postgresql on Centos 6, with a "feature not supported" error on all database transactions when trying to migrate Drupal 7.43 to Drupal 8.1.2. (Both Drush and UI fail the same way.)

From error log:
Migration failed with source plugin exception: SQLSTATE[0A000]: Feature not supported: 7 ERROR: cross-database references are not implemented: "d8.public.migrate_map_d7_url_alias" LINE 4: LEFT OUTER JOIN d8.public.migrate_map_d7_url_alias m... ^: SELECT ua.*, map.sourceid1 AS migrate_map_sourceid1, map.source_row_status AS migrate_map_source_row_status FROM @url_alias ua LEFT OUTER JOIN d8.public.migrate_map_d7_url_alias map ON pid = map.sourceid1 WHERE ( (map.sourceid1 IS NULL ) OR (map.source_row_status = :db_condition_placeholder_0) ); Array ( [:db_condition_placeholder_0] => 1 )

Centos 6.8, Postgres 9.5.3, php 5.6.22. Same error for all tables in the migration.

CommentFileSizeAuthor
#12 sqlbase_mapjoinable-2742953-9-test-only.patch1.28 KBmikeryan
#9 sqlbase_mapjoinable-2742953-9.patch2.16 KBjofitz
#9 interdiff-7-9.txt1.27 KBjofitz
#7 sqlbase_mapjoinable-2742953-7.patch908 bytesAda Hernandez
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

bperet created an issue. See original summary.

dimr’s picture

dimr CreditAttribution: dimr commented

Hi bperet,

did you solve it the problem?

I have the same error using SUSE Linux Enterprise Server 12 SP1 (x86_64), psql (PostgreSQL) 9.4.9 and PHP 7.0.14.

Thanks in advance

bzrudi71’s picture

bzrudi71 CreditAttribution: bzrudi71 commented
Issue tags: +PostgreSQL

This should not happen :(
Adding PostgreSQL tag to more attention.

dimr’s picture

dimr CreditAttribution: dimr commented
Issue tags: +migrate-d7-d8, +migration
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Title: Migration failed with source plugin exception: Feature not supported: cross-database references are not implemented » SqlBase::mapJoinable() should not join across databases with PostgreSQL
Issue tags: -migrate-d7-d8, -migration

SqlBase::mapJoinable() attempts to optimize by joining the source query (in the source database) to the map table (in the D8 database). PostgreSQL allows joins across schemas in the same database, but not across databases, so mapJoinable() should return FALSE if the driver is PostgreSQL and the databases do not match.

Workarounds for now:

  1. Put the source data and the D8 data in different schemas in the same database.
  2. Set ignore_map: true in the source configuration of your migration.
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Issue tags: +Novice

Novice for the actual fix - tests will be most definitely not be novice.

Ada Hernandez’s picture

Ada Hernandez CreditAttribution: Ada Hernandez at MTech, LLC commented
Version: 8.1.2 » 8.4.x-dev
Status: Active » Needs review
FileSize
sqlbase_mapjoinable-2742953-7.patch908 bytes

I've added a patch with the postgresql driver for actual version

Status: Needs review » Needs work

The last submitted patch, 7: sqlbase_mapjoinable-2742953-7.patch, failed testing.

jofitz’s picture

jofitz CreditAttribution: jofitz at ComputerMinds commented
Status: Needs work » Needs review
FileSize
interdiff-7-9.txt1.27 KB
sqlbase_mapjoinable-2742953-9.patch2.16 KB

I've done a little work on the test, but PostgreSQL is on the edge of my knowledge so will require checking.

heddn’s picture

heddn
Primary language English
Location Nicaragua
CreditAttribution: heddn at MTech, LLC commented
Issue tags: -Novice

The novice aspects are completed. Cleaning up tags.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Assigned: Unassigned » mikeryan
mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
FileSize
sqlbase_mapjoinable-2742953-9-test-only.patch1.28 KB

Uploading test-only patch, to verify the test is triggering the original problem here.

Status: Needs review » Needs work

The last submitted patch, 12: sqlbase_mapjoinable-2742953-9-test-only.patch, failed testing.

mikeryan’s picture

mikeryan
he/him
Primary language English
Location Murphysboro, IL, USA
CreditAttribution: mikeryan as a volunteer and at Virtuoso Performance commented
Assigned: mikeryan » Unassigned
Status: Needs work » Reviewed & tested by the community

I think this is OK. Note that the test is testing if mapJoinable() is reflecting the db array when it specified PostgreSQL, so it tests the same thing no matter what db engine the test is actually running under. I think this is OK, but the core committer may want to kick it back if they want to see a test that succeeds under MySQL and fails under PostgreSQL, which is more akin to the problem as seen in the wild.

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
CreditAttribution: alexpott at Chapter Three commented
Status: Reviewed & tested by the community » Fixed

Committed and pushed 3441b94 to 8.4.x and 49e94bdf0 to 8.3.x. Thanks!

Let's get this in for now. But I think we should file a follow-up. Somehow we need to ask the database driver rather than hardcoding stuff here. Since this is just not going to work for non-core provided db drivers.

  • alexpott committed 3441b94 on 8.4.x 
    Issue #2742953 by Jo Fitzgerald, mikeryan, Adita: SqlBase::mapJoinable...

  • alexpott committed 49e94bd on 8.3.x 
    Issue #2742953 by Jo Fitzgerald, mikeryan, Adita: SqlBase::mapJoinable...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.