There seems to be two, somewhat group-specific although not equally wrong – general opinions on this:
The “Crossfit”-group, for the people who proudly wear the “No Pain No Gain”- tshirts and think that it has to hurt to work. All that nonsense; you are either one of those or you have heard it. The former is obviously worse and this view undoubtedly represents the “most wrong” opinion.
The “Personal Trainer”-group, for the people who have attended at least one expensive course/workshop/coffee meeting. Mantras such as “do not move into pain” and “pain is bad, mkay” rule the atmosphere of beliefs, and – not unlike the CF-group, they take pride in being part of this… better-knowing group of educated individuals. Once upon a time I too probably belonged somewhat to this group, so – because of social “reasoning” – that makes it okay for me to say it.
If you noticed the SP-ref and the sarcasm at the end there, there might be hope for you yet.
Now to the point. Both groups are most likely wrong.
A 2017 meta-analysis titled “Should exercises be painful in the management of chronic musculoskeletal pain?” looked at both the acute and long-term effect of exercises where pain is allowed/encouraged compared with non-painful exercises. Based on the conclusion shown below, it would appear that both groups are wrong and that a moderate and controlled (in time) level of pain during (rehabilitation) exercises is okay, and perhaps even something to strive for. Good news for the sadists and masochists out there.
Conclusion: Protocols using painful exercises offer a small but significant benefit over pain-free exercises in the short term, with moderate quality of evidence. In the medium and long term there is no clear superiority of one treatment over another. Pain during therapeutic exercise for chronic musculoskeletal pain need not be a barrier to successful outcomes.