Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.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 ..
| Comment | File | Size | Author |
|---|---|---|---|
| #7 | 2120973_migrate_example_join_7.patch | 2.27 KB | manitoba22 |
| #4 | 2120973_migrate_example_join_4.patch | 2.38 KB | manitoba22 |
| #1 | 2120973_migrate_example_join_1.patch | 2.41 KB | manitoba22 |
Comments
Comment #1
manitoba22 CreditAttribution: manitoba22 commentedComment #2
manitoba22 CreditAttribution: manitoba22 commentedComment #4
manitoba22 CreditAttribution: manitoba22 commentedComment #5
manitoba22 CreditAttribution: manitoba22 commentedComment #7
manitoba22 CreditAttribution: manitoba22 commentedComment #8
manitoba22 CreditAttribution: manitoba22 commentedBeerNodeMigration 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.
Comment #8.0
manitoba22 CreditAttribution: manitoba22 commentedUpdated issue summary.
Comment #9
mikeryanCan 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).
Comment #10
manitoba22 CreditAttribution: manitoba22 commentedThe 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.
Comment #11
mikeryanWhen 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.
Comment #12
mikeryan