Bookmark comments are back

The end of last week was pretty exciting for linkaGoGo. I started to get the first signs of it on Thursday when I got a support email from a member who wanted to publish some folders using Javascript and had some trouble doing that. When investigating it I noticed that the comments on the bookmarks in his folder consisted of only weird characters. I thought it being an anomaly.

The next morning I got two additional support emails one saying his bookmark comments had gone and one indicating he has weird characters in his bookmark comments.

I then checked my own bookmarks and also my comments were gone and it showed some weird characters and after some checks on the database it showed that almost all comments were gone. It looked like to comments column got corrupted. This by itself is weird, as “normally” a whole database table gets corrupted not just a column.

So I started the process to restore the comments on Friday and based on the backups had most of the bookmark comments restored Friday night. Then on Saturday I was able to restore the rest of the comments.

Then I began to analyze what might have happened, I had checked the database but there was no sign of corruption. So then I thought of a bug in my code that could have accidentally wiped out all of the comments instead of just one. I could not find a cause in that either.

Then I remembered that on Wednesday afternoon the database had been slow for a while. So I thought: If there was an update that wiped out all the comments it must taken quite a while to go throught the millions of bookmarks. So I analyzed the slow queries log and there it was:

# Time: 070321 15:06:57
# Query_time: 446 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET timestamp=1174507617;
update bookmark_table set comments_column = 'here bookmark_id = number;

If you know SQL then you notice that there is no “where clause” and thus operates on all the rows. It seems that the “where clause” somehow had been cut off and moved in the assignment part and it also has no ending quote. The query had taken 446 seconds, that is 7 minutes and 26 seconds.

Further analyses showed that the sql contained non-printable characters (asci numbers less then 32) and had corrupted the sql that was generated by the JDBC API I was using, and in this case had mangled it in such a way that it was still valid sql but very destructive.

To prevent this from happening again the code nows clean up all the data by replacing all non-printable characters by ‘#’ before it gets send to the database.

I hope you didn’t even notice,

Gogolian