Due to #1721258: Source plugin exception with Postgres cross-schema joins, beer.inc does not work with a postgresql db:

source plugin exception: SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "drupal" does not exist LINE 4: LEFT OUTER JOIN ..

Files: 
CommentFileSizeAuthor
#7 2120973_migrate_example_join_7.patch2.27 KBmanitoba22
PASSED: [[SimpleTest]]: [MySQL] 148 pass(es).
[ View ]
#4 2120973_migrate_example_join_4.patch2.38 KBmanitoba22
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 2120973_migrate_example_join_4.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#1 2120973_migrate_example_join_1.patch2.41 KBmanitoba22
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in 2120973_migrate_example_join_1.patch.
[ View ]

Comments

manitoba22’s picture

StatusFileSize
new2.41 KB
FAILED: [[SimpleTest]]: [MySQL] Invalid patch format in 2120973_migrate_example_join_1.patch.
[ View ]
manitoba22’s picture

Status:Active» Needs review

Status:Needs review» Needs work

The last submitted patch, 2120973_migrate_example_join_1.patch, failed testing.

manitoba22’s picture

StatusFileSize
new2.38 KB
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 2120973_migrate_example_join_4.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
manitoba22’s picture

Status:Needs work» Needs review

Status:Needs review» Needs work

The last submitted patch, 2120973_migrate_example_join_4.patch, failed testing.

manitoba22’s picture

Status:Needs work» Needs review
StatusFileSize
new2.27 KB
PASSED: [[SimpleTest]]: [MySQL] 148 pass(es).
[ View ]
manitoba22’s picture

BeerNodeMigration still does rely on a "forbidden" join explicitly made (as the comment at line 327 indicates: "valid for MySQL only").

If this cannot be changed, there perhaps should be a prominent warning at the top of the file and in https://drupal.org/node/415260 (where the migrate_example* modules are introduced), that the beer example code works only in MySQL. This could avoid frustrating beginners with migrate who hope for a running example and happen to use another db system.

manitoba22’s picture

Issue summary:View changes

Updated issue summary.

mikeryan’s picture

Issue summary:View changes
Status:Needs review» Postponed (maintainer needs more info)

Can you explain a little why this doesn't work for you with Postgres? The data table is being joined with the map table using the standard Drupal Database API, would should work as well with Postgres as it does with MySQL (if it didn't, Drupal wouldn't work at all on Postgres).

manitoba22’s picture

The error SQLSTATE[3F000] occurs when trying to import Node via UI (import of User worked).

The Posgres version of my target database is 8.4.18 on Centos 6.4.

The critical point seems to be the cross-database join. Postgres limitations in this regard are cited in https://drupal.org/comment/6619050#comment-6619050 (with link to the original source). I don't think that this is a common situation for the Drupal Database API.

I'm not sure if this explains the situation better to you, or makes it reproduceble. Is there a Drupal function which lets me output or log the generated raw SQL query? Perhaps this could help better.

mikeryan’s picture

When adding information to an issue in the "Postponed (maintainer needs more info)" status, be sure to change the status to Active to maximize the chance of it being seen in the short term.

There should be no cross-database join here - in the examples, the source table and the map table are both in the current Drupal database. In the BeerNode migration, the GROUP_CONCAT definitely won't work in PostgreSQL (but that is documented).

The devel module has a dpq() function for printing the SQL query string corresponding to a query object. Also, simply casting a query object to (string) will give you the query string without the parameters substituted.

mikeryan’s picture

Status:Postponed (maintainer needs more info)» Closed (cannot reproduce)