Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL migration errors

Posted on 2014-12-27
5
Medium Priority
?
244 Views
Last Modified: 2015-01-02
Hi - I'm stuck trying to migrate a MySQL schema from a 5.1 version of MySQL to a system running 5.5. I'm getting strange 'bad allocation' errors in MySQL workbench on import that seem to be related to timeouts discussed here:

http://bugs.mysql.com/bug.php?id=58778 

I also set my timeouts from 30 seconds to more than 5 minutes, but I keep getting the errors.

The database has one giant table and about 20 other small tables, so I exported everything except the giant table and my SQL file dropped from 300MB to ~4MB.

And the error is different. No timeout - instead, I'm getting Error Code 2014: Commands out of sync; you can't run this command now. The command is "USE "spwonline" - the schema name. Here are the first few lines of my SQL file:

# HeidiSQL Dump 
#
# --------------------------------------------------------
# Host:                         localhost
# Database:                     spwonline
# Server version:               5.1.31-community-log
# Server OS:                    Win64
# Target compatibility:         ANSI SQL
# HeidiSQL version:             4.0
# Date/time:                    2014-12-25 10:26:21
# --------------------------------------------------------

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ANSI,NO_BACKSLASH_ESCAPES';*/
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;*/


#
# Database structure for database 'spwonline'
#

CREATE DATABASE /*!32312 IF NOT EXISTS*/ "spwonline" /*!40100 DEFAULT CHARACTER SET latin1 */;

USE "spwonline";

Open in new window



 Most of the blogs, forums, etc say I need to update the source system, then export, then the import should work. And there's the rub: I'm very sketched out by the current MySQL environment of this busy production website. It was our hope to extract the data and quietly start up the site in a much healthier server environment with better site code and never look back at this old server! Instead, the state of the server appears to be part of my migration challenge.

So...does anyone know what the problem is with the commands up to USE in the code above, that would cause me grief in a 5.5 environment? Or has anyone else had these types of issues and have more recommendations without having to overhaul the existing server?

Thank you

p.s. - In case it matters, the older server is WAMP Windows 2003, and new server is WAMP Windows 2008 r2. Export done with HeidiSQL. Import attempted in MySQL Wordbench.

Bill
0
Comment
Question by:billium99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 40520515
Have you tried running the import with the standard mysql command line client?
0
 
LVL 1

Author Comment

by:billium99
ID: 40520601
Hmm - when I do it this way, it gives me endless 1064 SQL syntax errors on line 1. It's been spinning for 5 minutes now.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 40520609
It may be something weird in the way HeidiSQL is exporting the data, or maybe in the data itself.  I use Heidi every day, though, and have never had issues with exporting.  Did you use the "Export Database as SQL" feature or the "Export Grid Rows" feature?  Also, what version of HeidiSQL are you using?

There are no structural/syntax reversions between MySQL 5.1 and 5.5 that would affect this, so I am inclined to start examining the data.  The 1064 error is an SQL syntax error, which also points to something weird in the data, probably quotes.  If you previously exported the entire database, try using the "Export Grid Rows" feature on the table.  This will export is as individual INSERT statements, which allow for easier troubleshooting.
0
 
LVL 1

Author Comment

by:billium99
ID: 40522025
Thanks Steve - I did use Export Database as SQL. The Heidi version is ancient: 4.0.

I suppose it should be a light touch to only update Heidi. I will do that first and try another export.

Bill
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 40522033
I highly recommend the upgrade.  The latest is 9.x, and is a really nice management suite.  It won my loyalty over Navicat and EMS MySQL Manager - both of which are commercial offerings.

If the upgrade does not help, see if you can reproduce the error with a manual process using mysqldump.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question