Fixing Drupal's broken book navigation after loosing all books

There seems to be an obscure bug somewhere either in Drupal itself or in one of the modules. I haven't been able to pinpoint it's location yet, but it struck me twice already and damage repair can be utmost time consuming, especially if you do not know what you are looking for. So I guess, I'll write a how-to for it.

The bug affects Drupal's book module, causing the CMS to loose the entire book navigation. That is, going to /book just yields an empty page. The individual book pages are not affected. The nodes themselves stay the way they are. However, the book navigation is not completely wiped from the database. Trying to recreate the outline manually will result in an error message like:

user warning: Duplicate entry '264' for key 2 query: INSERT INTO book (nid, mlid, bid) VALUES (264, 3763, 325) in /htdocs/modules/book/book.module on line 466.


Even though the operation may seem to succeed, it does not. Books stay broken.

The issue cannot be fixed from the webinterface. You have to go down to the database level. The naive approach here would be to simply wipe the "book" table and then try to recreate the outline manually. However, this will fail in a very subtle way, in case you already tried to recreate the outline before wiping. If your book navigation block is set to only show on book pages, you will find it no longer working afterwards, that is, not showing up any more at all.

Time to dive into the matter. First let's look at the anatomy of the "book" table. It consists of three columns

mlid

A reference into the menu_links table and the reason for the problem

nid

A reference into the node table

bid

The node id of the top level node of a book (the starting page)

In essence, the the "book" table's job is just to group nodes, belonging to the same book. The actual navigation structure is kept in the menu_links table. That is, books are technically spoken just menus.
The bug now only affects menu_links, deleting all book related rows there, but leaving the grouping in the book table intact.

Now, two things can happen. If you were quick-witted enough to wipe the book table before manually recreating the outline, you are in the clear. In the more likely scenario of you trying to fix the outline first, you are in trouble. I'll assume the later here, which involves an additional step in damage repair.

What will happen, if you try to re-create a book using one of the now dangling nodes? Well, simple. Drupal will first add a new row with a new primary key to the menu_links table, establishing a new menu (book==menu!). It will then try to add the corresponding grouping entry to the books table and be told by the database, that that entry already exists, resulting in three things:

• You get that fancy red alert box with the warning about duplicate entries
• The database will refuse to carry out the operation, as the combination bid/nid forms a key.
• Since the grouping entry remains unchanged, the mlid column of the "book" table still points to the old index (the one that was wiped by the bug) in menu_links table. In other words, the foreign key "mlid" of the "book" table mismatches the primary key "mlid" in "menu_links".

From here, you are screwed. The mismatching relationship between the two tables will utterly confuse the book navigation block, causing it to not show up any more. Simply wiping the "book" table at this time will do you no good either, since now the menu structure exists, while the grouping does not.

Ok, having explained the problem in great detail, how do you actually fix it? First of all, put your site in maintenance mode and make a backup of your database. Afterwards wipe the "book" table. This can be done by a simple SQL query:

DELETE FROM book;


Next, you have to make sure to get rid of all remaining or recreated menu structures from the menu_links table, that have anything to do with books. Do a

select menu_name from menu_links where menu_name like 'book-toc-%';


All book menus are named like book-toc-XXX with XXX being the nid of the root node. Delete all those menus:

delete from menu_links where menu_name like "book-toc-%";


Finally, you can recreate your book outlines manually. Unfortunately, that's the only way to do it. The menu_links table cannot be reconstructed from the books table due to the fact, that "mlid" is a primary/foreign key in those two tables. The only alternative is restoring a database backup (or at least a backup of these two tables).

UPDATE: Seems like the problem is caused by an old version of Drush (v2.1) and is triggered when checking for updates while no updates are available (and checking for /book while the script runs?). Upgrading to Drush v3.0 solves the problem.