MySQL Bugs: #47764: partitioned innodb tables can't be moved directly from windows to linux

Bug #47764	partitioned innodb tables can't be moved directly from windows to linux
Submitted:	1 Oct 2009 14:52	Modified:	7 Dec 2011 16:03
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.39	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
If you create a partitioned innodb table on windows, then copy the datadir to linux, it should work.  However, we see this when trying to open the table:

090929 19:59:29 [ERROR] Failed to open table test/t1#P#p0 after 10 attemtps.

090929 19:59:29 [ERROR] Cannot find or open table test/t1#P#p0 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

How to repeat:
on windows: mysqld --no-defaults --console

drop table if exists t1;
create table t1(a int)engine=innodb partition by key(a) partitions 10;

Now copy entire datadir to linux installation:

check table t1;
show table status;

examine error log..

Suggested fix:
we should maintain cross platform compatibility as was always done

I wonder if it is related to Windows filenames being case insensitive.

Set to "Verified" again: verified as described as discussed with Shane before:

win-x86> ./bin/mysql -uroot test -P33051
drop table if exists t1;
create table t1(a int)engine=innodb partition by key(a) partitions 10;
\q
win-x86> tar -cf bug47764.tar data
win-x86> gzip bug47764.tar 

linux>tar -xzf bug47764.tar.gz 

linux>./bin/mysql -uroot  --socket=/tmp/mysql_ssmirnova.sock  test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> check table t1;
+---------+-------+----------+-------------------------------+
| Table   | Op    | Msg_type | Msg_text                      |
+---------+-------+----------+-------------------------------+
| test.t1 | check | Error    | Table 'test.t1' doesn't exist |
| test.t1 | check | status   | Operation failed              |
+---------+-------+----------+-------------------------------+
2 rows in set (1.00 sec)

mysql> show table status;
Empty set (1.00 sec)

mysql> \q
Bye

linux> cat data/linux.err
...
091003  9:33:17 [ERROR] Failed to open table test/t1#P#p0 after 10 attemtps.

091003  9:33:17 [ERROR] Cannot find or open table test/t1#P#p0 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

This is a problem concerning a special character for partition table name in the server.

The partition name is created by create_partition_name(), with special characters "#P#" separating the table name and partition name. For example, for table t1 in this case, the partition name can be "test/t1#P#p0", "test/t1#P#p1" ... etc.

When we create a partition table on windows, the name is set to lower case by normalize_table_name():

normalize_table_name()
{
#ifdef __WIN__
innobase_casedn_str(norm_name);
#endif
}

Stack:

normalize_table_name
ha_innobase::create
handler::ha_create
ha_partition::create
...

when opening the table, similarly, for windows platform, the table name is converted to lower case before looking into SYS_TABLES:

normalize_table_name
handler::ha_open
ha_partition::open
handler::ha_open
...

However, in our case, we access a database created on windows platform with linux server, the partition table names in the SYS_TABLES come with lower case "#p#", while linux mysql still passes in upper case "#P#" for the partition table name, and normalize_table_name() will not convert the name to lower case since it is not a windows platform, as a result, the server cannot find the partition table with miss matched case of "P".

The fix would be for partition table, if global variable lower_case_table_names is set, and if we cannot find the table with names contains "#P#", convert the whole partition name to lower case and search again.

This would fix the problem.

Thanks
Jimmy

A more elegant fix could come from mysql layer create_partition_name(), for which it uses the hard code lower case "p" in creating the actual partition table names.

The related code is:

void create_partition_name()
{
...
  if (name_variant == NORMAL_PART_NAME)
    strxmov(out, in1, "#P#", transl_part, NullS);
  else if (name_variant == TEMP_PART_NAME)
    strxmov(out, in1, "#P#", transl_part, "#TMP#", NullS);
  else if (name_variant == RENAMED_PART_NAME)
    strxmov(out, in1, "#P#", transl_part, "#REN#", NullS);
}

Hi There,

We encountered exactly the same symptom of "090929 19:59:29 [ERROR] Failed to open table test/t1#P#p0 after 10 attemtps" when trying to port from Windows to Linux through physical backup with a partitioned table. Logical restore through mysqldump is not feasible as our database size is > 200GB.

Our Mysql Community Server version is 5.1.45

Please kindly correct me if I am wrong: The solution provided above seems to be at Mysql binary level and we could not find any fix for this bug from 5.1.39 to 5.1.51 . May I ask if there is an alternate solution at user level?

Thank you,

Gyan Der

There is fact two bugs, first is that the partitioning engine does not convert the partitioning names to lower case according to lower_case_table_names variable, and the second is that InnoDB does always convert table(/partition) names to lowercase on windows (regardless of what lower_case_table_names say).

I propose to fix the bug in 5.1+ by adding a second try in ha_partition::open().
If the ha_open on a partition fails (preferable only on 'No such file') and lower_case_table_names is 1 (default on windows) convert the path (including the partition name) to lower case and do a second try. This should also be done to ha_partition::rename() so that one can rename the lowercase partitions when the lower_case_table_names is set to 0 or 2.

Probably there should be two bugs for 'InnoDB: does not favor --lower_case_table_name=2 on windows' and 'Partitioning: does not favor --lower_case_table_name=1 for partition name part'.

See bug #58406

For the moment, I have set bug #58406 as a duplicate of this one. Mattias: when resolving this bug (or bugs), please look at #58406 also to see if it is the same problem(s). If not, please re-open that one and I'll see if I can find some more testcases.

Hi,
I have the exact same issue too. When will this bug be fixed?

I can confirm this is still an issue with MySQL 5.5 and going from Linux->Windows as well.

Anyone know of a workaround?

Hi,

I'm experienced the same problem trying to migrate a server from Windows to Linux today.
I first tried the same MySQL Server Version that was running on Windows (5.5.9). Then I upgraded to the latest released version 5.5.20 and still have the same Problem.

Why was this bug never solved? Does the sulution suggested by Jimmy Yang in 2009 not work?

I urgently need a solution because completely rebuilding the tables is not an option (they are very large and a complete dump and import would take weeks...). Does not even a workaround exist?

can docs team kindly document this fix.  which versions is it fixed in?
can you also note the problem described here in:

https://dev.mysql.com/doc/refman/5.5/en/innodb-migration.html

This was fixed in 5.1.62/5.5.21/5.6.5.