Duplicate entry in category_menu_map [#78906]

1.) When I add assign a new node to a category, a new menu item will be created.
2.) I will go to admin/menu, and i delete this new menu item. Everything goes fine untill here, the menu item is gone as espected!
3.) Only when i update the created node, you get a sql error..because the menuitem will be created again. But the record in the category_menu_map table is still present, so it's a duplicate entry.

user warning: Duplicate entry '93' for key 1 query: INSERT INTO category_menu_map (nid, mid) VALUES (93, 350) in.

Comment	File	Size	Author
#15	category_menu.install_1.patch	2.22 KB	Jaza
#10	category_menu.install_0.patch	2.11 KB	Jaza
#9	category_menu.install.patch	2.71 KB	bdragon
#4	dup_cat_menu_error1.gif	5.83 KB	venkat-rk

Comments

Comment #1

whereisian commented 2 September 2006 at 07:49

I'm having the same problem.

user warning: Duplicate entry '84-83' for key 1 query: INSERT INTO category_hierarchy (cid, parent) VALUES (84, 83) in /var/www/html/sandbox/includes/database.mysql.inc on line 120.

Not sure if it's related to these:
http://drupal.org/node/73679
http://drupal.org/node/81507

Cheers,

Ian

Comment #2

bdragon commented 18 September 2006 at 00:42

Please try again with today's version.
This bug was most likely a side effect of the category_get_parents() problem described in
http://drupal.org/node/76921

Thank you for your patience.
--Brandon

Comment #3

vatavale commented 19 September 2006 at 10:27

I have the same problem (also with today's version of category module).

Comment #4

venkat-rk commented 28 September 2006 at 11:45

Status	File	Size
new	dup_cat_menu_error1.gif	5.83 KB

I can confirm that this still exists with the category version of 25/09/06.

What I was doing:

1.) I had just tried multiple distant parents for a container in conjunction with pathauto (to get url paths spanning several containers and categories), but as that didn't seem to work, I reset all the containers back to having 'this container' as the distant parent.

2.) To see if I could replicate the book_load problem described in another issue, I ran a bulk update of nodes in pathauto. My placeholders were [categorypath] for containers and categories and [categorypathfirst] for all node types except event and page.

3.) The bulk update destroyed all my previous aliases and instead generated aliases for just the categories of a container (instead of container-name/category-name) or gave [categorypathfirst]-0 to 6 for story nodes that all had perfectly valid and hierarchical aliases before.

4.) I picked a category whose alias should have been container-name/category-name, and edited it and saved the changes. I noticed that I didn't have to select the cat's parent container as it was already correctly selected. Anyhow, I saved the changes and that's when the duplicate entry error hit me.

Comment #5

TheWhippinpost commented 29 September 2006 at 03:09

Witnessed.

user warning: Duplicate entry '820-801' for key 1 query: INSERT INTO category_hierarchy (cid, parent) VALUES (820, 801) in C:\Servers\Apache2\cgi-bin\drupal\includes\database.mysql.inc on line 120.

Strangely, although I was hitting this message regularly last night, tonight, it didn't occur for the first new category added, but just did for the second - Whether this is a clue pointing to a cache issue, I don't know.

Comment #6

bdragon commented 29 September 2006 at 21:07

Witnessed.
Will debug.

Comment #7

bdragon commented 29 September 2006 at 22:53

Ok...

Multiple menu items for one node.

PHP Stack trace:
PHP   1. {main}() /var/www/servers/cats.rtk0.net/pages/index.php:0
PHP   2. menu_execute_active_handler() /var/www/servers/cats.rtk0.net/pages/index.php:15
PHP   3. call_user_func_array() /var/www/servers/cats.rtk0.net/pages/includes/menu.inc:418
PHP   4. node_page() /var/www/servers/cats.rtk0.net/pages/includes/menu.inc:0
PHP   5. node_form() /var/www/servers/cats.rtk0.net/pages/modules/node.module:2057
PHP   6. drupal_get_form() /var/www/servers/cats.rtk0.net/pages/modules/node.module:1584
PHP   7. drupal_submit_form() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:127
PHP   8. call_user_func_array() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:200
PHP   9. node_form_submit() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:0
PHP  10. node_save() /var/www/servers/cats.rtk0.net/pages/modules/node.module:1840
PHP  11. node_invoke_nodeapi() /var/www/servers/cats.rtk0.net/pages/modules/node.module:495
PHP  12. category_menu_nodeapi() /var/www/servers/cats.rtk0.net/pages/modules/node.module:317
PHP  13. category_menu_entity_updated() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:65
PHP  14. category_menu_save_item() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:347
PHP  15. category_menu_insert_mapping() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:567
PHP  16. db_query() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:617
PHP  17. _db_query() /var/www/servers/cats.rtk0.net/pages/includes/database.inc:197
PHP  18. trigger_error() /var/www/servers/cats.rtk0.net/pages/includes/database.mysql.inc:121
PHP Warning:  Duplicate entry &#039;14&#039; for key 1
query: category_menu_insert_mapping
INSERT INTO category_menu_map (nid, mid) VALUES (14, 48) in /var/www/servers/cats.rtk0.net/pages/includes/database.mysql.inc on line 121

PHP Stack trace:
PHP   1. {main}() /var/www/servers/cats.rtk0.net/pages/index.php:0
PHP   2. menu_execute_active_handler() /var/www/servers/cats.rtk0.net/pages/index.php:15
PHP   3. call_user_func_array() /var/www/servers/cats.rtk0.net/pages/includes/menu.inc:418
PHP   4. node_page() /var/www/servers/cats.rtk0.net/pages/includes/menu.inc:0
PHP   5. node_form() /var/www/servers/cats.rtk0.net/pages/modules/node.module:2057
PHP   6. drupal_get_form() /var/www/servers/cats.rtk0.net/pages/modules/node.module:1584
PHP   7. drupal_submit_form() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:127
PHP   8. call_user_func_array() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:200
PHP   9. node_form_submit() /var/www/servers/cats.rtk0.net/pages/includes/form.inc:0
PHP  10. node_save() /var/www/servers/cats.rtk0.net/pages/modules/node.module:1840
PHP  11. node_invoke_nodeapi() /var/www/servers/cats.rtk0.net/pages/modules/node.module:495
PHP  12. category_menu_nodeapi() /var/www/servers/cats.rtk0.net/pages/modules/node.module:317
PHP  13. category_menu_entity_updated() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:65
PHP  14. category_menu_save_item() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:347
PHP  15. category_menu_insert_mapping() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:567
PHP  16. db_query() /var/www/servers/cats.rtk0.net/pages/sites/cats.rtk0.net/modules/contributions/modules/category/category_menu/category_menu.module:617
PHP  17. _db_query() /var/www/servers/cats.rtk0.net/pages/includes/database.inc:197
PHP  18. trigger_error() /var/www/servers/cats.rtk0.net/pages/includes/database.mysql.inc:121
PHP Warning:  Duplicate entry &#039;14&#039; for key 1
query: category_menu_insert_mapping
INSERT INTO category_menu_map (nid, mid) VALUES (14, 50) in /var/www/servers/cats.rtk0.net/pages/includes/database.mysql.inc on line 121

Looking at mysql:

mysql> describe category_menu_map;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned |      | PRI | 0       |       |
| mid   | int(10) unsigned |      | MUL | 0       |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Hmm....

mysql> show keys from category_menu_map;
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table             | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| category_menu_map |          0 | PRIMARY  |            1 | nid         | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| category_menu_map |          1 | mid      |            1 | mid         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

Why is nid PRIMARY in this table? Isn't this supposed to be an N:M join table?

Comment #8

shunshifu commented 15 October 2006 at 15:57

I believe I am having a similiar problem. When creating a category it creates a duplicate. When editing one I get this

Warning: Duplicate entry '421-416' for key 1 query: INSERT INTO category_hierarchy (cid, parent) VALUES (421, 416) in /home/shoushuc/public_html/includes/database.mysql.inc on line 120

Warning: Cannot modify header information - headers already sent by (output started at /home/shoushuc/public_html/includes/database.mysql.inc:120) in /home/shoushuc/public_html/includes/common.inc on line 266

When I refresh the page I get a warning that the category has been edited by another user cannot edit

Thanks

Phil

Comment #9

bdragon commented 5 November 2006 at 20:03

Assigned:	Unassigned	» bdragon
Status:	Active	» Needs review

Status	File	Size
new	category_menu.install.patch	2.71 KB

(This is now the primary issue for "duplicate entry for category_menu_map" errors.)

This patch for category_menu.install will (hopefully) fix the keys on the category_menu_map table to allow multiple mappings per node.

After patching, visit update.php and run the update for category_menu.

I'm not sure if there will be further issues or not with the actual *code*, as the primary key on the table has been wrong since the beginning, so the code to read from the table has never *encountered* multiple mids for one nid (due to them not getting stored)

Comment #10

Jaza commented 6 November 2006 at 03:39

Status	File	Size
new	category_menu.install_0.patch	2.11 KB

You're right that the primary key is wrong. But in your patch, the index keys are wrong. I think that the key structure for category_menu_map should be as follows:

PRIMARY KEY (nid,mid),
KEY mid (mid),
KEY nid (nid)

This is how the term_node table in core (also a M:N table), and the equivalent category_node table, does it. Updated patch that implements this index structure. Let me know if it looks OK, and I'll commit it.

Comment #11

bdragon commented 6 November 2006 at 03:52

Ok, I'm okay with mid being just (mid) (as we probabaly don't need to order by nid) but the nid (nid) is a duplicate of the first part of the primary key.

Comment #12

bdragon commented 6 November 2006 at 03:56

One reason to have the indexes like I had them is the ability to serve data directly out of the index.

"In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed"

Comment #13

Jaza commented 6 November 2006 at 04:13

@bdragon: perhaps you'd be able to explain a few things to me, as it looks like you know more about database indexing than me (I admit that I'm not an expert on the subject, I just pick up what I can):

Is a key or primary key of (field1, field2) different to (field2, field1), and if so, how?
What is the practical difference between a key of (field1, field2), and two separate keys of (field1), (field2)? And which type of key(s) is better in the case of category_menu_map?

I'm willing to follow your advice and judgement, but I'd like to understand as much as I can before committing anything. :P

Also, answering my questions by saying 'RTFM' and pointing me to a URL is fine. I don't expect you to go out of your way explaining the intricacies of database indexing to me (or to anyone else, for that matter).

Comment #14

bdragon commented 6 November 2006 at 05:16

1. Is a key or primary key of (field1, field2) different to (field2, field1), and if so, how?

Yes. MySQL can only read indexes left to right. The index is not even *considered* unless it can start a match with the leftmost part of the index. The first parts of the key should be the WHERE conditions, followed by any ORDER BY conditions, or in our case, the data.

"SELECT field2 FROM table WHERE field1 = 11111" would not be able to use the (field2,field1) key.
"SELECT field1 FROM table WHERE field2 = 11111" would not be able to use the (field1,field2) key.

If both keys are in place, and field1 and field2 are INTEGERs, both of those queries can be performed entirely within the index.

2. What is the practical difference between a key of (field1, field2), and two separate keys of (field1), (field2)? And which type of key(s) is better in the case of category_menu_map?

The seperate keys are smaller, which is a good thing in most circumstances.

_However_, this is a join table. Having a forwards and backwards key that uses both columns means MySQL can bypass the data table completely, as all the needed data is stored directly in the index. That makes joins faster.

http://dev.mysql.com/doc/refman/4.1/en/explain.html
http://dev.mysql.com/doc/refman/4.1/en/mysql-indexes.html

Comment #15

Jaza commented 6 November 2006 at 05:50

Status:

Needs review

» Fixed

Status	File	Size
new	category_menu.install_1.patch	2.22 KB

Attached patch is the same as bdragon's patch from #9 (above), with minor code cleanup. Patch committed to HEAD and 4.7.

Thanks for explaining the logic behind your choice of indexing, bdragon. I think I understand now why the new structure is more appropriate for the category_menu_map table. Anyway, being a developer is all about learning - thanks for teaching me something very useful!

Comment #16

(not verified) commented 20 November 2006 at 06:00

Status:

Fixed

» Closed (fixed)

Comment #17

hectorplus commented 21 November 2006 at 09:52

Ok, i just downloaded the latest release of category(Dated nov. 11), it said it updated the category_menu_map file, but the issues is not fixed. I still get a duplicate erro.
I am not familiar in using the .patch thing, do you know of any tutorial. I am using eclipse. Thanks

Comment #18

transformative commented 27 November 2007 at 21:58

http://drupal.org/patch/apply

Comment #19

fluitfries commented 28 November 2007 at 16:08

why is this issue closed? i have the latest patch installed, even checked my live files against HEAD and dev, and the patch itself, and this issue is not fixed for me.

could it be because i have had the issue for so long and now there might be problems in my db? do i need to do any sort of uninstall/install of the module? please help!

Duplicate entry in category_menu_map