Link to home
Avatar of Bill Henderson
Bill HendersonFlag for United States of America

asked on

MySQL migration errors

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.

Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Have you tried running the import with the standard mysql command line client?
Avatar of Bill Henderson


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

Avatar of Steve Bink
Steve Bink
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial