Naturally, I ran into this problem in the middle of the night when the client was asleep and I had no access to the Magento administrative interface. The short story is that after running a re-index of the site, the home page began returning a 404 error status. A bit of Googling indicated we were likely running into a problem where something had an empty URL rewrite that caused the home page to rewrite to a non-existant page. Due to client privacy concerns, I cannot just copy and paste the SQL queries I ran along with their output, but I will provide what I can.

First, I had to find the URL rewrite that had an empty request path. This is the one that was causing the trouble.

mysql> SELECT * FROM `enterprise_url_rewrite` WHERE request_path = '' ;

At first, I just tried to add a request path. Unfortunately, a foreign key constraint was causing me issues (ERROR 1451).

mysql> update enterprise_url_rewrite set url_rewrite_id = '/broken' where url_rewrite_id = <INTEGER> ;
-- <SNIP>
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

So, I decided to remove the other end of the foreign key so that I could update this row and get the site fully functional again.

mysql> delete from enterprise_catalog_product_rewrite where url_rewrite_id = <INTEGER> ;
Query OK, 1 row affected (0.01 sec)

mysql> update enterprise_url_rewrite set url_rewrite_id = '/whoops' where url_rewrite_id = <INTEGER>;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

After that I had to clear the Magento full page cache in order to get the home page working again. This site uses file storage for sessions and caching, so I just had to rm -rf var/full_page_cache (in the Magento top-level directory).