| Bug #94850 | Not able to import partitioned tablespace older than 8.0.14 | ||
|---|---|---|---|
| Submitted: | 1 Apr 2019 9:33 | Modified: | 4 Sep 2019 14:19 |
| Reporter: | sean sean | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.15 | OS: | CentOS (7.4) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | moving tablespace, MySQL, partition, regression | ||
[1 Apr 2019 9:33]
sean sean
[4 Apr 2019 12:24]
MySQL Verification Team
Hello Sean Ren, Thank you for the report. I tried to reproduce this issue at my end on OL7 with dummy schema but not seeing any issues while copying tablespaces from one MySQL instance(8.0.13 or 8.0.14) to another (8.0.15). Could you please provide source and destination environment details(OS), sample schema(create table statement and sample data), configuration details(unaltered my.cnf/my.ini) from source and target instances? Thank you. I'll be shortly joining my test activity log for your reference. regards, Umesh
[4 Apr 2019 12:25]
MySQL Verification Team
test results
Attachment: 94850.results (application/octet-stream, text), 9.41 KiB.
[8 Apr 2019 8:26]
sean sean
Thanks for your investication on this Umesh!
I did some test again , and still met the same issue.
here are the details you required :
provide source and destination environment details(OS):
both CentOS Linux release 7.4.1708 (Core)
sample schema(create table statement and sample data):
same as you did:
CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
configuration details(unaltered my.cnf/my.ini):
will send to you later
one thing I'm confused based on your test on 8.0.14 and 8.0.15 is, after creating the table t1 in your test, the file name is : t1#P#p0.ibd (uppercase #P#) but on my test, file name is : t1#p#p0.ibd (lowercase #p#)
I tried on the same server and same my.cnf for version 8.0.13, 8.0.14 and 8.0.15, result is :
8.0.13 #P# (uppercase)
8.0.14 #p# (lowercase)
8.0.15 #p# (lowercase)
======================================================================
update 2019-04-08 03:56 SH
======================================================================
after checking, the reason is quite clear, it's related with variable "lower_case_table_names"
before 8.0.13 (including 8.0.13), server initialized with lower_case_table_names=1 in centos, partitioned table will have uppercase suffix #P#,
after 8.0.14 (including 8.0.14), server initialized with lower_case_table_names=1 in centos, partitioned table will have lowercase suffix #p#.
I think it's start from the bug fixing:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html
InnoDB: Partition suffixes (the #P# part of a partitioned table name) were not converted to lowercase when moving tablespace data files from a MySQL instance on Windows to a MySQL instance on Linux where the lower_case_table_names variable was set to 1. Failure to fully convert table names to lowercase caused errors later when attempting to rename tables. (Bug #26925260)
So, I suppose in your test, you started MySQL service with default lower_case_table_names in linux, which is 0, so the partitioned table was with uppercase suffix #P#.
but for Innodb engine table in production envrioment, we need keep lower_case_table_names=1 to avoid case confliction:
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should use lower_case_table_names=1 on all platforms to force names to be converted to lowercase.
Suggest fix:
even set lower_case_table_names=1, partitioned table file suffix should be always same as uppercase #P# as previous versions in linux.
for window issue in Bug #26925260, suggest the process of import tablespace could be more tolerant and case-insensitive.
BTW, I could not find the detailed information related to Bug #26925260, could you help to provide a related links ?
Thanks
Wen
[10 Apr 2019 8:12]
MySQL Verification Team
Thank you for the feedback. I'm able to reproduce reported issue when both source and target instances are started with lower_case_table_names=1 regards, Umesh
[10 Apr 2019 8:16]
MySQL Verification Team
Copying Tablespaces from 8.0.13 instance to 8.0.15 Instance when lower_case_table_names=1 on both source and target instances
Attachment: 94850_8.0.13_8.0.15.results (application/octet-stream, text), 7.69 KiB.
[4 Sep 2019 13:12]
Daniel Price
Posted by developer:
commit 0fbbd91a325f098f9330857e78a84b3c5cecd261
Author: Aditya A <aditya.a@oracle.com>
Date: Fri Aug 23 19:31:08 2019 +0530
Bug #29627690 NOT ABLE TO IMPORT PARTITIONED TABLESPACE FROM VERSIONS 8.0.14-16
PROBLEM
-------
This bug is a regression after the fix for
<Bug26925260 ERROR 1030 (HY000): GOT ERROR 44 - 'INNODB ERROR' FROM STORAGE ENGINE>
During import we first try to open the cfg file corresponding to the
tablename. To search for a cfg file,we first try to build the path
to cfg with the data directory and the tablename ,but because of the
above bug the partition table ibd file as well as the cfg file are built
in lower cases where as in version <=8.0.13 we build it in upper cases.
This is the reason we are unable to get the path to the cfg and ibd file
FIX
---
If we don't find the cfg/cfp file ,we search it after converting table
name to lower cases and if found we rename the cfg/cfp and ibd
file according to previous convention.
Approved by: Mayank Prasad <mayank.prasad@oracle.com>
[4 Sep 2019 14:19]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.18 release, and here's the changelog entry: Importing a partitioned table from a MySQL 8.0.13 instance (or earlier) to a MySQL 8.0.14, 8.0.15, or 8.0.16 instance failed with a tablespace is missing error for source and target instances defined with lower_case_table_names=1. The tablespace file and the metadata file used by the import operation could not be found due to a file name case mismatch.
