term_node table is empty after upgrading from Drupal 5 to 6

Please try the following:

1. restore the backup of your Drupal 5 database and confirm the term_node table has some row
2. Re-run the Drupal 5-6 update and post back if you get the same issues again, specifically any errors on the update.php report.

I've not seen this bug reported anywhere else so we need more information.

Yes, the vid is for the node revision - if you have a corrupted entry in your node table (with no vid), then it's quite possible the update query would fail. You'll need to check your node table (SELECT nid from node WHERE vid IS NULL or similar ought to work), and delete any corrupted nodes before trying the update again.

vid column was added in drupal 6, so that shouldn't be an issue. If you're getting 'other errors' I'm wondering if the cause might not be directly related to taxonomy module.

I have exactly the same problem as amanda267. My term_node table was full of entries and now it's empty. Whatever code is responsible for getting the latest revision of each node and inserting it into the vid column is not only failing, but not checking to see whether it failed or not before deleting the data in the table.

klance, thanks for confirming the bug. Could either or both of you post any error messages you're getting - i.e. from update.php itself or from the watchdog?

I just realized thanks to Nat that I must have encountered the same bug.

please see http://drupal.org/node/356394 and http://drupal.org/node/358739 for details and links to the same page
in drupal 5.5 and drupal 6.9, with the first working OK, the second showing the bug.

Like the others, I have not seen any related error/warning during the 5.5->6.8 upgrade,

Please see again http://drupal.org/node/358739 where interesting comments appeared a few minutes ago

wrt the other users, the only difference in my case, is that I also upgraded from 6.8 to 6.9. Also, my term_node table is not empty (both in the 6.8 and 6.9 databases). I don't know if and how this and the comments in http://drupal.org/node/358739 are all related.

is it possible to rebuild the term_node table so that it works with sane URLs? I have no problem to work at the mysql prompt, but I need to know what to do, and going back to 5.5 I'd lose all the content added after the upgrade (since I didn't immediately realize I had this problem)

Please let me know via email or contact form if you need me to add more info here and/or run some tests

Like the others, I have not seen any related error/warning during the 5.5->6.8 upgrade

Actually, the others said they DID see warnings during the upgrade. We need the exact text of the errors before being able to diagnose this problem.

I explicitly said "related". Unfortunately, I don't think I have the possibility now to reproduce the warnings I saw. In any case, my problem seems different now, as my term_node table was never empty, as I said.

Please have a look at http://drupal.org/node/286052, where there is further discussion just now on the problem I noticed, which is definitely related to the views module and may help with this other issue too.

HTH,
M.

this error - user warning: Column 'vid' cannot be null query: UPDATE term_node t SET vid = (SELECT vid FROM node n WHERE t.nid = n.nid) in C:\xampp\htdocs\modules\system\system.install on line 1154. - causes emptiness of term_node.

You have some rows in "term_node" with "nid" that not exist in "node" table.

You can get list of this nids by query:

SELECT DISTINCT nid FROM `term_node` t WHERE (SELECT count( * ) FROM node n WHERE t.nid = n.nid) = 0

Deleting rows in "term_node" with this nids will solve your problem :)

ps Sorry for my English :)))

I think these update.php errors are related to the problem:

# warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "t" at character 18 in includes/database.pgsql.inc on line 139.
# user warning: ERROR: syntax error at or near "t" at character 18 query: UPDATE term_node t SET vid = (SELECT vid FROM node n WHERE t.nid = n.nid) in system.install on line 1150.

Looks like you might not be able to reference the parent query from the one in parenthesis; at least not in postgresql.

I have the same problem.
Here are the errors from update.php:

    * user warning: Column 'vid' cannot be null query: UPDATE term_node SET vid = (SELECT vid FROM node n WHERE term_node.nid = n.nid) in /Library/WebServer/Documents/modules/system/system.install on line 1164.
    * user warning: Duplicate entry '9-0' for key 'PRIMARY' query: ALTER TABLE term_node ADD PRIMARY KEY (tid, vid) in /Library/WebServer/Documents/includes/database.mysql-common.inc on line 374.

This may or may not be related, but I have lots of nodes with terms, but no revisions.

It looks like another update is clearing the table.

*

Update #6044

* DELETE FROM {term_node} WHERE vid = 0

I walked through the queries that relate to that table during the update process, and it looks like it adds the vid column, shuffles the keys around, then erases all the data from the table. What should vid hold?

----

Okay, looks like the query failed where the term_node.vid field is supposed to get populated during the update process.

I reverted to my drupal 5 database. Then I ran the queries by hand that create the field and arrange the keys. Then I manually made the vid field match the nid field (I don't have any revisions, so this *should* work if my guesses are correct. This may destroy critical data if you try it.....!) with this query:
UPDATE term_node SET vid=nid;
Then I re-ran update.php to apply the rest of the version 6 core updates. I got some expected query errors, but at the end of the process the table still held its data. Both a quick check in mysql and browsing to a few of the nodes shows that they survived the update with assigned taxonomy intact.

This is a sloppy process, but I hope it helps someone.

--|deleted|--

On your Drupal 5 database, please run SELECT COUNT(*) FROM node WHERE vid IS NULL and let us know the results.

mysql> select count(*) from node where vid is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

Hey,

I had the same issue with postgresql I ran this query and it seemed to fix the issues:

update term_node set vid=n.vid FROM node n where n.nid=term_node.nid;

I experienced the same issue when updating from Drupal 5.19 to Drupal 6.13 and the term_node was empty.

My term_node table was innoDB and not myisam.

* user warning: Column 'vid' cannot be null query: UPDATE term_node SET vid = (SELECT vid FROM node n WHERE term_node.nid = n.nid) in /domains/EXAMPLE.com/html/modules/system/system.install on line 1164.

* user warning: Duplicate entry '1-0' for key 'PRIMARY' query: ALTER TABLE term_node ADD PRIMARY KEY (tid, vid) in /domains/EXAMPLE.com/html/includes/database.mysql-common.inc on line 374.

Failed: ALTER TABLE {term_node} ADD PRIMARY KEY (tid, vid)

I used a backup altering the table

ALTER TABLE term_node TYPE = myisam;

And updated to Drupal 6.13 but my term_node table is somehow corrupted and taxonomy are missing on blog entries. Some nodes are missing one taxonomy term others are missing multiple taxonomy terms. I'll post that as another issue.

taxonomy.module was disabled during update.php in both instances.

I'm using Mediatemple Gridservice
Status:
Drupal 6.13
Access to update.php protected
Cron-opgaver Sidst kørt for 2 timer 33 minutter siden
Du kan køre cron manuelt.
Database updates Opdateret
Drupal core update status No update data available
No information is available about potential new releases for currently installed modules and themes. To check for updates, you may need to run cron or you can check manually. Please note that checking for available updates can take a long time, so please be patient.
Filsystem Skrivbar (offentlig download-metode)
Konfigurationsfil Beskyttet
MySQL database 5.1.26
PHP 5.2.6
PHP memory limit 64M
PHP register globals Ingen
Unicode-bibliotek PHP mbstring-udvidelse
Update notifications Aktiveret
webserver Apache/2.0.54

I made a how to fix this here:
http://drupal.org/node/569534#comment-2018056

#569534: Drupal 5.19 update to Drupal 6.13 removed term_node s above tid 39 was marked as duplicate on 21 Sep 09.

I just ran into this on a D5->D6 upgrade. The term_node table went from ~12k to 500 entries. The cause was orphaned nids in term_node. As soon as the query in system_update_6001() hits an nid that doesn't have a match in the node table, the query is aborted, and then system_update_6044() wipes out any entries in term node that come after that first orphaned node.

The attached patch deletes the orphaned nids first, then runs the update query to set the vid.

Not sure why the test bot would fail to apply the patch. Trying again.

#24: drupal.345080.patch queued for re-testing.

I think you should first check to find any empty nids one before attempting to upgrade with like this:

SELECT DISTINCT nid FROM `term_node` t WHERE (SELECT count( * ) FROM node n WHERE t.nid = n.nid) = 0

These results should then be used to remove empty nids:

DELETE FROM `term_node` WHERE `nid` = 463;
DELETE FROM `term_node` WHERE `nid` = 1065;

After the empty nid's are removed then do the upgrade to new D6.

If you manually remove the empty nid's before update from 5.23 to 6.20 it goes without any problems.

I just did that without any problems.

Setting back to needs review.