By W.M. on

I am not sure if this is the way Drupal 7 works, I need to verify:

I have noticed that upon assigning a taxonomy term to a node, duplicate data (two similar rows) get added to taxonomy_index table.

This is what I see inside the table using phpmyadmin:

nid tid sticky
Edit 2 1 0 1310719325
Edit 2 1 0 1310719325

Is this the way it should be ?!

⋅ Categories: Drupal 7.x

Comments

Patrick Hubert’s picture

Patrick Hubert commented

I see the same problem with a Drupal 6 site that I am updating to D7.
The old nodes are correct, but as soon as I modify them I see the taxonomy fields being duplicated.

I am using D7.4, as there are multilang issues with D7.7. I cannot confirm that this is also occurring in the latest version.

-Patrick

mat.’s picture

mat. commented

Yes, I have the same problem - duplicate items in table "taxonomy_index", and this causes the duplicate items in Views (f.e. "related posts").

http://imageshack.us/photo/my-images/829/moodleszskmczphpmyadmin.png/

Drupal 7.7, multilanguage

pvasili’s picture

pvasili commented

You should remove all duplicate lines and do in php:
db_add_primary_key('taxonomy_index', array('nid', 'tid'));
or SQL
ALTER IGNORE TABLE taxonomy_index ADD PRIMARY KEY (nid, tid);

CREATE TABLE taxonomy_index_copy AS (SELECT DISTINCT * FROM taxonomy_index);
TRUNCATE TABLE taxonomy_index;
INSERT INTO taxonomy_index SELECT *  FROM taxonomy_index_copy;
DROP TABLE taxonomy_index_copy;
ALTER IGNORE TABLE taxonomy_index ADD PRIMARY KEY (nid, tid);
W.M.’s picture

W.M. commented

But it looks like this is a Drupal 7 core issue, any commits planned for future stable release ?!

pvasili’s picture

pvasili commented

http://drupal.org/node/610076

You should check the presence of this bug:
SELECT COUNT(DISTINCT (CONCAT(tid,nid))) dist,COUNT(*) total FROM taxonomy_index
If dist = total {
ALTER IGNORE TABLE taxonomy_index ADD PRIMARY KEY (nid, tid);
}
else {
execute all 5 SQL queries above
}

pvasili’s picture

pvasili commented

I added a primary key, and began to edit my material.
Some fields have been changed.
I'm trying to save the material and see:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '52-101' for key 'PRIMARY': INSERT INTO {taxonomy_index} (nid, tid, sticky, created) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => 52 [:db_insert_placeholder_1] => 101 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] => 1316360296 ) in function taxonomy_field_update() (string 1730 in file /.../modules/taxonomy/taxonomy.module).

andypost’s picture

andypost
he/him
Primary language Russian
commented

Duplicates are caused by #1050466: The taxonomy index should be maintained in a node hook, not a field hook