I tried to post the following comment to this SO post and got the orange box saying
An error occurred during comment submission.
It seems you cannot update a table from a trigger originated while updating the same table (understandably, mysql puts a lock on the table). But `ON UPDATE CASCADE` will anyway set the `parent_id` to `NULL`. So if an entry has a valid level (say 3) but its `parent_id` is null, it means that it is in fact a top level entry. Some tweaking might be required in the code - but I think you can rely on this behavior. If parent is null, it is the root no matter what the value of level is.
At first I thought it was network or proxy issue, but other test comments worked fine. When I tried to post that comment on other threads I got the same error. Then I tried to post it line by line and apparently the following line is the villain.
But `ON UPDATE CASCADE` will anyway set the `parent_id` to `NULL`. So if an entry has a valid level (say 3) but its `parent_id` is null, it means that it is in fact a top level entry.
From what I observed, you cannot post this as a comment to any post in SO or meta or SU. Interestingly, if I misspell `UPDATE` in the `ON UPDATE..` part, I can submit it - but I can't fix it by going back and editing the comment.
Did I find a bug in SO code-base.... what's the price for that?
Update: I originally tried with backticks - just now tried again without backticks; both are not working. As you can see, others are able to post the same comment on this thread, but not me :(
If it'd help, here is the source of the AJAX request, copied from Firebug:
The response tab in Firebug is empty.
Update: I guess Jeff is right about the proxy trying to protect me: I cannot post the following either:
ON UPDATE CASCADE set parent_id NULL
But I can post
ON UPDATE CASCADE will anyway set the parent_id to NULL