Hi All,
I was reading a Jeff Attwood post on ServerFault here (quite a long one, but not obscenely long) and saw that the last part of the question got cut off:
Cut off text http://www.41085.org/SF-1.jpg
When viewing the revision history, the full text is intact:
Full text intact http://www.41085.org/SF-2.jpg
And no, the text had not been deleted at a later date...
This appears to happen on both Questions and Answers.
I think I know what that is. I used a SQL server .NET CLR regex to un-nofollow internal links in posts, and I suspect it has a maximum length based on the SQL data type it is cast to.
Hopefully that was the rare post that was
extra long
contained internal site links
otherwise, there may be others like it out there..
The revision history will contain the actual post in any case, so doing an edit no-op (inserting a space) will restore the truncated content. Not an easy thing to query for, though, because the rendered HTML (in the root post), and the Markdown (in revision history) will always be different lengths.
For those who want to check their lengthy answers, you can do so at statoverflow, with a request similar to:
SELECT ParentID, LENGTH(Body) AS length, Body, LastEditDate FROM posts WHERE PostTypeId = 2 AND OwnerUserId = 6309 AND LENGTH(Body) > 5000 AND Body LIKE '%http://stackoverflow.com%' ORDER BY length DESC LIMIT 200
Replace '6309' by your userid, and 5000 by "the proper limit"
CAVEAT: statoverflow is based on the July dump, not the August public dump, meaning there is a all month of answers, also potentially affected (because edited before August 7th). All the following numbers (of answers truncated or users affected) can be actually higher.
Since my 2 initial truncated answers reported in this (duplicate) MSO question, I did found similar problems (i.e. "answers truncated") with:
They all:
If I consider this SO answer with only 4480 characters (in the HTML Body), it has not been truncated (even though it does contain a internal link to stackoverflow).
So the limit to consider may be 5000 characters.
Warning: The previous SQL request may miss some other truncated answers which contain internal stackoverflow links, but for which the HTML rendering truncates said link (the string "http://stackoverflow.com" would not be present in "Body")
So you should also check any post of yours with more than 5000 characters, without "stackoverflow.com"
SELECT ParentID, LENGTH(Body) AS length, Body, LastEditDate FROM posts WHERE PostTypeId = 2 AND OwnerUserId = 6309 AND LENGTH(Body) > 5000 AND Body NOT LIKE '%http://stackoverflow.com%' ORDER BY length DESC LIMIT 200
How many potential answers are affected?
SELECT COUNT(*) FROM posts WHERE PostTypeId = 2 AND LENGTH(Body) > 5000 AND Body LIKE '%http://stackoverflow.com%'
There are 252 "potentially truncated" answers in StackOverflow alone.
You can list them with:
SELECT ParentID, LENGTH(Body) AS length, Body, LastEditDate FROM posts WHERE PostTypeId = 2 AND LENGTH(Body) > 5000 AND Body LIKE '%http://stackoverflow.com%' ORDER BY length DESC LIMIT 300
Most of them (not all, because they have recently been edited) are truncated:
Note: the following SQL request seems to indicate 171 users potentially affected by this "truncated message" issue (which includes, again potentially, more than 200 answers)
SELECT OwnerUserId,COUNT(*) AS n FROM posts WHERE PostTypeId = 2 AND LENGTH(Body) > 5000 AND Body LIKE '%http://stackoverflow.com%' GROUP BY OwnerUserId ORDER BY n DESC
I've been playing with this today, and I found three things:
http://stackoverflow.com showing up in the "body" column somewhere. It should be safe to leave that in.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:
368858 380819 429995 78756 1046016 788084 242996 717725 841231 470942 683273 1154571 553264 1106741 375881 279860 891681 450835 669619 243387 402283 945288 474584 1059574 784417 1078412 823138 1167060 1001569 1181533 47394 379371 219827 359763 146271 181050 814627 657751 189156 688325 387783 904353 749852 922402 1149611 271686 815443 489425 323346 1041266 432154 1214621 696378 631852 535385 368750 543995 1173912 796246 415035 961601 1151658 582412 764710
It happened to an answer of mine, and I found this post when I came here to report it. The good news is that editing the post and adding a single space was enough to make it come back to life.
The post was: https://stackoverflow.com/questions/1156572/evaluating-mathematical-expressions-using-lua/1157717#1157717 in case anyone wants to know.
I found this on one of my questions, too, but the work-around is pretty simple.
I'm posting now because this seems like something that we should be able to test for and fix: just use the dump to run a query for all posts where the length > N and the last edit is before Aug 7.
