MySQL Bugs: #55588: Unable to migrate data from 5.1 to 5.5

Bug #55588	Unable to migrate data from 5.1 to 5.5
Submitted:	27 Jul 2010 19:35	Modified:	27 Oct 2010 13:22
Reporter:	Prasad Subraveti	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	Mysql 5.5	OS:	Windows (XP Professional)
Assigned to:		CPU Architecture:	Any
Tags:	data, MYSQL SERVER

Description:
Some history for context:

I have 2 mysql instances. One on a linux box and one on a windows desktop. On a regular basis, i perform DB dumps from my linux box to the windows box.

Linux box runs Mysql Server 5.0.51a
Windows box used to run Mysql Server 5.1

I have a database in Linux server. Lets call it "mydb". I did a dump of "mydb" to the windows box. I added some new tables to it and populated data to those tables. So lets call it "final db"

I had mysql 5.1 server instance installed on my server. I uninstalled 5.1. Did not remove the data. I installed mysql 5.5 server using the msi recommended installation for 32 bit PCs.

I copied all the data from 5.1 instance to 5.5 instance. I basically moved the <final db> folder under "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data" to "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data"

When i query against the new tables i had in <final db> , the query fails with the following error on Mysql workbench:

Error Code: 1146
Table '<final db.newtable' doesn't exist

However when i query the tables that came via the data dump, they return data correctly.

How to repeat:
Please refer to description.

So you did a simple copy/paste of the db directory?. Are those tables InnoDB or MyISAM?. Thanks in advance.

MyISAM

Simple copy and paste from the data directory.

Was the 5.5 server started and working while you were moving the <final db>?

yes. The server was up and running. But there was no data access being done.

I did restart the server instance and it did not help.

Please, send the results of:

dir "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server
5.5\data"

from cmd.exe command line window and the results of

show databases;

statement. Point out what database is that "final db" you copied from 5.1.

Result of dir command:

 Volume in drive C is OS
 Volume Serial Number is 464E-986F

 Directory of C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data

07/29/2010  10:24 AM    <DIR>          .
07/29/2010  10:24 AM    <DIR>          ..
07/23/2010  11:47 AM    <DIR>          heartbeats
07/27/2010  10:04 PM        18,874,368 ibdata1
07/27/2010  10:04 PM        25,165,824 ib_logfile0
07/27/2010  11:29 AM        25,165,824 ib_logfile1
07/27/2010  10:37 AM    <DIR>          mysql
07/29/2010  10:24 AM                 0 mysql_data_directory_structure.txt
07/27/2010  10:37 AM    <DIR>          performance_schema
07/27/2010  10:04 PM            13,466 subravprat3500.err
07/27/2010  10:04 PM                 5 subravprat3500.pid
07/27/2010  10:37 AM    <DIR>          test
               6 File(s)     69,219,487 bytes
               6 Dir(s)  100,080,803,840 bytes free

Result of logging to mysql and show databases:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.5-m3 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| heartbeats         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

The name of the database in question is 'heartbeats'

Did you run mysql_upgrade?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".