We help IT Professionals succeed at work.

MySQL migration errors

Bill Henderson
on
411 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2004

Commented:
Have you tried running the import with the standard mysql command line client?
Bill HendersonWeb Marketing

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2004

Commented:
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.
Bill HendersonWeb Marketing

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2004
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.