« Paul Graham on Great Hackers | Main | Building More Secure OSS with OSS »

July 28, 2004

Aha Moment: Restoring Movable Type Category Assignments lost in Upgrade

This morning I got back all the Movable Type category assignments I had made for entries that were lost when I migrated from BDB to MySQL in 2.661, and then dumped into MySQL tables for MT 3.0.

The aha moment happened while shaving: When I moved my entries from BDB to MySQL all the entry_category_id fields were null, which I chalked up to conversion problems. A bit later, after I had forgotten about the lost category assignments I was trying to manually (at the MySQL shell) change a category and discovered this table mt_placement. MT used to keep categories in the mt_entry table, but at some point they started putting them in mt_placement so a person could assign multiple categories. Wasn't until this morning that I connected my recent fiddling with the lost category assignments from a month ago.

Sure enough, the mt_placement table on the 2.661 database has all the assignments. Since I had done a number of manual reassigning entries to categories I had to make sure that anything I dumped from the 2.661 database into the 3.0 database wouldn't overwrite or duplicate records. A quick query in the 3.0 mt_placement database gave me a list of entry_ids to exclude. I ran these three commands and restored 351 category assignments that were lost:

In 2.661 database on old server:
select placement_entry_id, placement_blog_id, placement_category_id, placement_is_primary into outfile '/tmp/placement.tsv' from mt_placement where placement_blog_id in (<blog ids>) and placement_entry_id not in (<ids with existing category entry in new weblog>);

scp /tmp/placement.tsv new.server:/tmp/.

In 3.0 database on new server:
load data infile '/tmp/placement.tsv' into table mt_placement (placement_entry_id, placement_blog_id, placement_category_id, placement_is_primary);

As a person who knows MySQL, having MT entry data in MySQL makes solving these kinds of problems so much easier.

Posted by mike at July 28, 2004 9:06 AM