The following is an interesting combination of the present perfect in the first clause and the present simple in the second. They supposedly refer to the same experience. Why is this mismatch okay? Any logical explanations?
Have you ever been so close to achieving your goal but things suddenly all go wrong?
The short answer is that English speakers are not very strict with tenses, so the "rules" can be quite flexible.
In fact, your sentence could be written with consistent tenses1:
Have you ever been so close to achieving your goal but things have suddenly all gone wrong?2
However, because the first clause seems to establish a specific time (i.e., when the person has been close to achieving a goal), the second clause seems to work better with a simple (non-perfect) tense. It could even work well with the simple past tense:
Have you ever been so close to achieving your goal but things suddenly all went wrong?
Here are some more examples in which it's unclear whether the first clause establishes a specific time so that the second clause could be in the present perfect, simple present, or simple past:
I've tried to whistle that tune several times, but I've always gotten it wrong / but I always get it wrong / but I always got it wrong.
I've often tried to sleep when I haven't been tired / when I'm not tired / when I wasn't tired.
I've never mixed red with blue so that they've formed purple / so that they form purple / so that they formed purple.
1 For simplicity, I won't distinguish among "tense", "aspect", etc.
2 I think that this would read better with "when" instead of "but" (no matter which tenses are used), but that's a separate issue.
Have you ever been so close to achieving your goal but things suddenly all go wrong?
The right answer here is that you have two independent clauses in the sentence separated with a conjunction (but). Another example would be when someone gets hired at a company, they might be introduced thus on their first week:
Pat has worked as an assassin for a variety of organizations and now they are bringing their hands-on personnel skills to our HR department.
Or:
Pat can take this assignment because he's done this kind of work dozens of times before.
The second clause I the first example I give can be expected to be (and is) present continuous because they are actively in the process of joining the company.
Our friends at the Cambridge Dictionary provide support for this in British English grammar in their article on clauses, but they don't provide a rule along the lines of "if (x situation), then verbs may have mismatched tenses" that I found in my looking for one.
If one is looking for a hard-and-fast rule for why tense mismatch is allowed in some cases but not others, it might be possible to find a book from what's seen as an authoritative source describing it, but the English language is descriptive, rather than prescriptive. There's no single governing body like the Académie Française (French Academy), which has authority over "proper" French, or the Asociación de Academias de la Lengua Española (Association of the Spanish Language), which are a collection of similarly minded organizations around the world that focus on maintaining the consistency of the Spanish language.
Rather, Standard English just kind of wings it, because there's 1) there's no single Standard English, and 2) no single place that (successfully) defines rules for it. Frankly, something in English is grammatically correct once enough people use it that it enters regular usage, which can be frustrating for English language learners -- but is also a pretty egalitarian way of operating!
If you're interested in the history of how English came to be and why it's so loosey-goosey, I highly recommend The Last Lingua Franca: English until the return of Babel by Nicholas Ostler, as it does a deep dive of why English became what it is, while also looking at other languages (namely Latin) that have served the same purpose and have similar origins.
