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.

Files: 
CommentFileSizeAuthor
#24 taxonomy_update_7005-pgsql-fix-1759144-24.patch735 bytesDavid_Rothstein
PASSED: [[SimpleTest]]: [MySQL] 40,365 pass(es).
[ View ]
#20 drupal-taxonomy_update_7005-pgsql-fix-1759144-20-after_1993166-3-do-not-test.patch596 bytesjweowu
#12 drupal-taxonomy_update_7005-pgsql-fix-1759144-12.patch538 bytesrondp
PASSED: [[SimpleTest]]: [MySQL] 39,725 pass(es).
[ View ]
#7 1759144-7.x-taxonomy_update_7005-pgsql-fix.patch420 bytesrondp
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1759144-7.x-taxonomy_update_7005-pgsql-fix_0.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]
#5 1759144-7.x-taxonomy_update_7005-pgsql-fix.patch2.05 KBJosh Waihi
FAILED: [[SimpleTest]]: [MySQL] 39,479 pass(es), 1 fail(s), and 0 exception(s).
[ View ]

Comments

Here's a dirty workaround to avoid the error in the updater (near line 682 in taxonomy,install) :

<?php
    $query
->orderBy('tn.vid');
   
$query->orderBy('td.weight');
   
$query->orderBy('tn.tid');
   
$fields =& $query->getFields();
    unset(
$fields['td.weight']);
    unset(
$fields['tn.tid']);
   
db_insert('taxonomy_update_7005')
      ->
from($query)
      ->
execute();
?>

Version:7.15» 7.x-dev
Priority:Normal» Major

This 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.

This "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.

Perfect - 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

<?php
        
'n' => array(
         
'description' => 'Preserve order.',
         
'type' => 'int'// ***Note**** - this changed from serial to int
         
'unsigned' => TRUE,
         
'not null' => TRUE,
        ),
?>

and inserted this after line 684 (per comment #1)

<?php
    $fields
=& $query->getFields();
    unset(
$fields['td.weight']);
    unset(
$fields['tn.tid']);
?>

Thank you for posting those - was a quick solution in the end.

Status:Active» Needs review
StatusFileSize
new2.05 KB
FAILED: [[SimpleTest]]: [MySQL] 39,479 pass(es), 1 fail(s), and 0 exception(s).
[ View ]

This 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.

Status:Needs review» Needs work

The last submitted patch, 1759144-7.x-taxonomy_update_7005-pgsql-fix.patch, failed testing.

StatusFileSize
new420 bytes
FAILED: [[SimpleTest]]: [MySQL] Unable to apply patch 1759144-7.x-taxonomy_update_7005-pgsql-fix_0.patch. Unable to apply patch. See the log in the details link for more information.
[ View ]

Ran 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.

Status:Needs work» Needs review

Status:Needs review» Needs work

The last submitted patch, 1759144-7.x-taxonomy_update_7005-pgsql-fix.patch, failed testing.

As 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.

Ah. 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

StatusFileSize
new538 bytes
PASSED: [[SimpleTest]]: [MySQL] 39,725 pass(es).
[ View ]

Ah, 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. :-)

mbair-wifi:taxonomy ronald$ patch < ~/Desktop/1759144-7.x-taxonomy_update_7005-pgsql-fix.patch
patching file taxonomy.install
mbair-wifi:taxonomy ronald$ git diff > ~/Desktop/drupal-taxonomy_update_7005-pgsql-fix-1759144-12.patch

Please find attached (also now following proper naming instructions).

Just 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!

So, 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.

Status:Needs work» Needs review

Just needs to be set to needs review.

Great, thanks Berdir.
And it passed, too, which is nice.

Version:7.x-dev» 7.17
Assigned:Unassigned» acanthoweb

HI,
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.

Patch 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

Status:Needs review» Needs work

#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.

Status:Needs work» Reviewed & tested by the community

And as #12 works just fine -- RTBC.

I'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.

See 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.

Version:7.17» 7.x-dev
StatusFileSize
new735 bytes
PASSED: [[SimpleTest]]: [MySQL] 40,365 pass(es).
[ View ]

I 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!

Title:taxonomy_update_7005 on pgsql returs error "column "td" of relation "taxonomy_update_7005" does not exist"taxonomy_update_7005 on pgsql returns error "column "td" of relation "taxonomy_update_7005" does not exist"

Just fixing the typo in the subject.

Status:Reviewed & tested by the community» Fixed
Issue tags:+7.23 release notes

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

Issue summary:View changes

Put the error message in quotes and corrected the text that mentionned "group by" instead of "order by".