WL#13352: Innodb: Case insensitive names for partition tables

Affects: Server-8.0   —   Status: Complete

BACKGROUND

MySQL internally uses partition and sub-partition separator strings to generate tablespace name and file name for partitioned tables. These separators are historically in upper case for Linux (#P# and #SP#) and in lower case for windows (#p and #sp#). This creates issues when migrating data directory across windows to Linux or vice versa.

Since the separators are not user identifiers it makes sense to keep them uniform across platforms.

For few identifiers in MySQL, like table and schema name, we allow the names to be case sensitive. This behaviour is controlled by the configuration lower-case-table-names. With lower- case-table-names=1, the identifiers are converted to lower case and it is possible to migrate data directories across case sensitive and case insensitive file system.

Partition/Sub-partition name, OTOH, are case insensitive identifiers similar to "column name".

  1. Partition and sub-partition names are usually generated as p0,p1,p2 ... in lower case.

  2. Such names can also be given by user as "partition PARt_1". In this case we preserve the user case in meta information but use case insensitive collation similar to "column name". One cannot have two partitions named "part_1" and "PART_1" in same table.

Thus it makes sense to use uniform (lower case) for other generated names based on partition names irrespective of lower-case-table-names configuration.

  • partition tablespace name [my_schema.my_table#p#part_1]
  • partition tablespace file name [my_table#p#part_1.ibd]

The current WL is created to solve the issues related to partition naming from root and also resolve the specific issues introduced in previous bug fix.

USER STORIES

  • As an MySQL User I want to use partitioned table across major version upgrade from 5.7.* to 8.0.* with correct data, metadata and innodb statistics.

  • As a MySQL User I want to use partitioned table across minor version upgrade from 8.0.x to 8.0.y (when y > x) with correct data, metadata and innodb statistics.

  • As a MySQL User I want to import table partitions from 8.0.x to 8.0.y (when y > x).

  • As a MySQL User I want to migrate MySQL data directory from case insensitive file system to cases sensitive system with lower-case-table-names = 1

  • As a MySQL User I want to migrate MySQL data directory from case sensitive file system to cases insensitive system with lower-case-table-names = 1

  • As a MySQL developer I want to keep tablespace name uniform in DD cache, innodb dictionary cache and innodb tablespace cache. This would ensure correct MDL locking of tablespace.

SCOPE

This worklog aims to solve all user issues related to alphabetical case differences in partition name and separators.

  1. Handle partition name, partition separator (#p#), sub-partition name and sub-partition separator (#sp#) everywhere in case insensitive manner so that the difference in case doesn't matter for file names on disk and metadata stored in DD. This includes handling of partitioned tablespace file names during import.

  2. Change the default for partition and sub-partition separators to lower case irrespective of platform.

  3. Change the default for tablespace name and file name to store partition name and sub-partition name in lower case.

  4. During upgrade, check and modify partitioned table file names on disk and DD to have lower case names.

  5. During import, check and modify table file names on disk to have lower case names.

  6. During upgrade, check and correct DD partition metadata for issues introduced in previous bug fixes.

  7. During upgrade, check and correct innodb statistics data for issue introduced in previous bug fixes.

Functional

F-1 All partitioned and sub-partitioned tables must be upgraded successfully from 5.7 to 8.0

  • 5.7.* => 8.0.latest

F-2 All partitioned and sub-partitioned tables must be upgraded successfully from 8.0.x to 8.0.y where y > x. In view of the issues introduced in 8.0.14 some specific combination must be tested.

  • 8.0.13 => 8.0.14 => 8.0.latest
  • 5.7.* => 8.0.14 => 8.0.latest

F-2.1 Execute DDL Create/Alter/Drop/Truncate in each upgrade step of F-1 and F-2.

F-2.2 Execute DML Insert/Update and Query in each upgrade step of F-1 and F-2.

F-2.3 Check metadata views for partition name, tablespace name and file names for F-1 and F-2.

  1. information_schema.tables

  2. information_schema.innodb_tables

  3. information_schema.partitions

  4. information_schema.innodb_tablespaces

  5. information_schema.files

F-2.4 Check table name in persistent statistics tables for F-1 and F-2.

  1. mysql.innodb_index_stats

  2. mysql.innodb_table_stats

F-2.5. Check internal DD tables for F-1 and F-2 in debug mode.

SET DEBUG='+d,skip_dd_table_access_check';

  1. mysql.table_partitions

  2. mysql.tablespaces

  3. mysql.tablespace_files

F-3 Import partition tablespace from lower versions in 8.0.latest.

  • 8.0.13 -> 8.0.latest
  • 8.0.14 -> 8.0.latest

F-4 With lower-case-table-names = 1, migrate data directory with partitioned table from windows to linux and verify.

F-4.1 With lower-case-table-names = 1, migrate data directory with partitioned table from linux to windows and verify.

User Interface

  • No external interfaces are changed.

  • The partition name and separators (#p# and #sp#) in Innodb file for partitioned tables would be in lower case irrespective of platform now. Earlier, on Linux, the separators was be in capital (#P# and #SP#). User should not have any dependency on these file names. The names would be reflected correctly in metadata views.

  • Partition and sub-partition identifiers are case insensitive. This is same as current behaviour but needs to be clearly documented.

Design Summary

Handling partition and sub-partition separators

  • Refactor to consolidate all partition name related functionality in a single namespace.

  • Generate always lower case separator and partition/sub-partition name for ...

    Innodb dictionary cache [dict_table_t]
Innodb tablespace cache [fil_space_t]
Innodb space name in DD for partitioned table

  • While parsing partitioned table name in innodb dictionary cache use case insensitive comparison for partition, sub-partition and separators.

Handling upgrade from 5.7

  • During recovery tablespace file scan, identify partitioned table

  • Modify file name to have lower case partition name and separator

  • Update DD with the modified space and file name

Handling upgrade from 8.0.14 onwards

  • Correct innodb statistics table to remove duplicate entry with #p# and #P#

  • Correct DD space name to convert "?" to right tablespace name.

Handling partition tablespace Import

  • Allow partition, sub-partition and separators in any alphabetical case

  • Modify partition table file name to have lower case partition name and separator

Innodb Dictionary Naming Interface

storage/innobase/include/dict0types.h

namespace dict_name {

/* Partition separator in dictionary table name and file name.
constexpr char PART_SEPARATOR[] = "#p#";

/** Sub-Partition separator in dictionary table name and file name.  
constexpr char SUB_PART_SEPARATOR[] = "#sp#";

...

/** Get schema and table name from dictionary table name.
void get_table(const std::string &dict_name, bool convert, std::string &schema, std::string &table, std::string &partition, bool &is_tmp);

/** Get schema and table name from dictionary table name.  
void get_table(const std::string &dict_name, std::string &schema, std::string &table);

/** Get partition and sub-partition name from partition string
void get_partition(const std::string &partition, bool convert, std::string &part, std::string &sub_part);

/** Build dictionary table name.  
void build_table(const std::string &schema, const std::string &table, const std::string &partition, bool is_tmp, bool convert, std::string &dict_name);

/** Build partition string from dd object.
void build_partition(const dd::Partition *dd_part, std::string &partition);

/** Build 5.7 style partition string from dd object.  
void build_57_partition(const dd::Partition *dd_part, std::string &partition);

/** Check if dd partition matches with innodb dictionary table name.  
bool match_partition(const std::string &dict_name, const dd::Partition *dd_part);

/** Build space name by converting schema, table, partition and sub partition
void convert_to_space(std::string &dict_name);
/** Rebuild table name to convert from 5.7 format to 8.0.  
void rebuild(std::string &dict_name);  
}

Updating Files

storage/innobase/include/fil0fil.h

/** Check and rename old style partition file lower case.
void check_and_rename_partition_file(std::string &file_path, bool revert, ib_file_suffix extn, bool import);
class Fil_path {

/** Parse file-per-table file name and build Innodb dictionary table name.
bool parse_file_path(const std::string &file_path, ib_file_suffix extn, std::string &dict_name);
}

Updating DD

storage/innobase/include/fil0fil.h

/** Compare and update space name and dd path for partitioned table.
bool fil_update_partition_name(space_id_t space_id, uint32_t fsp_flags, bool update_space, std::string &space_name, std::string &dd_path);

/** Add tablespace to the set of tablespaces to be updated in DD.
void fil_add_moved_space(dd::Object_id dd_object_id, space_id_t space_id, const char *space_name, const std::string &old_path, const std::string &new_path);

Importing partition tablespace

storage/innobase/include/fil0fil.h

/** Adjust file name for import for partition files
void fil_adjust_name_import(dict_table_t *table, const char *path, ib_file_suffix extn);


Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.