Manhandle that database back into shape

A short tale of how I got the links page working after a Weblinks module upgrade broke it.

The ideal solution would be to track down the exact problem, posting issues and working with the module's maintainers, finally squaring everything away with the current module version. However, the issues page doesn't show anyone else having trouble with the module; the problem may be an obscure glitch related to my frequent abuse of this site for experimentation purposes. More importantly, at the moment I'm short on time to fiddle with this. I decided I'd just go back to the previous version of the module and be done with it.

Here's the process, including an unexpected problem necessitating light database vivisection:

Getting ready

I had one other site (Site B) using Weblinks and running off the same Drupal 6 installation. Not surprisingly, its weblinks stopped working properly when I updated the module but didn't run update.php. I avoided that update for obvious reasons (it had left Drupalace.com's weblinks broken).

It would have been interesting to run update.php on Site B and see whether Weblinks broke there too, but that site isn't meant as a punching bag like this one, and I really just wanted a quick fix for Drupalace.com via a module downgrade. 

Fortunately, back when I installed Weblinks 6.x-2.1, I didn't trash the old module; I moved it to a directory named "uninstalled modules". So it was readily available for the downgrade process. This is good practice for any module upgrade: Keep the old version, in case you need to quickly jump back. 

Diving in

The steps I took, for the possible edification of other beginners at poking database innards:

1. Back up the site's database (or in this case, both sites'). Really important.

2. Inside the Drupal installation, replace Weblinks 6.x-2.1 with the previous version I had used, 6.x-1.5. 

3. Check the weblinks page on Site B. With its un-updated database, it should revert to proper behavior now that the previous module is back in place... Yep, its weblinks are back in action. Good!

4. For kicks, see whether weblinks now work on Drupalace... No. Its updated database recoils from the old module, spewing error messages. Here's a taste:

user warning: Unknown column 'bw.weight' in 'field list' query: SELECT DISTINCT(n.nid), bw.weight, n.title FROM node n INNER JOIN node_revisions nr ON nr.vid = n.vid INNER JOIN weblinks bw ON bw.vid = nr.vid LEFT JOIN term_node tn ON tn.nid=n.nid AND tn.vid=n.vid WHERE n.status = 1 AND tn.tid IN (116) ORDER BY bw.weight ASC, n.title ASC in /xxxxx/sites/all/modules/weblinks/weblinks.module on line 1142.

5. For further giggles, run update.php on Drupalace and see what happens. Just a bunch of errors like

FAILED: ALTER TABLE {weblinks} DROP tid

No surprise there; you generally expect update.php to do good stuff when upgrading, not downgrading, modules. 

6. Time to guess where the damage is. From clues like the above, it's pretty likely that the culprit is the weblinks table in the database. Simple plan of attack: Nuke existing weblinks table, import weblinks table from old (pre-Weblinks 6.x-2.1) backup of database. Drink beer. 

However:

7. Head to phpMyAdmin. Try to import old database backup into a new empty database, so I can export just its weblinks table for subsequent import into Drupalace. But... despite many attempts, phpMyAdmin only runs forever without importing the database. Possibly a temporary issue with the hosting service. Something to look into soon. Right now, I want to finish this fix somehow.

8. Isn't there a way to pluck out and import just the weblinks table from the saved database backup, rather than try to import the whole database backup? There probably is, if I start searching and asking. But I've got short time to play with this and want a fast fix, dagnabbit... 

9. I check the contents of Drupalace's wayward weblinks table, which looks like this:

Peeking inside (via the 'Browse' tab), things appear sound, in that each record has values in the nid and vid fields, proper URLS in the url field, and so on. So maybe if I just find whatever changes Weblinks 6.x-2.1 has made, and manually set them back to their 6.x-1.5 status, I'll rescue the site. And then drink beer. Sounds like a plan.

10. I compare the above table with the law-abiding weblinks table from Site B's database. That looks like this:

Sure enough, there are differences. For one, Weblinks 6.x-2.1 clearly added several new fields, like urlhash and last_status_info. But new fields shouldn't cause trouble for the old module, so I won't worry about them for now. More importantly, Drupalace's updated table had lost a field, weight. And a couple other fields common to both tables showed changes in their Type, Attributes, Null, or Default values.

11. Fumbling with phpMyAdmin, I add a weight field to Drupalace's weblinks table. (It doesn't sound like a vital field to me, but I'm not making judgment calls today.) Then I change values in Drupalace's table (using the little 'Change' pencil icon) so they match those in Site B's table. For example, I change Type of the last_click field from the newfangled datetime to the comfortable old int(10). 

Note that (for me) this isn't a process of necessarily understanding these table values. The mental workings aren't running much deeper than "do whatever makes the Drupalace table look like the Site B table. Yeah, that's it." (This, not Shakespeare, is what you get when you set a monkey before a keyboard.) 

Finishing the changes, Drupalace's table now looks like this (still leaving in extraneous new fields for now):

12. Final step: See whether Drupalace's weblinks page now works like it should. It does. As expected.

That's it. It's all piddling stuff to the pros, who could perform (and explain) the procedures with much more ease. Or who would have an even better method to fix things. Or who wouldn't have run into trouble with the module update in the first place.

But as a user who still tends to shy away from the deep innards, even mild hackwork like the above makes me feel like I've picked up a hickory club and gone all Buford Pusser on that database. It's heady stuff. The point for beginners is: With minimal knowledge of database tools like phpMyAdmin, and a site you're willing to hack, and diligent backups, you can poke around a database and learn to cobble some handy fixes. 

Now step aside. I'm still carrying the hickory club, and I want that beer.