When editing a node with taxonomy of type multiple and hierarchical, you get a sql warning because drupal tries to insert duplicate term_node rows. This is because the values given to the function that saves the node (taxonomy_node_save) are not filtered correctly.

For example, in function , $terms contains:

array
  11 => 
    array
      0 => '68'
      1 => '54'
  14 => 
    array
      0 => '167'
      1 => '159'
      2 => '175'
  12 => 
    array
      0 => 68
      1 => 54
      2 => 159
      3 => 175
      4 => 167
      5 => 191
  10 => 
    array
      0 => '191'

which generates :

user error: Duplicate entry '68-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 68) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

user error: Duplicate entry '54-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 54) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

user error: Duplicate entry '159-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 159) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

user error: Duplicate entry '175-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 175) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

user error: Duplicate entry '167-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 167) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

user error: Duplicate entry '191-386' for key 1
query: INSERT INTO term_node (nid, tid) VALUES (386, 191) in /web/cg94.fr/drupal/includes/database.mysql.inc on line 108.

I tracked this down to function _taxonomy_term_select()
where you should add :

	$value = array_intersect(array_keys($options), $value);

on line 922.

CommentFileSizeAuthor
#2 intersect.patch518 bytesmansion

Comments

shouchen’s picture

shouchen commented
Version: 4.6.3 » x.y.z

must be cvs (not 4.6.3) since new form api is involved

mansion’s picture

mansion commented
Status: Active » Needs review
StatusFileSize
new intersect.patch518 bytes

Here is a patch against 4.7-beta1 that fixes the problem.

mansion’s picture

mansion commented
Version: x.y.z » 4.7.0-beta1
morbus iff’s picture

morbus iff
they/them
commented

Can you give us a small breakdown of how to create some vocabs/terms that cause this issue? I am unable to do so with Vocab 1 (multiple, 'a', 'b', 'c') and Vocab 2 (single, 'c', 'b', 'b/z') upon node creation or node edit.

ralfm’s picture

ralfm commented

This happens also with Hierarchy: single

Here is an example:


drupal_beta1=# SELECT * from term_data order by tid;
 tid | vid |        name         |                        description                         | weight 
-----+-----+---------------------+------------------------------------------------------------+--------
   1 |   1 | seguridad           | Testing                                                     |      0
  
(10 rows)

drupal_beta1=# SELECT * from term_hierarchy ;
 tid | parent 
-----+--------
   1 |      0
(10 rows)

drupal_beta1=# SELECT * from term_node ;
 nid | tid 
-----+-----
   8 |   1
   9 |   1
(2 rows)
mansion’s picture

mansion commented

Here is how my vocab were created:

Hierarchy : multiple
Common terms: checked
Free tagging: unchecked
Multiple selection : checked
Required: unchecked

(sorry about the translation, I am using another language than English, but you'll figure it out).

Then in every vocab, you create one terms with subterms.

Then, when editing your node, you choose multiple terms. Save.
Then, edit again. Save. Error.

eaton’s picture

eaton commented

I encounter this problem whenever editing a node with free tagging terms. The array_intersect() change fixed it -- no ill-effects, from what I can see, and I can now edit without triggering cascades of DB errors.

+1, unless there's some subtler problem that I haven't spotted.

spiderman’s picture

spiderman
he/they
Primary language English
Location Halifax, NS
commented

not sure if this patch was actually committed, but i can't reproduce it on a fresh install of 4.7.0-beta2, so i gather this problem is resolved :)

eaton’s picture

eaton commented

Same here. I'm cautiously pleased, and it's not reproducable. Yay for hiesenbugs.

mansion’s picture

mansion commented

Obviously, you didn't test in with the right environment because the bug is still here in 4.7beta2.
Here is a backtrace in case it's needed:

Warning: Duplicate entry '153-199' for key 1 query: INSERT INTO term_node (nid, tid) VALUES (199, 153) in /drupal/includes/database.mysql.inc on line 108
Call Stack
# Function Location
1 {main}() /drupal/index.php:0
2 menu_execute_active_handler() /drupal/index.php:15
3 call_user_func_array () /drupal/includes/menu.inc:355
4 node_page() /drupal/includes/menu.inc:0
5 node_form() /drupal/modules/node.module:1999
6 drupal_get_form() /drupal/modules/node.module:1676
7 drupal_submit_form() /drupal/includes/form.inc:102
8 call_user_func_array () /drupal/includes/form.inc:134
9 node_form_submit() /drupal/includes/form.inc:0
10 node_save() /drupal/modules/node.module:1832
11 node_invoke_nodeapi() /drupal/modules/node.module:493
12 taxonomy_nodeapi() /drupal/modules/node.module:317
13 taxonomy_node_save() /drupal/modules/taxonomy.module:1016
14 db_query() /drupal/modules/taxonomy.module:643
15 _db_query() /drupal/includes/database.inc:192
16 trigger_error () /drupal/includes/database.mysql.inc:108

BTW, this bug appears under PHP5.1, I didn't test with other PHP versions.

dries’s picture

dries commented

Related to http://drupal.org/node/41042 ?

adam.skinner’s picture

adam.skinner commented
Title: Duplicate values for insert with multiple taxonomy select (new form API) » Unable to replicate under 4.7b2
Version: 4.7.0-beta1 » 4.7.0-beta2

I created a vocabulary (according to the outlined specification) as follows:

t1
-- t1
-- t2
t2
-- t1
-- t2

I associated the subterms to the matching subterms in t1 for t2. I then created a node, and edited it twice, the second time giving it all 6 terms. This did not cause an error. Perhaps there is a conflict with another installed module?

adam.skinner’s picture

adam.skinner commented
Title: Unable to replicate under 4.7b2 » Duplicate values for insert with multiple taxonomy select (new form API)

[is moron]

eaton’s picture

eaton commented

I'm still seeing it on my 4.7b2 install, and the one-line patch above still fixes the problem. I just realized that my earlier 'can't reproduce it' announcement was due to accidentally copying the patched file into my live directory.

Seriously, +1 on this patch.

moshe weitzman’s picture

moshe weitzman commented

i'm not entirely sure what causes this but i think the error will disappear with http://drupal.org/node/41042. after that one lands, please try to reproduce this and close this if unsuccesssful.

moshe weitzman’s picture

moshe weitzman commented
Priority: Critical » Minor
mansion’s picture

mansion commented
Priority: Minor » Critical

I will check in the next release (beta5 or whatever it is called). If it is still not fixed, I will make it critical again so that it is noticed. I don't think the other bugs you mention are related but I didn't check in details.

mansion’s picture

mansion commented

I just tested with latest CVS.
The bug is still here.
Instead of returning duplicate entries SQL error, it now returns multiple :
Illegal choice <em>145</em> in <em>Services</em> element.

My small patch still fixes it...
Feel free to apply it whenever you like it, but please before going stable.
Thanks.

morbus iff’s picture

morbus iff
they/them
commented

Mansion, I am still unable to duplicate this. Could I get a SQL dump of your term and vocabulary tables so that I can use exactly what you've got? Recreating them manually isn't helping.

morbus iff’s picture

morbus iff
they/them
commented

Also, I'm a little worried about your patch - according to the PHP.net example on array_intersect, that function will actually LOSE unique values, since it will return only items that appear in both arrays. What were your reasonings for choosing array_intersect?

eaton’s picture

eaton commented

Using the latest CVS (2/23/06) I attempted to duplicate this. I created a vocabulary as follows:

Vocab (multiple terms allowed, hierarchy: multiple, related terms, NO free tagging, NOT required)
-Term 1
--Subterm 1
---Sub-Subterm 1
-Term 2
--Subterm 2

I created a page and applied several of the terms to it, saved, and encountered no error. I edite it, changed the terms, and still no error.

Is there another scenerio that I'm missing? It does appear that the issue is resolved in CVS.

chx’s picture

chx commented
Status: Needs review » Fixed

I am marking this issue fixed, I think the fix was a side effect from killes array_merge to + patch.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)