Return to Answer

Post Migrated Here from meta.stackexchange.com (revisions)

occurred Jul 22, 2010 at 18:07

Post Made Community Wiki

Source Link

created Sep 4, 2009 at 16:12

Joel Coehoorn

I've been playing with this today, and I found three things:

Even if a link is trunctated, the "body" column shows the encoded html and therefor the href="..." part is still included. So I don't think you can have an internal link without the http://stackoverflow.com showing up in the "body" column somewhere. It should be safe to leave that in.
The LENGTH() function returns the number of bytes, not the number of characters. I haven't checked it thoroughly yet, but I suspect that using CHAR_LENGTH() will have more consistent results as far as that goes.
The bug only effects posts that were last edited on or before August 7th. Anything after then should be okay.

Adding in all that new information, I put together this query:

SELECT 
    ParentID, CHAR_LENGTH(Body) AS length, Body, LastEditDate, CreationDate 
FROM posts 
WHERE PostTypeId = 2 AND CHAR_LENGTH(Body) > 4500 AND Body LIKE '%http://stackoverflow.com%'
AND LastEditDate < '2009-08-08'  AND CreationDate  < cast('2009-08-08' as datetime)
 ORDER BY length DESC LIMIT 200

At this point, that has us down to about 74 records, at least one of which was already corrected. That's small enough we could clean it all up by hand, if I could get another set of eyes on this to check my assumptions and query.

Update
Well that's one big assumption down: statoverflow doesn't have the august dump yet :/

Update 2
I also realized that query only covered answers. I now have the september dataset loaded on my server at home, and I ran an updated query there that should cover both questions and answers. It's still just 96 posts (mixed questions and answers, only question ID shown). That's still small enough to work by hand, if anyone want to take a few here and there. I'm marking this post community wiki so that after someone fixes a few posts they can mark them off the list: