MySQL Bugs: #98126: Documentation discrepancy on relocating databases among drives

Bug #98126	Documentation discrepancy on relocating databases among drives
Submitted:	4 Jan 2020 23:44	Modified:	30 Mar 2020 16:08
Reporter:	C D Tavares	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8	OS:	MacOS (Mojave 10.14.6)
Assigned to:		CPU Architecture:	x86 (Mini 2018)

Description:
Page https://dev.mysql.com/doc/refman/8.0/en/symbolic-links-to-databases.html says:

  On Unix, the way to symlink a database is first to create a directory on some disk where you have free space and then to create a soft link to it from the MySQL data directory.
  shell> mkdir /dr1/databases/test
  shell> ln -s /dr1/databases/test /path/to/datadir

However, page https://dev.mysql.com/doc/refman/8.0/en/create-database.html says:

  Creating a database directory by manually creating a directory under the data directory (for example, with mkdir) is unsupported in MySQL 8.0.

These statements appear to conflict, and the procedure described on the former page fails when tried.

How to repeat:
Precreate a directory on an external drive to hold a new database.
Normalize its ownership and access to the proper values.
Symlink to this new directory from the main data dir of the MySQL installation.
Using an SQL client, create a new database of the appropriate name.

When I attempted to establish the new database in this way, MySQL ignored or avoided the symlink and instead created a new directory with a slightly altered name inside the main data directory on the boot volume.

Suggested fix:
Correct the discrepancy in the documentation; also, replace the non-working protocol on the former page with a protocol that actually succeeds for people who need to locate a new database on a different volume.

Hi Mr. Tavares,

Thank you for your bug request.

I agree that the documentation is not 100 % clear on this issue.

Verified as reported.

If it's not out of line to request this here, it would be nice to have some sort of clarification as soon as possible as to what is the approved and supported method to locate/relocate a database to another volume. The issue is holding up production.

Hi,

This is not a forum where you can seek advice.

But, I have added your request to the internal bug, so our documentation team will look into it.

Posted by developer:
 
The procedure shown at https://dev.mysql.com/doc/refman/8.0/en/symbolic-links-to-databases.html does not work in MySQL 8.0 due to introduction of the data dictionary, which needs to know about directories in the data directory. For that reason, operations that change directories must be performed by the server itself.

I have updated the section with a procedure that works in MySQL 8.0. Thanks for noticing the issue.

Thank you, Paul.

Terminal log of failure of new documented procedure

Attachment: SQL-failure.txt (text/plain), 3.18 KiB.

Sorry, but newly documented procedure does not work, at least in 8.0.18. Uploaded terminal log of what occurs when trying to use a relocated database after scrupulously following new instructions. Please reopen.

Please, always use our latest patch fix release.

I upgraded to 8.0.19, the latest version I could find available at your website, and the error is unchanged.

Is there some other area of your website where I should be looking for a "latest patch release?"

Hi,

No, 8.0.19 is latest patch fix release.

We have corrected our documentation, so that procedure that you reported is changed with the one that has a correct syntax.

Hence, the only change is in the documentation, which you can find on dev/mysql.com.

To summarize what has occurred, you have replaced a documented procedure that did not work with another documented procedure that does not work.

I infer from your recent journal entries that you consider this to be an adequate response to the issue and that your work here is now done. I trust you understand how I might remain unsatisfied with this.

How would you recommend I proceed from this point to obtain a documented procedure that is both satisfactory to you and actually works?

Hi Mr. Tavares,

I have now followed five times directives found in this chapter of our Reference Manual:

8.12.2.1 Using Symbolic Links for Databases on Unix

and it worked like a charm. After I restarted the server I ran these commands:

-----------------------------------------------------------------------------------

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.02 sec)

mysql> use mydb1;
Database changed
mysql> create table test (id int primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+-----------------+
| Tables_in_mydb1 |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.10 sec)

mysql> use test
Database changed
mysql> drop schema mydb1;
Query OK, 0 rows affected (0.05 sec)

-----------------------------------------------------------------------------------

When I exited mysql CLI and I checked, both the originated directory (which I placed in /tmp/) and a symbolic link in datadir were gone. This is expected behaviour, since I dropped the schema at the end of each of my sessions.

Hence, what is it that does not work properly for you and have you carefully followed all steps on that page ???

Also, have your privileges on the path, where database was moved, been correct ?????

In any case, our documentation is now correct.