A trainwreck is quite possibly the most terrifying thing to happen to a musician short of being locked in a room with Leonard Bernstein.
In the simplest terms, a trainwreck is a performance fuckup of such a ferocious intensity that both the performer and the audience are left in awe. It is the consequence of one's brain not being able to keep one's hands in check, the result being a messy atonal slide into melodic oblivion.
A trainwreck isn't just a mistake - mistakes can be smoothed over, ignored or (if you're playing something inherently improvisational) toyed with and incorporated into the groove. This particular beast has no real salvation - once the train has wrecked there isn't much to do except start over, slink off the stage in acute embarrassment or, if you're particularly cocky, stand up and bow.
The damn things aren't merely musically fatal; they're alive and have an internal momentum so powerful that the performer is physically incapable of stopping them - once unleashed the musician is no longer really in control - they're driving a car with no brakes whose steering wheel just came away in their hands toward the edge of a cliff.
An audience's reaction to such a violent collision of man and machine is the perfect example of musical schadenfreude.
The term is generally attributed to pianists or drummers because of the percussive nature of those instruments - the event is of such a spectacular nature that it is particularly suited to instruments that require a lot of vigorous movement. It's not that, say, violinists are inherently incapable of causing a trainwreck, it's just that it looks much daintier when it happens.