tl;dr: If you see weird errors when using or formatting a drive internally on a Mac (especially after upgrading to a newer and/or faster SATA hard drive), it could mean the SATA cable needs to be replaced.
Who would've thought such a tiny cable could cause so many problems?
I have an older Mac mini (mid-2011 i5 model), and I use it as a general media server and network backup. It handles Time Machine backups for two other Macs, it has about 20 TB of external storage connected, and I also use it as a 'home base' to store all my Dropbox, iCloud, and Photos content locally, and store an extra Time Machine backup of all that. I'm a little nutty about backups... but I haven't lost a file in two decades and I don't want to start now ;-).