Improve company productivity with a Business Account.Sign Up


MySQL migration errors

Posted on 2014-12-27
Medium Priority
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
  • 3
  • 2
LVL 51

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

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 51

Accepted Solution

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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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:…

579 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