CODEWITHSUNDEEP
mysqlpartitioningpartition
Rakmo
Rakmo

Reputation: 1982

In MySQL, data is still accessible after moving the partition directory

My partition schemes looks something like:

ALTER TABLE my_table
    PARTITION BY RANGE (integer_field) (
    PARTITION p0 VALUES LESS THAN (100) DATA DIRECTORY = '/my_location/partitions/p0'  ,
    PARTITION p1 VALUES LESS THAN (200) DATA DIRECTORY = '/my_location/partitions/p1' ,
    PARTITION p_other VALUES LESS THAN (MAXVALUE) DATA DIRECTORY = '/my_location/partitions/p_other'
);

As expected, data is being stored properly into the partitions, and into the proper directory.

Problem:

Now when I remove/move the directory from the location, like mv /my_location/partitions/p0 /some_other_location/ , the directory gets moved successfully, but data is still query-able from MySQL shell, even after restarting the shell.

My Solution:

To get it working as expected, after moving the directory including the .ibd file, I had to drop the partition explicitly:

ALTER TABLE my_table DROP PARTITION p0;

This removed the partition from the scheme as required and also cleared the data, verified it by again querying the same data.

Assumption / Understanding:

I think that MySQL is caching the data, not sure where and why exactly, which makes it query-able even after partition directory is moved away. Definitely cache is not at connection level as I closed and reopned the shell.

Question:

I expected the data to disappear as soon as the directory p0 was moved away. Is it really necessary to run the drop partition statement each time the directory is moved?

Constraints:

It is sure that p0 directory is moved away only when the p0 partition is no more used. So there will not be any more data required to enter into existing p0 partition

MySQL: 8.0.19

Upvotes: 0

Views: 464

Answers (2)

Rakmo
Rakmo

Reputation: 1982

As pointed by @Rick James and @Solarflare, moving around the ibd file while it is still in Open state for MySQL does behave in weird way, and tablespaces gets messed up. Following their guidance, and the MySQL Docs, here is the final approach that successfully worked for me (Possibly, the right way to do it):

  1. Lock the table

    FLUSH TABLES my_table FOR EXPORT;

    This prevents any update/write operation to the desired table and makes the ibd file safe to copy. Additionally, this creates a .cfg file. These steps are very well explained in MySQL Docs

  2. Once the table is "Locked", copy the .ibd file to the desired location for archival. PS: Do not move / delete the source file yet.

    cp -r /my_location/partitions/p0  /some_other_location/

  3. Unlock tables to be able to alter the partition

    UNLOCK TABLES;

  4. Drop the required partition safely. This also informs the tablespaces.

    ALTER TABLE my_table DROP PARTITION p0;

    Note that this statement leads to removal of partition, plus the data corresponding to that partition.

Upvotes: 0

Rick James
Rick James

Reputation: 142366

Windows or not?

Did you restart mysqld?

mv on Linux (and close relatives) is really a "rename". And, assuming the target is on the same filesystem, that rename can even involve a different directory.

When a program (eg, mysqld) has a file (eg, the table partition) "open" mysqld, it has control over it -- even if you run off to a shell and rm the file!.

I suspect that when you restart mysqld (for any reason, including reboot), the "data directory" will become messed up.

Aside from the filesystem's partitioning, you must tell MySQL when you "archive" a partition. Read up on "transportable tablespaces" for the version you are using. Here's a writeup for 5.6; 5.7 has some improvements.

I don't see the advantage of using a filesystem partition. With "transportable tablespaces", you can disconnect a MySQL partition from a PARTITIONed table. This turns the partition into a table. Then that table can be deleted, renamed, copied, etc, without impacting the partitioned table. Search for "transportable" in http://mysql.rjweb.org/doc.php/partitionmaint ; there are some links.

Upvotes: 1

Related Questions