Description:
Reference Bug #98126 for earlier history of this issue.
I am extremely reticent to mark this bug "critical," but it is the only severity for which the description admits for no workaround.
The procedure for relocating a database on other than the boot drive is documented here:
https://dev.mysql.com/doc/refman/8.0/en/symbolic-links-to-databases.html
Bug #98126 was filed for documentation clarification, because there were two conflicting procedures documented, and neither one worked.
In closing Bug #98126, the responder claims he performed the newly documented procedure with no errors. However, he does not mention whether or not he performed it on MacOS -- I suspect he did not, because it still fails to work, with the precise error previously reported.
Bug #98126 was closed because "it was a documentation bug and we fixed the documentation." However, the capability documented still does not work and there seems to be no other way to achieve the result.
I have been waiting for an actual fix to this for about a year now, and can no longer put off the project that requires a database to be created on bulk RAID storage instead of the internal drive.
How to repeat:
This is a log, from MySQL installation through failure, of all steps taken to reproduce this problem.
It is 100% repeatable.
Installed MySQL 8.0.23 today on a Mojave (10.14.6) machine from which MySQL had been completely
uninstalled with the uninstaller several months previously.
Used https://dev.mysql.com/downloads/file/?id=501470 (macOS 10.15 (x86, 64-bit), DMG Archive)
using legacy encryption (necessary for our client)
server:~ ranch$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'cdt'@localhost identified with mysql_native_password by 'REDACTED';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'cdt'@localhost with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'cdt'@'%' identified with mysql_native_password by 'REDACTED';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'cdt'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
server:~ ranch$ mysql -u cdt -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database anotherdrive;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
server:~ ranch$ mysqladmin -u root -p shutdown
Enter password:
server:~ ranch$ cd /usr/local/mysql
server:mysql ranch$ sudo su
Password:
sh-3.2# ls -al
total 760
drwxr-xr-x 13 root wheel 416 Feb 3 19:33 .
drwxr-xr-x 17 root wheel 544 Feb 3 19:32 ..
-rw-r--r-- 1 root wheel 382369 Dec 11 00:42 LICENSE
-rw-r--r-- 1 root wheel 666 Dec 11 00:42 README
drwxr-xr-x 34 root wheel 1088 Feb 3 19:32 bin
drwxr-x--- 29 _mysql _mysql 928 Feb 3 19:46 data
drwxr-xr-x 5 root wheel 160 Dec 15 11:25 docs
drwxr-xr-x 16 root wheel 512 Dec 15 11:25 include
drwxr-x--- 3 _mysql _mysql 96 Feb 3 19:33 keyring
drwxr-xr-x 17 root wheel 544 Feb 3 19:32 lib
drwxr-xr-x 4 root wheel 128 Dec 15 11:25 man
drwxr-xr-x 34 root wheel 1088 Dec 15 11:25 share
drwxr-xr-x 5 root wheel 160 Dec 15 11:25 support-files
sh-3.2# cd data
sh-3.2# ls -al
total 359280
-rw-r----- 1 _mysql _mysql 196608 Feb 3 19:45 #ib_16384_0.dblwr
-rw-r----- 1 _mysql _mysql 8585216 Feb 3 19:33 #ib_16384_1.dblwr
drwxr-x--- 2 _mysql _mysql 64 Feb 3 19:46 #innodb_temp
drwxr-x--- 29 _mysql _mysql 928 Feb 3 19:46 .
drwxr-xr-x 13 root wheel 416 Feb 3 19:33 ..
drwxr-x--- 2 _mysql _mysql 64 Feb 3 19:45 anotherdrive
-rw-r----- 1 _mysql _mysql 56 Feb 3 19:33 auto.cnf
-rw-r----- 1 _mysql _mysql 637 Feb 3 19:33 binlog.000001
-rw-r----- 1 _mysql _mysql 1436 Feb 3 19:46 binlog.000002
-rw-r----- 1 _mysql _mysql 32 Feb 3 19:33 binlog.index
-rw------- 1 _mysql _mysql 1676 Feb 3 19:33 ca-key.pem
-rw-r--r-- 1 _mysql _mysql 1112 Feb 3 19:33 ca.pem
-rw-r--r-- 1 _mysql _mysql 1112 Feb 3 19:33 client-cert.pem
-rw------- 1 _mysql _mysql 1680 Feb 3 19:33 client-key.pem
-rw-r----- 1 _mysql _mysql 3447 Feb 3 19:46 ib_buffer_pool
-rw-r----- 1 _mysql _mysql 50331648 Feb 3 19:45 ib_logfile0
-rw-r----- 1 _mysql _mysql 50331648 Feb 3 19:33 ib_logfile1
-rw-r----- 1 _mysql _mysql 12582912 Feb 3 19:46 ibdata1
drwxr-x--- 8 _mysql _mysql 256 Feb 3 19:33 mysql
-rw-r----- 1 _mysql _mysql 28311552 Feb 3 19:45 mysql.ibd
-rw-r----- 1 _mysql _mysql 1527 Feb 3 19:46 mysqld.local.err
drwxr-x--- 111 _mysql _mysql 3552 Feb 3 19:33 performance_schema
-rw------- 1 _mysql _mysql 1676 Feb 3 19:33 private_key.pem
-rw-r--r-- 1 _mysql _mysql 452 Feb 3 19:33 public_key.pem
-rw-r--r-- 1 _mysql _mysql 1112 Feb 3 19:33 server-cert.pem
-rw------- 1 _mysql _mysql 1676 Feb 3 19:33 server-key.pem
drwxr-x--- 3 _mysql _mysql 96 Feb 3 19:33 sys
-rw-r----- 1 _mysql _mysql 16777216 Feb 3 19:45 undo_001
-rw-r----- 1 _mysql _mysql 16777216 Feb 3 19:44 undo_002
sh-3.2# stat anotherdrive
16777220 6997930 drwxr-x--- 2 _mysql _mysql 0 64 "Feb 3 19:45:43 2021" "Feb 3 19:45:43 2021" "Feb 3 19:45:43 2021" "Feb 3 19:45:43 2021" 4096 0 0 anotherdrive
sh-3.2# mv anotherdrive /Volumes/Big_Data/SQL-Databases-LIVE/anotherdrive
sh-3.2# ln -s /Volumes/Big_Data/SQL-Databases-LIVE/anotherdrive
sh-3.2# stat anotherdrive
16777220 6998478 lrwxr-xr-x 1 root _mysql 0 49 "Feb 3 20:01:47 2021" "Feb 3 20:01:47 2021" "Feb 3 20:01:47 2021" "Feb 3 20:01:47 2021" 4096 0 0 anotherdrive
sh-3.2# chown -h _mysql anotherdrive
sh-3.2# stat anotherdrive
16777220 6998478 lrwxr-xr-x 1 _mysql _mysql 0 49 "Feb 3 20:01:47 2021" "Feb 3 20:01:47 2021" "Feb 3 20:06:16 2021" "Feb 3 20:01:47 2021" 4096 0 0 anotherdrive
sh-3.2# cd /Volumes/Big_Data/SQL-Databases-LIVE/
sh-3.2# ls -al
total 16
drwxr-xr-x+ 4 cdt staff 136 Feb 3 20:01 .
drwxr-x---@ 21 macsrwe staff 782 Jan 29 02:08 ..
-rw-r--r--@ 1 cdt staff 6148 May 7 2020 .DS_Store
drwxr-x---+ 2 _mysql _mysql 68 Feb 3 19:45 anotherdrive
sh-3.2# stat anotherdrive
16777242 16200609 drwxr-x--- 2 _mysql _mysql 0 68 "Feb 3 19:45:43 2021" "Feb 3 19:45:43 2021" "Feb 3 20:02:10 2021" "Feb 3 19:45:43 2021" 4096 0 0 anotherdrive
sh-3.2# which mysql.server
sh-3.2# # starting SQL server with the preference panel
sh-3.2# exit
server:mysql ranch$ mysql -u cdt -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| anotherdrive |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use anotherdrive;
Database changed
mysql> create table test (id int primary key);
ERROR 1030 (HY000): Got error 168 - 'Unknown (generic) error from engine' from storage engine
mysql> quit
Bye
server:mysql ranch$
Suggested fix:
I have no contribution here.
