I have had this setup working for years, but all of the sudden today, MySQL crashed, and now I’m having trouble with every database on my external drive on my Mac.
It is currently running MySQL 8.0.21, and that update was done 3 months ago. Prior to that is when I say “working for years” since verstion 5.6/5.7. I haven’t updated the OS recently either — it has been running Catalina for a while.
Initially after the crash, the databases could not be recovered automatically by InnoDB. When it started up, it had lines for all of the tables in every one of those DBs that mentioned they could not be recovered because they were “in an unprotected location.”
Eventually, I figured out how to change the permissions for the users on the external drive to match those expected by mysql, and put this extra location in innodb_directories config setting, and it starts up OK.
However, now, when trying to select data, it tells me Tablespace is missing for table ... and there is a new error in the error log:
Operating system error number 1 in a file operation.
Error number 1 means 'Operation not permitted'
Cannot open datafile for read-only: ... OS error: 101
Not sure why it’s giving me both error 1 and 101, but according to perror, 1 is indeed operation not permitted, and 101 is STREAM ioctl timeout.
As for “not permitted” — the permissions were actually more lenient on these folders. drwxr-xr-x vs. drwxr-x--- (so, world readable and executable) and the ibd files within them are similarly more lenient: -rw-r--r-- vs -rw-r-----.
I don’t even know where to start with error 101, except to try to clear error 1 first, and see if it goes away. The drive is absolutely accessible, even by mysql, which could read it to start up, as best I can tell (since the errors regarding them being unprotected went away).
I decided to chmod o-r and chmod o-x to make those folders and tables match exactly the permissions on the ones that worked. I restarted mysql to see if that made a difference in its ability to read those tables in the databases on the external drive, but it gives the same errors.
I moved a database folder that was working from the main drive to the external drive. It continued to work, until I restarted MySQL. Then it started up as normal, but when selecting data again it gave the same error as before. I moved a database from where it didn’t work on the external drive, to the main MySQL folder, and restarted MySQL, and it works fine. Move it back, and we get the same errors.
MySQL runs under the same user account that I am logged in as, so I don’t see why it would have an issue with any kind of permissions related to the user account.
Finally, I tried adding mysqld and mysqld_safe to Full Disk Access in MacOS and restarting MySQL, in case MacOS was blocking it at the program level, again with no luck.
I am out of ideas. Anyone have anything else I can try?
