Hi,
I'm getting this error message while trying to update from Drupal 6.26 to Drupal 7.15.
Failed: PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column "td" of relation "taxonomy_update_7005" does not exist LINE 1: ...tid, nid, vid, type, created, sticky, is_current, td.weight,... ^: INSERT INTO taxonomy_update_7005 (vocab_id, tid, nid, vid, type, created, sticky, is_current, td.weight, tn.tid) SELECT td.vid AS vocab_id, td.tid AS tid, tn.nid AS nid, tn.vid AS vid, n.type AS type, n2.created AS created, n2.sticky AS sticky, n2.nid AS is_current, td.weight AS tdweight, tn.tid AS tntid FROM taxonomy_term_data td INNER JOIN taxonomy_term_node tn ON td.tid = tn.tid INNER JOIN node n ON tn.nid = n.nid LEFT OUTER JOIN node n2 ON tn.vid = n2.vid ORDER BY tn.vid ASC, td.weight ASC, tn.tid ASC; Array ( ) in taxonomy_update_7005() (line 687 of /var/www/suivis7/modules/taxonomy/taxonomy.install).
Searching in the Drupal source code I found that SelectQuery_pgsql::orderBy add the order by field in the $fields property even if there's no distinct or group by clause. Then InsertQuery::preExecute uses the fromQuery fields to genereate the query, which contains the order by fields. I don't think order by fields should be added to the fields property but only added to the sql string in the __string() function. This way, it wouldn't make insert select queries with order by clauses to fail and it would be possible to add order by to fields only if distinct or group by is present.
Ubuntu 12.04 64bits
PHP 5.3.10-1ubuntu3.2
Pgsql 8.3
Apache 2.2.22
Thanks.
Comments
Comment #1
plachance CreditAttribution: plachance commentedHere's a dirty workaround to avoid the error in the updater (near line 682 in taxonomy,install) :
Comment #2
David_Rothstein CreditAttribution: David_Rothstein commentedThis sounds pretty bad - possibly caused by #1549390: taxonomy_update_7005() can be faster which was committed recently. (Do you know if updating to Drupal 7.14 has the problem too, or only updating to Drupal 7.15 or higher?) Although it sounds from your comment like that might just have triggered the bug and the root cause is actually in the database driver.
Comment #3
pgeorgi CreditAttribution: pgeorgi commentedThis "dirty workaround" helped me a bit, but then I ran into an issue with the "n" field, which looked similar to the issue in https://drupal.org/node/183125.
Changing n from "serial" to "int" additionally to the change above fixed this update for me.
Comment #4
christoph CreditAttribution: christoph commentedPerfect - thanks for this. It worked for me converting d-6.26 to d-7.15 (I had no taxonomies enabled at all on this small site). To clarify for anyone else looking using comment #3 I changed line 601 to read
and inserted this after line 684 (per comment #1)
Thank you for posting those - was a quick solution in the end.
Comment #5
Josh Waihi CreditAttribution: Josh Waihi commentedThis isn't tested but I think this will work. The problem is that the PostgreSQL driver adds additional fields to the SELECT query that are mentioned in the orderBy clause. So as mentioned above, removing these fields could be one approach.
The approach I've taken is to order by the tid column from the taxonomy_term_data table instead of the taxonomy_term_node table as its already being selected. To fix the weight column problem, I've added the weight column to the temporary table and also used it to sort the db_query_range on as 'n' doesn't seem like a strong sorting column.
Comment #7
rondp CreditAttribution: rondp commentedRan into the same issue with Drupal 7.17 and after applying the patches suggested in #4 got the error
SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "n" violates not-null constraint
So I reverted the first suggested change and only applied the second, and that did the trick.
Resulting patch attached.
Comment #8
webchickComment #10
rondp CreditAttribution: rondp commentedAs far as I can tell, the problem is this:
Output: [fatal: unable to find filename in patch at line 1].
I don't know what this testing tool expects in terms of filenames, but if someone can fill me in, or point me to the source of truth on that, I'd be happy to try and make the required changes.
Comment #11
webchickAh. The best way to make a patch against core is to clone Drupal from Git (see copy/paste instructions at http://drupal.org/node/3060/git-instructions/7.x), edit the file in place, then run "git diff > foo.patch" to generate the patch file. See also http://drupal.org/node/707484
Comment #12
rondp CreditAttribution: rondp commentedAh, ok; thanks for the instructions.
Seemed a bit over the top to clone 180,000 objects to create a patch for one file but here we go. :-)
Please find attached (also now following proper naming instructions).
Comment #13
PMunn CreditAttribution: PMunn commentedJust tried updating to 7 from 6.26 after not having tried a 7 build in quite a while with my pgsql install. This was the only error visible, so I'm looking forward to this getting resolved and rolled into a new point release so I can try again.
Thanks to everyone for your efforts!
Comment #14
rondp CreditAttribution: rondp commentedSo, how can I initiate a test of the new patch from #12, or does this have to be done by a mod?
Couldn't find anything about this in the referenced documentation.
Comment #15
BerdirJust needs to be set to needs review.
Comment #16
rondp CreditAttribution: rondp commentedGreat, thanks Berdir.
And it passed, too, which is nice.
Comment #17
acanthoweb CreditAttribution: acanthoweb commentedHI,
I had the same error so I have applied the patch #12. Now I'm getting the following error:
taxonomy module
Update #7005
Failed: PDOException: SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of sequence "taxonomy_update_7005_n_seq" is not yet defined in this session: INSERT INTO taxonomy_update_7005 (vocab_id, tid, nid, vid, type, created, sticky, is_current) SELECT td.vid AS vocab_id, td.tid AS tid, tn.nid AS nid, tn.vid AS vid, n.type AS type, n2.created AS created, n2.sticky AS sticky, n2.nid AS is_current FROM taxonomy_term_data td INNER JOIN taxonomy_term_node tn ON td.tid = tn.tid INNER JOIN node n ON tn.nid = n.nid LEFT OUTER JOIN node n2 ON tn.vid = n2.vid ORDER BY tn.vid ASC, td.weight ASC, tn.tid ASC; Array ( ) in taxonomy_update_7005() (line 690 of modules/taxonomy/taxonomy.install).
Many thanks for your help.
Comment #18
nevergone CreditAttribution: nevergone commentedPatch apply Drupal 7.x latest dev, run database update and error message:
SQLSTATE[55000]: Object not in prerequisite state: 7 ERROR: currval of sequence "drupal_taxonomy_update_7005_n_seq" is not yet defined in this session
Comment #19
nevergone CreditAttribution: nevergone commentedComment #20
jweowu CreditAttribution: jweowu commented#12 works fine for me, but the error in #17/#18 also necessitates #1993166: Postgres: db_insert()->from($query) with zero rows causes error with default options.. Because these two patches conflict, I'm attaching a version of #12 which applies on top of the patch in comment #3 of that issue.
However, if this latter error is happening to you, then chances are fair that you're not actually using the taxonomy module at all, in which case you could just disable and uninstall it in Drupal 6 before upgrading -- there's no point in wasting server resources on modules which aren't needed.
Comment #21
jweowu CreditAttribution: jweowu commentedAnd as #12 works just fine -- RTBC.
Comment #22
jweowu CreditAttribution: jweowu commentedI'll add a caveat, which is that unset()ting parts of the query like that evidentially isn't a good pattern for general use. My initial attempt to circumvent #17/18 (after applying #12) was to use
$query->countQuery()
to establish if there were any results before attempting the insert, but that caused nasty interactions between the countQuery and the object it was cloned from. I had generated the countQuery after all the field manipulations, and while it worked fine, executing it had the effect of then removing all columns from the original SELECT $query when that executed during the db_insert(). I don't understand why that happens, and it's not an issue if we're not doing a count, but it's certainly something to be wary of in general.Comment #23
benjifisherSee the instructions for adding an issue summary: http://drupal.org/node/1427826 . There are not a lot of comments here, and I think the summary should say what the current status is.
Comment #24
David_Rothstein CreditAttribution: David_Rothstein commentedI think we can commit a patch like #12 as a workaround, given that this is one-time code in an update function (and it doesn't seem to change the query for other database drivers), but we need an issue for the real PostgreSQL bug and a code comment referring to it.
So I created an issue at #2057693: PostgreSQL orderBy method adds fields it doesn't need to, leading to fatal errors when the result is used as an insert subquery, and here is a patch that refers to it. I'm leaving at RTBC since I didn't really change anything else; I'll give people a chance to look at it and comment, but otherwise intending to commit this before the next Drupal release. Thanks!
Comment #25
jweowu CreditAttribution: jweowu commentedJust fixing the typo in the subject.
Comment #26
David_Rothstein CreditAttribution: David_Rothstein commentedCommitted to 7.x - thanks! http://drupalcode.org/project/drupal.git/commit/4202d15
Comment #27.0
(not verified) CreditAttribution: commentedPut the error message in quotes and corrected the text that mentionned "group by" instead of "order by".