MySQL migration errors

Posted on 2014-12-27
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: 

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
# --------------------------------------------------------


# 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.

Question by:billium99
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
LVL 50

Expert Comment

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

Author Comment

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.
LVL 50

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.

Author Comment

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.

LVL 50

Accepted Solution

Steve Bink earned 500 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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question ( here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

749 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