MySQL Bugs: #58406: Issues with copying partitioned InnoDB tables from Linux to Windows

Bug #58406	Issues with copying partitioned InnoDB tables from Linux to Windows
Submitted:	22 Nov 2010 22:02	Modified:	6 Feb 2012 23:12
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1,5.6 (from trunk)	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
----------
101115 14:19:53 [ERROR] Table .\test\d has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.
101115 14:19:53 [Warning] Table .\test\d key_used_on_scan is 0 even though there is no primary key inside InnoDB.
101115 14:19:53 [ERROR] Innodb could not find key n:o 0 with name PRIMARY from dict cache for table test/d
101115 14:19:53 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49709 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x301bcc0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
0000000140236650    mysqld.exe!dict_index_get_n_ordering_defined_by_user()[dict0dict.ic:416]
000000014022D705    mysqld.exe!row_search_max_autoinc()[row0sel.c:4689]
0000000140219B39    mysqld.exe!ha_innobase::innobase_initialize_autoinc()[ha_innodb.cc:2724]
0000000140219F47    mysqld.exe!ha_innobase::open()[ha_innodb.cc:2976]
000000014009BB35    mysqld.exe!handler::ha_open()[handler.cc:2094]
0000000140117CC9    mysqld.exe!open_table_from_share()[table.cc:1916]
000000014007A649    mysqld.exe!open_unireg_entry()[sql_base.cc:3941]
000000014007DCC1    mysqld.exe!open_table()[sql_base.cc:2932]
000000014007E971    mysqld.exe!open_tables()[sql_base.cc:4628]
000000014007EE68    mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:5039]
000000014006D752    mysqld.exe!mysql_execute_command()[sql_parse.cc:2710]
0000000140071860    mysqld.exe!mysql_parse()[sql_parse.cc:6055]
0000000140072538    mysqld.exe!dispatch_command()[sql_parse.cc:1262]
0000000140072FC7    mysqld.exe!do_command()[sql_parse.cc:888]
000000014009A0A7    mysqld.exe!handle_one_connection()[sql_connect.cc:1136]
000000014031F645    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402EA017    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402EA0E5    mysqld.exe!_threadstart()[thread.c:275]
0000000076D7F56D    kernel32.dll!BaseThreadInitThunk()
0000000076FB3021    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000003078FC0=CREATE TABLE IF NOT EXISTS t10 ( `pk` INTEGER NOT NULL AUTO_INCREMENT , `col_int` INTEGER , PRIMARY KEY ( `pk` ) ) PARTITION BY HASH ( `pk` ) PARTITIONS 5 SELECT `pk` , `col_int_key` FROM D
thd->thread_id=2
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
101115 14:22:56 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
101115 14:22:56  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
101115 14:22:58  InnoDB: Started; log sequence number 1 155407682
101115 14:22:58 [Note] Recovering after a crash using log-bin
101115 14:22:58 [Note] Starting crash recovery...
101115 14:22:58 [Note] Crash recovery finished.
101115 14:22:58 [Note] Event Scheduler: Loaded 26 events
101115 14:22:58 [Note] C:\mysql\bin\mysqld: ready for connections.
Version: '5.1.52-enterprise-gpl-advanced-log'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition (GPL)
101115 14:23:27 [ERROR] Cannot find or open table test/dd#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:
Not sure yet, but seems to have something to do with copying raw data files from Linux to Windows, when the table names exist as both upper and lower case ('D' and 'd' in the example above) and then accessing the same tables via CREATE TABLE <new> SELECT FROM <D/d> or otherwise as per the two examples below. Could also be that I copied the raw files form 5.5.7rc (on Linux) to 5.1.52/53 (on Windows) (yes yes I know ;) - can't remember details, will do some more research/testing later. I think this may have been the case since I saw some PERFORMANCE_SCHEMA output in the mysql_upgrade output of 5.1.53 :P

----------
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
C:\mysql\bin\mysqlcheck.exemysql.time_zone_leap_second                        OK
: mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FOR UPGRADE'mysql.time_zone_transition_type                    OK
mysql.user                                         OK

performance_schema.cond_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_current
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
----------

Other crashes

----------
thd->query at 00000000030F1EC0=show create table D
thd->thread_id=2
thd->killed=NOT_KILLED
----------

----------
101123  6:50:18 [ERROR] Table .\test\d has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.
101123  6:50:18 [Warning] Table .\test\d key_used_on_scan is 0 even though there is no primary key inside InnoDB.
101123  6:50:18 [ERROR] Innodb could not find key n:o 0 with name PRIMARY from dict cache for table test/d
101123  6:50:18 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16384
read_buffer_size=262144
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49709 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x30d4d10
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
00000001402366C0    mysqld.exe!dict_index_get_n_ordering_defined_by_user()[dict0dict.ic:416]
000000014022D775    mysqld.exe!row_search_max_autoinc()[row0sel.c:4699]
0000000140219B29    mysqld.exe!ha_innobase::innobase_initialize_autoinc()[ha_innodb.cc:2725]
0000000140219F37    mysqld.exe!ha_innobase::open()[ha_innodb.cc:2977]
000000014009BBD5    mysqld.exe!handler::ha_open()[handler.cc:2094]
0000000140117BE9    mysqld.exe!open_table_from_share()[table.cc:1916]
000000014007A6F9    mysqld.exe!open_unireg_entry()[sql_base.cc:3939]
000000014007DD71    mysqld.exe!open_table()[sql_base.cc:2932]
000000014007EA11    mysqld.exe!open_tables()[sql_base.cc:4626]
000000014007EF08    mysqld.exe!open_and_lock_tables_derived()[sql_base.cc:5037]
00000001400EF4D0    mysqld.exe!mysql_admin_table()[sql_table.cc:4611]
00000001400F10CF    mysqld.exe!mysql_check_table()[sql_table.cc:5534]
000000014006E194    mysqld.exe!mysql_execute_command()[sql_parse.cc:3073]
0000000140071910    mysqld.exe!mysql_parse()[sql_parse.cc:6072]
00000001400725E8    mysqld.exe!dispatch_command()[sql_parse.cc:1263]
0000000140073077    mysqld.exe!do_command()[sql_parse.cc:889]
000000014009A147    mysqld.exe!handle_one_connection()[sql_connect.cc:1136]
000000014031FAA5    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001402EA477    mysqld.exe!_callthreadstart()[thread.c:295]
00000001402EA545    mysqld.exe!_threadstart()[thread.c:275]
0000000076D7F56D    kernel32.dll!BaseThreadInitThunk()
0000000076FB3021    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000003082F80=CHECK TABLE `D`  FOR UPGRADE
thd->thread_id=3
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
----------

related: bug 47764

Marking as duplicate of bug  #47764 for the moment. See comment there.

This is not the same as bug#47764.

That bug needs partitioning to be hit.

Perhaps this is due to UPPER vs lower case table names?
InnoDB always uses lower case on windows even if lower_case_table_names is set to 2. (Partitioning on the other hand does never use lower case, see bug#47764).

Did you use lower_case_table_names = 1 (or 2) on linux before moving to windows?

Reverting to open status

Thank you for the feedback.

Do you have any idea on how to repeat this? Full CREATE TABLE query which fails for example.

Added to changelog for 5.1.62: 

When copying a partitioned InnoDB table from a Linux system to a
Windows system, you could encounter this error: 101115 14:19:53
[ERROR] Table . est\d has no primary key in InnoDB data dictionary,
but has one in MySQL! Normally, the solution to copy InnoDB tables
from Linux to Windows is to create the tables on Linux with the
lower_case_table_names option enabled. Partitioned tables, with #P#
appended to the filename, were not covered by that solution.

Didn't add to changelog for 5.5.21, 5.6.5 because the fix had already gone into 5.5 and 5.6, and was just being backported under this bug. (Possibly some minor code cleanup did go into those releases.)

Also included in 5.5.21 changelog.