Bug #102476 Procedure for moving database dir to non-boot drive still fails
Submitted: 4 Feb 2021 3:51 Modified: 4 Feb 2021 21:15
Reporter: C D Tavares Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.23 OS:MacOS
Assigned to: CPU Architecture:x86 (Mac Mini, Mojave)

[4 Feb 2021 3:51] C D Tavares
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.
[4 Feb 2021 3:54] C D Tavares
Corrected OS field, supplied version
[4 Feb 2021 8:55] C D Tavares
.
[4 Feb 2021 14:33] MySQL Verification Team
Hi Mr. Tavares,

Thank you for your bug report.

However, it is not a bug.

Your `mv` and `ln` commands are not correct. There are other mistakes in your procedure, as well.

Also, you have not read the entire Reference Manual on the subject of issues with databases that are symlinked ......

Not a bug .
[4 Feb 2021 21:15] C D Tavares
Could you please be less cryptic? This response is extremely unhelpful.
What is wrong with the mv and ln commands? I've been using Unix since 1973 and they look OK to me.
I admit I have not "read the entire manual" on any portion of MySQL. I was unaware this was a requirement. Perhaps you could provide the URL.