The behavior was that http://davidherron.com/book did not list any books, despite a couple hundred pages whose content type is "book". Further navigating to those pages did not show the normal book navigational tidbits. Hurm.
UPDATE: October 2015, The same behavior happened on another Drupal 6 site I still run. The http://visforvoltage.org/book/ page was empty, and the book hierarchy was somewhat missing in action. Many of the individual Book pages did not show their child pages, for example. Details are below. Upon investigation, I found that entries in the menu_links table had disappeared -- the book table itself doesn't have much information, but refers to entries in menu_links to handle the hierarchy. The failure occurred sometime between May 2015 and October 2015. I can't verify beyond that because while I was doing regular database backups, I didn't save all the old backups. I happened to have saved one in May and another in October. Details are below. I wrote a pair of PHP scripts, shown below, to help fix the problem. Once I ran the scripts there was still a bit of manual action of editing individual nodes, to get the information lined up correctly.
I see that in Drupal 7 the book and menu_links tables have an identical (or nearly identical) schema. It means that while I wrote the scripts below for Drupal 6, they might work for a Drupal 7 site that gets a similarly messed up book hierarchy.
Another lesson to learn is the value of having backups. The main cure for this problem came because I simply had some old copies of the database lying around. /UPDATE
A key clue was in the
book_get_books
function. In there is the following SELECT:
SELECT n.type, n.title, b.*, ml.*
FROM {book} b INNER JOIN {node} n on b.nid = n.nid INNER JOIN {menu_links} ml ON b.mlid = ml.mlid
WHERE n.nid IN (". implode(',', $nids) .") AND n.status = 1
ORDER BY ml.weight, ml.link_title
De-drupalize this and run it in MySQL with a select like the following. In the parenthesized list of node ID's substitute a few nid's of book pages. In my case this query returned an empty list. It's also important to consult the book table as it contains the book node hierarchy of the pages.
SELECT n.type, n.title, b.*, ml.*
FROM book b INNER JOIN node n on b.nid = n.nid INNER JOIN menu_links ml ON b.mlid = ml.mlid
WHERE n.nid IN (14, 89) AND n.status = 1
ORDER BY ml.weight, ml.link_title
This SQL query indicates that the menu_links table contains important data about each book node. I don't understand why book hierarchy data is kept in the menu table, but it is.
It's clear from consulting the source that to repair book navigation requires repairing entries in the menu_links and in book tables. The question is what's the best way to do so.
I stumbled across a simple way to proceed. First list the book table entries like so:
SELECT * FROM book ORDER BY bid
The bid column is "book ID" which appears to be the node ID of the parent book. The nid column is the node ID of the specific row, and the mlid column connects it to the menu_links table.
In my case the menu_links table entries were missing for the book nodes. A simple way to recreate the menu_links entry is to simply edit the book outline for the given node. It's not completely foolproof but there is a couple simple steps to the process.
For each nid visit http://example.com/node/nid and edit it's book outline using the 'Outline' tab. In my case it does not show any outline information. For book table entries where nid and bid are the same, those are top level book nodes. In the Outline tab select "create new book" from the dropdown. If nid and bid are not the same instead in the book dropdown select the correct parent book, once you do the page changes to have a second dropdown showing the book hierarchy, and make sure to select the correct parent book item. Then click Save. If nid and bid are not the same and the parent book page hasn't been repaired you should instead be editing that book page.
After Saving the book outline for a book page the menu_links entry is created. In my case a warning message was printed about duplicate entries in the book table. Also the book hierarchy still wasn't correct but it was close.
It turns out the menu_links entry was created with an mlid other than the mlid in the book table. It's very simple to then change the book table entry to have the new mlid value.
UPDATE book SET mlid = #### WHERE nid = ####
The book module code works once the mlid value in book and menu_links table agree.
UPDATE October 2015
The above was written in June 2009. It's 6.5 years later and I'm facing the same problem. The above didn't help me very much, but I have stumbled across a solution anyway.
First, is that I had old copies of the site database laying around. I loaded several until I found one which had the book hierarchy. Since it was 6 months old, the book hierarchies weren't exactly the same between the old site and the current site, but it's close enough to be of use.
Here's the key behavior I found in the site where book navigation was broken:
mysql> select * from book where bid = 13822;
+------+-------+-------+
| mlid | nid | bid |
+------+-------+-------+
| 3445 | 9471 | 13822 |
| 3474 | 9951 | 13822 |
| 6861 | 13822 | 13822 |
| 6862 | 13823 | 13822 |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> select * from menu_links where mlid = 6861;
Empty set (0.00 sec)
mysql> select * from menu_links where mlid = 6862;
Empty set (0.00 sec)
mysql> select * from menu_links where mlid = 3474;
Empty set (0.00 sec)
mysql> select * from menu_links where mlid = 3445;
Empty set (0.00 sec)
The "select * from book where bid = 13822;" query is a way to find all the Book information for nodes within a given book. The "bid" field is the Book ID, and is the Node ID of the top most Node in the Book. Hence, this tells you all the entries in the book hierarchy.
The next set of links are supposed to return the matching menu_links entry, but you can see there are none. Fortunately the old copy of the site does have these menu_link entries.
That means the cure is to copy the menu_links entry from the backup into the current live site. The next step is to edit the Book node, then save it. No need to make any changes, just edit and save. Doing so does something magical, and the book hierarchy navigation starts to align itself correctly, and all is good.
This also means the repair job is to -- one-by-one query the menu_links entries from your backup, copying them to the current site database. This is tedium defined, but it is stitching the book hierarchy back together.
UPDATE November 2015
Well, the manual repair job grew tiresome especially after fixing a couple hundred nodes by hand. The following two PHP scripts take care of copying data from the old backup, from May 2015, which happened to have a working book hierarchy, and patching it into the currently running database.
Since this code makes no attempt to work through Drupal's API, it's imperfect. In fact I did have to go back through the book nodes and edit a few, then save, to get the changes to register correctly.
<?php
$servername = "... server name or IP address";
$username = ".... your database user name";
$password = ".... your password";
$dbold = ".... database name to copy data from";
$dbcur = ".... database name to insert data to";
// Create connection
$connold = new mysqli($servername, $username, $password, $dbold);
// Check connection
if ($connold->connect_error) {
die("Connection failed: " . $connold->connect_error);
}
// Create connection
$conncur = new mysqli($servername, $username, $password, $dbcur);
// Check connection
if ($conncur->connect_error) {
die("Connection failed: " . $conncur->connect_error);
}
$sql = "select * from menu_links where mlid = " . $argv[1];
$result = $connold->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$sqlins = "INSERT INTO menu_links (menu_name, mlid, plid, link_path, router_path, link_title, options, module, hidden, external, has_children, expanded, weight, depth, customized, p1, p2, p3, p4, p5, p6, p7, p8, p9, updated) VALUES ('". $row["menu_name"] ."', ". $row["mlid"] .", ". $row["plid"] .", '". $row["link_path"] ."', '". $row["router_path"] ."', '". $row["link_title"] ."', '". $row["options"] ."', '". $row["module"] ."', ". $row["hidden"] .", ". $row["external"] .", ". $row["has_children"] .", ". $row["expanded"] .", ". $row["weight"] .", ". $row["depth"] .", ". $row["customized"] .", ". $row["p1"] .", ". $row["p2"] .", ". $row["p3"] .", ". $row["p4"] .", ". $row["p5"] .", ". $row["p6"] .", ". $row["p7"] .", ". $row["p8"] .", ". $row["p9"] .", ". $row["updated"] .")";
echo $sqlins ."\n";
$conncur->query($sqlins);
}
} else {
echo "0 results";
}
$connold->close();
$conncur->close();
You configure the database information in the variables above. The assumption is that you loaded the old backup into the same database server as the currently running site. It's also recommended to make a duplicate of your site, and make that the destination database, so you can test the script without risking the live running website. In case it's not obvious, you're on your own with this and I disclaim responsibility for what you do with the above.
The above worked for me in my case, and it might not work for you.
To use this, first run
SELECT * from book where bid = ######;
The "bid" value is the Node ID of a top level Book. Every Book node is represented in the Book table, with a bid value of the Node ID of the top level book containing this node. If that didn't make sense, it will once you run the query. I showed a sample output of this query above. The "nid" column is the Node ID of each node in the book, and you can visit "/node/#####" on your site to see what content page this maps to. The "mlid" column is a reference to a menu_links entry.
As shown above, in my site the menu_links entries were gone. What this script does is copy the menu_links entry from the backup site to your current site. As I say above, once you do that you should visit the /node/##### page then edit the page and save it. Doing so will resurrect something that will then cause book navigation elements to show up. In most cases that was all which was necessary, run the above script then edit/save the book node. Sometimes it didn't work, unfortunately.
After doing a couple hundred of these I came up with a different idea - since I had a couple hundred more book nodes to fix.
<?php
$servername = "... server name or IP address";
$username = ".... your database user name";
$password = ".... your password";
$dbold = ".... database name to copy data from";
$dbcur = ".... database name to insert data to";
// Create connection
$connold = new mysqli($servername, $username, $password, $dbold);
// Check connection
if ($connold->connect_error) {
die("Connection failed: " . $connold->connect_error);
}
// Create connection
$conncur = new mysqli($servername, $username, $password, $dbcur);
// Check connection
if ($conncur->connect_error) {
die("Connection failed: " . $conncur->connect_error);
}
$sql = "select * from book";
$result = $conncur->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$sql2 = "select * from menu_links where mlid = ". $row["mlid"];
$result2 = $conncur->query($sql2);
if ($result2->num_rows <= 0) {
$result3 = $connold->query($sql2);
if ($result3->num_rows > 0) {
while ($row = $result3->fetch_assoc()) {
$sqlins = "INSERT INTO menu_links (menu_name, mlid, plid, link_path, router_path, link_title, options, module, hidden, external, has_children, expanded, weight, depth, customized, p1, p2, p3, p4, p5, p6, p7, p8, p9, updated) VALUES ('". $row["menu_name"] ."', ". $row["mlid"] .", ". $row["plid"] .", '". $row["link_path"] ."', '". $row["router_path"] ."', '". $row["link_title"] ."', '". $row["options"] ."', '". $row["module"] ."', ". $row["hidden"] .", ". $row["external"] .", ". $row["has_children"] .", ". $row["expanded"] .", ". $row["weight"] .", ". $row["depth"] .", ". $row["customized"] .", ". $row["p1"] .", ". $row["p2"] .", ". $row["p3"] .", ". $row["p4"] .", ". $row["p5"] .", ". $row["p6"] .", ". $row["p7"] .", ". $row["p8"] .", ". $row["p9"] .", ". $row["updated"] .")";
echo "... fixing bid=". $row["bid"] ." nid=". $row["nid"] ." mlid=". $row["mlid"] ."\n";
echo $sqlins ."\n";
$conncur->query($sqlins);
}
} else {
echo "... no connold menu_links for ". $row["mlid"] ."\n";
}
} else {
echo "...skipping bid=". $row["bid"] ." nid=". $row["nid"] ." mlid=". $row["mlid"] ."\n";
}
}
} else {
echo "no book entries" ."\n";
}
$connold->close();
$conncur->close();
The configuration is the same as before, as is the assumption that you have a backup instantiated containing a good Book hierarchy and that you want to copy missing menu_links entries to the current database. Again, I have to warn you to first run this on a test site rather than your live site so you can see the behavior for your site. I also have to again disclaim responsibility for what happens to your site. I can simply say the above worked for me.
This script runs through all Book nodes, checking for ones with missing menu_link entries. Any missing menu_link entries will be copied from the backup database to the destination database. So simple, why didn't I think of this before spending a full day manually copying database entries?
This worked to fix the majority of book nodes that were remaining. After running the script I went through the hierarchy of the backup site and current site, finding any book nodes that didn't get fixed properly by this script, and editing each. Generally it took a simple edit/save and the node would be fixed up right away. However in some cases I had to change a book node to be its own book. After that I could then edit it again to reinsert it somewhere in the book hierarchy.