Menu problems with Postgres [#10349]

After entering the menu module in administer - I get the following series of errors appear in the web browser. As can be see, the value for mid is always 0, but this is the primary key and (of course) needs to be unique for each record. I have also lost all my administer menus. - and trying to reset the menu system doesn't help.

user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'archive', 'archives', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/block', 'blocks', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/blog', 'blog entry', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'blog', 'blogs', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/book', 'book page', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/node/book', 'books', '', 4, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/node/book/orphan', 'orphan pages', '', 8, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'book', 'books', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/comment', 'comments', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/filters', 'input formats', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'filter/tips', 'compose tips', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/forum', 'forum topic', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/help', 'help', '', 9, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/menu', 'menus', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/node', 'content', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'node', 'content', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add', 'create content', '', 1, 28) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/page', 'page', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/path', 'url aliases', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/poll', 'poll', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'poll', 'polls', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'search', 'search', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'search/help', 'search help', '', 0, 16) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'node/add/story', 'story', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/themes', 'themes', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/settings', 'settings', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/settings/blog', 'blog', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/settings/forum', 'forum', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/settings/story', 'story', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/settings/throttle', 'throttle', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/modules', 'modules', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/taxonomy', 'categories', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'tracker', 'recent posts', '', 1, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/upload', 'uploads', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, -120, 'user/1', 'my account', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 1, 'logout', 'log out', '', 10, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/user', 'users', '', 0, 22) in /usr/share/drupal/includes/database.pear.inc on line 111.
user error: DB Error: unknown error
query: INSERT INTO menu (mid, pid, path, title, description, weight, type) VALUES (0, 0, 'admin/logs', 'logs', '', 0, 22) in /usr/share/drupal/includes/database.pea

Comments

Comment #1

JonBob CreditAttribution: JonBob commented 25 August 2004 at 18:42

Not sure exactly what's happening there. It is possible it could be related to this pending patch, but it seems different to me.

The menu system should be passing positive integers in for the menu ID field. I'll look into it; in the meantime, try manually clearing the entire menu table to restore the default menu items. That table is not used at all until the first time you visit the menu admin pages.

Comment #2

AlanChandler CreditAttribution: AlanChandler commented 25 August 2004 at 18:56

After this error, there is always one row in the menu table

If I delete the row then I get my menus back.

Comment #3

JonBob CreditAttribution: JonBob commented 25 August 2004 at 19:08

These menu IDs are supposed to be generated in the menu_rebuild() function in menu.inc:

      $new_mid = db_next_id('{menu}_mid');

Is your menu_mid value in the sequences table getting updated correctly? Can you determine (through print statements or whatnot) whether the above statement is being reached when you go to admin/menu, and whether unique nonzero IDs are being returned?

Comment #4

AlanChandler CreditAttribution: AlanChandler commented 25 August 2004 at 20:19

I don't really know php, so tell me if I did the right thing

      $new_mid = db_next_id('{menu}_mid');
      print "NEW_MID:  ";
      print $new_mid;
      print "<br>\n";

Then prints lots of lines with blank value for $new_mid thus

NEW_MID:

NEW_MID:

NEW_MID:

NEW_MID:

NEW_MID:

And then hits the db errors above

Comment #5

JonBob CreditAttribution: JonBob commented 25 August 2004 at 20:26

This means db_next_id() is completely failing to do what it should. I'm not sure why this could be.

Has anyone else experienced this function failing with PostgreSQL? Adrian?

Alan, are you using table prefixing or anything else slightly unusual?

Comment #6

AlanChandler CreditAttribution: AlanChandler commented 25 August 2004 at 20:52

I just discovered that mysql uses a table called sequences = presumably in order to create this sort of thing. The database.pgsql file has a comment that says that postgres doesn't do it that way - and probably means should have some form of CREATE SEQUENCE type command related to the "menu" table (I notice there is a similar one related to the "users" table)

Ultimately, I think this bug is just a manifestation of this

Comment #7

AlanChandler CreditAttribution: AlanChandler commented 25 August 2004 at 21:10

Watchdog reported that there was a missing relation menu_mid_seq so I did a CREATE SEQUENCE menu_mid_seq INCREMENT 1 START 1;
statement on the database, and low and behold - I have a menu table with a whole range of values in them, showing how the menu should be built.

Only problem; The selection of administer/menu never completed - the browser just hung, and now I get absolutely no menu at all in which to try and navigate myself back to the site.

Comment #8

AlanChandler CreditAttribution: AlanChandler commented 25 August 2004 at 21:29

OK - deleted the menu table again and got menus back. Error report that caused the hang is

warning: Invalid argument supplied for foreach() in /usr/share/drupal/modules/menu.module on line 344.

Comment #9

AlanChandler CreditAttribution: AlanChandler commented 26 August 2004 at 19:49

OK - now seems to be working. I put some print statements in to see what values are being assigned to "mid" in the database, and they seem to now be from 40 upwards. This presumably means that when you delete rows in the menu table the sequence menu_mid_seq does not go down again.

It definately did not work last night, so I suspect the previous hiccup was related to not initialising mid to a high enough number to start with.

How these menu arrays work is mind numbingly difficult to figure, but I think this piece of code which initialises the menu array

  $_menu['items'] = array(
    0 => array('path' => '', 'title' => '', 'type' => MENU_IS_ROOT),
    1 => array('pid' => 0, 'path' => '', 'title' => t('Navigation'), 'weight' => -50, 'access' => TRUE, 'type' => MENU_IS_ROOT | MENU_VISIBLE_IN_TREE)
    );

is using up mid 0 and 1, so perhaps having database records with the same mid (ie 0 or 1) could be the problem. In which case, my

CREATE SEQUENCE menu_mid_seq INCREMENT BY 1 START 1; in database/database.pgsql needs to start at a higher number. Can someone who does understand this comment?

Comment #10

JonBob CreditAttribution: JonBob commented 26 August 2004 at 19:57

I don't know how sequences work in postgres, but you are correct that the lowest number used should be 2. This is accomplished in MySQL by inserting a value of 1 in the menu_mid sequence, so that the first time it is called it is incremented to 2.

You're also correct in thinking that IDs are never reused. This behavior ensures that, for example, node IDs don't get recycled, which could lead to confusing URL situations.

Comment #11

adrian CreditAttribution: adrian commented 8 September 2004 at 09:26

Assigned:

Unassigned

» vertice@www.drop.org

I am getting the second problem at the moment. (ie: browser timeout with a message stating invalid use of foreach()). 'mid' should be assigned using the db_next_id("{menu}_mid") function .. which should call "select nextval('public.menu_mid_seq')::text" in postgres.

When you declare a field as serial , it implicitly creates the right sequence, and sets the default to nextval('public.table_field_seq').

The database.mysql has a weird entry tho :


INSERT INTO sequences (name, id) VALUES ('menu_mid', 1);

maybe we need to explicitly create a sequence for the menu then? I did a " create sequence menu_mid_seq start 2 increment 1;", and it's still not working.

Comment #12

adrian CreditAttribution: adrian commented 8 September 2004 at 09:49

I made a change to postgres.pgsql which does the same as the above mysql line ..
namely :

  ALTER SEQUENCE menu_mid_seq RESTART 2;

And it works fine. Except, entries 1 and 2 aren't in the db at all (is that a problem?)

Also. updates.inc seems to have this fixed (atleast with my updated copy) , whereby it calls db_next_id("{menu}_mid"); after creating the table, which essentially pushes the first value to be assigned up to 2, not 1.

since it's a 1 line patch to a file I have already modified, i am going to add it to the latest compatibility patch.