Why you might have lost your entire book outline after upgrading from Drupal 5 to Drupal 6

I'm currently in the throws of upgrading a complicated site (which happens to be my personal blog) to Drupal 6 from Drupal 5. Way back in 2004, I stumbled upon an issue which claimed it was possible to allow multiple parents for a node in a book outline, but that an interface hadn't been built for it. That doesn't prevent people like me from adding an entry in the {book} table, my example being a video of a SkyTrain (Vancouver's elevated rapid transit system) appearing in the "SkyTrain" book outline as well as the "Video" outline. It worked like a charm!

In my first test upgrade (intended to document issues of a real upgrade while keeping the existing Drupal 5 site alive), though, I found that my entire book outline completely disappeared after running update.php. After looking at the related error, I think I know why, and have a "solution", which is to hack book.install.

* user warning: Duplicate entry '6019' for key 1 query: INSERT INTO book_temp (nid, parent, weight) SELECT b.nid, b.parent, b.weight FROM book b INNER JOIN node n on b.vid = n.vid in /var/www/staging/DRUPAL-6/html/modules/book/book.install on line 127.

What happens in the Drupal 6 update is that the installer creates a {book_temp} table, populates the content it needs from {book}, removes the {book} table, sets up an entirely new {book} table and runs a query, then drops {book_temp}. The two DROPs means if you get an error like the above, both {book} and {book_temp} with your outline get dropped.

The solution is, before you start an upgrade, to edit out the following line in book.install when upgrading to Drupal 6.

Change

      $schema['book_temp'] = array(
        'fields' => array(
          'nid'    => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
          'parent' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
          'weight' => array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'size' => 'tiny')
        ),
        'indexes' => array(
          'parent' => array('parent')
        ),
        'primary key' => array('nid'),
      );

to

      $schema['book_temp'] = array(
        'fields' => array(
          'nid'    => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
          'parent' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
          'weight' => array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'size' => 'tiny')
        ),
        'indexes' => array(
          'parent' => array('parent')
        ),
      );

If you want a diff to work with, it looks like this for Drupal 6.3:

--- book.install	10 Jan 2008 18:13:42 -0000	1.20
+++ book.install	3 Aug 2008 21:09:46 -0000
@@ -118,7 +118,6 @@
         'indexes' => array(
           'parent' => array('parent')
         ),
-        'primary key' => array('nid'),
       );
 
       db_create_table($ret, 'book_temp', $schema['book_temp']);

All this does is remove a primary key in the {book_temp} table meaning you can have multiple nids and therefore multiple parents. After upgrading with this modification to book.install, my book outline appears to have fully come over with the upgrade.

Is this common enough to warrant an issue against the Drupal project? At any rate, I wanted to document it here since it seems that nobody had come across it in my searches.