How many versions we have of the BSD OS, how many linux distros,LibreOffice, MariaDB and so on? in open source, forking is more common that you can imagine, is one of the strength of open source, allowing some projects to survive.
No doubt and no dispute that forking is the strength of open source. But how many of these forks you mentioned are still active and considered successful? Not that many. And how many such forks are developed completely isolated from the origin? Probably even fewer.
Requirements on the tech skills to fork a Linux distro are relative low. Most of them just use the same kernel, same GNU tool chains, same set of user-space utilities and libraries, same GUI if it is desktop oriented, and same set of service software if it is for servers. Linux distros update the components all the time. They pull in the latest kernel, toolchains, user-space utilities, libraries and programs etc. to make new releases. I have done this myself back in the days and I don't see things have changed dramatically.
Yes it's a whoe different level of tech skills required to fork the Linux kernel for different purposes, such as realtime, or telco grade, or for massive clusters in Google/Facebook/etc. But do you see who are making and maintaining these forks? Google, Facebook, Telcos, auto makers, etc., i.e. the big boys with the money and talent.
Even these big boys don't carry the weight on their own. They do not maintain their own Linux kernel forks in isolation. They keep sync'ing with the upstream. They push their changes back upstream in the name of sharing. But you can also call that offloading. In this way, you can see that these forks are effectively new branches grown on top of their origins and they keep getting "nutrition" from the main branch.
In both cases of Linux distro and Linux kernel, the forks still live within the ecosystems. Now if your fork is completely cut off from its origin, i.e. the main branch, you will have to stand up and root on your own. It's going to be even more difficult if you are no longer in the ecosystem.
I am not familiar with BSD but I know it has a much smaller user base comparing to Linux. I doubt such a small user base can support many forks to thrive, not to mention forks that grow in dark corners without exchanges with the ecosystem.
MariaDB is a fork of MySQL. I haven't heard of another fork of either. Database is not my field. As far as I know, MariaDB is doing well because Oracel bought up MySQL and everyone disliks Oracle. Some of the main developers of MySQL joined MariaDB even. If your fork can get the main developers of the original project on board, sure you have a better chance of success or even better to surpass the origin. But that does not happen often.