Updating content with taxonomy hierarchy gives the following error:

* user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') td, term_hierarchy th, term_synonym ts WHERE td.vid = 1 AND th.parent = 664 AN' at line 1 query: SELECT td.tid FROM term_data) td, term_hierarchy th, term_synonym ts WHERE td.vid = 1 AND th.parent = 664 AND td.tid = th.tid AND td.tid = ts.tid AND LOWER(ts.name) = '' in /sites/default/modules/node_import/supported/taxonomy.inc on line 406.

This is caused by a typo in sites/default/modules/node_import/supported/taxonomy.inc
Apply the following patch to fix:

*** taxonomy.inc.orig   2009-09-28 09:51:03.000000000 +1000
--- taxonomy.inc        2009-09-28 09:52:23.000000000 +1000
***************
*** 403,409 ****
        // parent (or the parent will be created). So stop.
      }
      else if (($tid = db_result(db_query("SELECT td.tid FROM {term_data} td, {term_hierarchy} th WHERE td.vid = %d AND th.parent = %d AND td.tid = th.tid AND (td.tid = %d OR LOWER(td.name) = '%s')", $vocab->vid, $value, (is_numeric($child) && intval($child) > 0) ? $child : -1, $child))) ||
!              ($tid = db_result(db_query("SELECT td.tid FROM {term_data) td, {term_hierarchy} th, {term_synonym} ts WHERE td.vid = %d AND th.parent = %d AND td.tid = th.tid AND td.tid = ts.tid AND LOWER(ts.name) = '%s'", $vocab->vid, $value, $child)))) {
        $value = $tid;
      }
      else {
--- 403,409 ----
        // parent (or the parent will be created). So stop.
      }
      else if (($tid = db_result(db_query("SELECT td.tid FROM {term_data} td, {term_hierarchy} th WHERE td.vid = %d AND th.parent = %d AND td.tid = th.tid AND (td.tid = %d OR LOWER(td.name) = '%s')", $vocab->vid, $value, (is_numeric($child) && intval($child) > 0) ? $child : -1, $child))) ||
!              ($tid = db_result(db_query("SELECT td.tid FROM {term_data} td, {term_hierarchy} th, {term_synonym} ts WHERE td.vid = %d AND th.parent = %d AND td.tid = th.tid AND td.tid = ts.tid AND LOWER(ts.name) = '%s'", $vocab->vid, $value, $child)))) {
        $value = $tid;
      }
      else {
CommentFileSizeAuthor
#5 osc_sg - Products.txt317.69 KBarpieb
#5 osc_sg - Products - Failed.txt52.95 KBarpieb
#2 Map file columns (step 4 of___.pdf49.14 KBarpieb
#2 Set default values (step 6 ___.pdf58.64 KBarpieb
#2 Preview import (step 7 of 8___.pdf56.76 KBarpieb
#2 Terms in Parts Catalog Is___.pdf116.91 KBarpieb
#2 Isetta_import_errors.txt42.32 KBarpieb
#2 Isetta 250-300 - Engine.txt18.34 KBarpieb
taxonomy.inc_.patch1.45 KBscholar

Comments

jenlampton’s picture

jenlampton
she / her
commented

Thanks for this. Patch working for me.

arpieb’s picture

arpieb commented
StatusFileSize
new Isetta 250-300 - Engine.txt18.34 KB
new Isetta_import_errors.txt42.32 KB
new Terms in Parts Catalog Is___.pdf116.91 KB
new Preview import (step 7 of 8___.pdf56.76 KB
new Set default values (step 6 ___.pdf58.64 KB
new Map file columns (step 4 of___.pdf49.14 KB

I'm getting this same error on D6.14 using the latest (Oct 10, 2009) build of 6.x-1.x-dev. When I try to apply the patch above, I get a WSOD and the following error in my php_errors.log file, and as such have backed out the patch:

[28-Nov-2009 20:25:26] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required './sites/all/modules/node_import/supported/taxonomy.inc' (include_path='.;c:\php\includes;C:\PHP\XLS.Reader.1.2\xls_reader') in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\isettasrus\sites\all\modules\node_import\node_import.inc on line 73

I have attached all pertinent import setup info, module info, taxonomy info and the actual file I'm trying to import. I also tried to "force" the import to go through by ignoring the errors at Step 7, and have attached the resulting error log from node_import. The kicker is, I imported a smaller test file only a couple weeks ago that worked flawlessly with the same release of the node_import module.

I've got about 20 of these files to import (around 4000 parts listings) and am on a tight timeline as I thought the import would go through on the "live" data as all the test data loaded just fine...

I've also attempted an import using the RC4 version, and all I get are WSOD's on every attempt.

Browser is Firefox 3.5.5, dev server is a WAMP installation running PHP 5.2.x. I'm running a multilanguage setup for English and German (could this be causing problems?).

Please let me know if you need any further info to help me troubleshoot this import.

Thanks!

-R

arpieb’s picture

arpieb commented

I found the problem after some digging. It turns out the table column {vocabulary}.hierarchy was set to 0 instead of 1 for the UC catalog vocabulary, which confused node_import into thinking it was a flat vocabulary. Once that was changed in the database, the import loaded flawlessly.

I'm still not sure why the taxonomy term wasn't being reported in the error message though - unless node_import compares the value and reports a blank value on import if there are no matching terms in the vocabulary...?

-R

onlineguy’s picture

onlineguy commented

Yes, I finally saw the difference after downloading and examining patch vs. original, basically one exclamation point (negation symbol)... your patch shows the original same as the patched code! I'll try it and see if it works for me. Thanks.

arpieb’s picture

arpieb commented
StatusFileSize
new osc_sg - Products - Failed.txt52.95 KB
new osc_sg - Products.txt317.69 KB

Actually, the typo is specifically that a ")" was used instead of a "}" to close a table name reference on line 406:

SELECT td.tid FROM {term_data) td

(notice that "term_data" is closed with a paren instead of curly)

The patch fixes the SQL error for me as well. However, I have migrated my template site to a new client site, and have blown out the Catalog vocabulary and all Product content to prep for a new import (from osCommerce - woohoo!). Now I'm only getting the error message:

Input error: is not allowed for Catalog (not a term or synonym in Catalog).

with no other info. I've checked to make sure that {vocabulary}.hierarchy = 1 for my load, and as far as I can tell the input TSV file is properly formatted (attached, along with the "Node Import debug report") and it looks like the proper column values are being loaded from the file, just not being assigned to the node's taxonomy fields...? I am correct in that the node_import module creates terms in the vocabulary as needed on import, yes?

Any help would be greatly appreciated! I'll keep plugging away and post anything else I run across that might hold a clue...

-R

onlineguy’s picture

onlineguy commented

My product node_import (after fixing several actual errors your module quickly identified) worked FLAWLESSLY
after applying this patch. By the way now I see why you posted both orig and patch the same (in case anyone
pasted the wrong one, they'd still patch the code). THANK YOU SO MUCH.

danieldd’s picture

danieldd commented

arpieb, I don't know if you're still struggling with this but I have had the same error message & issues and after much frustration seem to have finally got it to work (at least with test data. Will repost if I have further problems when doing main import).

Here are steps it took:

- I used latest Dev version of node import. I installed the patch above. And I ensured heirarchy was set to 1 as per your comment #5.

- I prepopulated the vocabularies that I was trying to import, as per this issue: http://drupal.org/node/680254.

- I had my vocabularies set to select list (don't know if this is significant). I did not enable multiple values or user generated values in the vocabularies.

- I disabled content taxonomy module. I had been trying to import terms as content taxonomy as per this issue http://drupal.org/node/434572. However, it seems importing terms with heirarchies is not supported here. Instead I just imported into core taxonomy.

- I ensured the syntax of the vocabulary terms was formatted in my csv file as follows: parent>>child. And then accepted the >> heirarchy delimiter in step 5. I did not put more than 2 levels of the heirarchy in any field. (as a separate issue, you may want to check that the CSV/TSV file is being delimited consistently. Some of my terms had , in them and were being delimited with " while other terms which did not have special characters were not which was confusing the module).
- Note the terms can also be imported when you just put a single term (without showing a heirarchy) in the term column, although if child/ parent terms are duplicated in the vocabulary this obviously means the node can be assigned to the wrong term - so this option only worked for one of my vocabularies.

- After all these steps I have managed to import 600+ nodes so far without any errors.

Hope this helps, and saves you some frustration. If you have any success with Content Taxonomy or discover any better ways of doing this let us know.

danieldd’s picture

danieldd commented

Sadly, my luck did not continue. After building a new site, I went through exactly the same process to import nodes with terms attached.

This time it did not work. No matter how I formatted the csv file and presented the parent/ child relationships etc, the module did not seem able to recognise which terms to assign (my taxonomy has the same terms duplicated under different parents and at different levels in the heirarchy). It seems strange as it had worked perfectly previously.

My ultimate solution was to import the terms using tid rather than the term names. This required a bit of preparation (ie putting all the terms with associated tid's in a spreadsheet and using that to look up tid's on the csv I wanted to import). However, it worked perfectly once I had done this.

I recommend that others with complex vocabularies take this approach rather than try and import by term name, until the module is better able to support taxonomy.

nally’s picture

nally commented

I am getting this same error and found that my tables 'hierarchy' column was indeed set to 0 in the vocabulary table, as some have observed above.

Changing this has not improved the situation however.

I'm wondering whether others observing this behavior have been using the hierarchical select module at all.

Does anyone have any experiences or qualifiers on that?

arpieb’s picture

arpieb commented

I also ran into some issues with trying to import a child before the parent had been declared. For example:

Football >> NFL >> Falcons
Football >> NFL
Football

Would fail for the first two items because the parent wasn't defined yet (happened primarily in Taxonomy imports). You need to make sure the parents exist first:

Football
Football >> NFL
Football >> NFL >> Falcons

(Horrible analogy, but I was just on ESPN.com - guess subliminal messages DO work!)

Could be some of what you're running into with HS imports as well...?

nally’s picture

nally commented

Thanks @arpieb for the fast response. I saw your suggestion right away, but it turned out that I had the taxonomy already defined.

I've just gotten 'round to retrying without Hierarchical Select enabled (i.e. I disabled it) and that helped a certain amount. At least I was able to import nodes with terms from the hierarchical taxonomy.

What happened though, was that when I imported a node tagged by a "leaf" term like NFL in your example, using the syntax "Football >> NFL" in the CSV... the node was indeed tagged by that single term. (not Football, but only NFL).

Then when I turned hierarchical select on and went back to edit the node, it actually nuked NFL, rather than adding Football.

Further testing will be required.

This seems to be analogous to how Dates are difficult to import if you don't have Select List set as the UI widget for a CCK Date. It must be that Node Import is using those widgets in some way.