Moving Hosts + MySQL Errors

Here’s a funny story. I’ve been moving around a few sites, and one of them involves a couple MySQL databases. I’ve been lucky in the past and have moved sites with pretty much no problems for the most part (well there was that ONE time…). Until last week.

When I tried uploading the databases to the new host, I would get this error for one database:

#1064 – You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ENGINE=MyISAM DEFAULT CHARSET=latin1’

or this one for another database:

#1064 – You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

and yup, another database gave me this grief:

#1064 – You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ALTER TABLE `wp_categories` DISABLE KEYS */’

I *hate* bugging my webhosts unless it’s something I’m really sure is a problem on their end. I know they are overrun with psycho customer support requests. With these MySQL errors shooting out, I wanted to make sure I was contacting the right host with the problem. Was it my original host that had the problem (corrupt databases or something?) or was it the new host with the problem?

So I did a bit of investigating and started uploading and downloading databases on the handful of webhosts I work with. I discovered that a couple hosts had no problems with any of the databases. No matter what I tried uploading, they all took them no prob. Another couple hosts choked on the databases – no matter what I tried uploading, always shooting out some sort of error. And then one or two hosts could handle some databases, yet not others.

So what’s a girl to do? I run to Emax Hosting because they’re my tried and true. I’ve had a few bitty sites sitting on their servers for a year + and I know if something’s wonky – they’ll know why. Bonus reason: Chris responds on his forum pretty darn fast.

Here’s the scoop Chris gave me:

It all boils down to 2 things.

[1] MySQL version, we use on most of our server 4.1 which has no issues being backwards compatible but 4.0 has issues with 4.1

[2] Other issue is with the different version but how and what is properly exported when backing your database up.

Doh! It all comes down to me trying to squeeze database backups from MySQL 4.1x to MySQL 4.0x. Apparently that doesn’t work out too well. πŸ˜•

MySQL tip of the day: when you’re moving hosts, watch the MySQL version you are moving onto. If it’s 4.1x you should have no problems. But if it’s 4.0x, you’ll want to make sure the host you’re moving from isn’t 4.1x because you’ll get nothing but ONE BIG HEADACHE.

BTW: I had the inspiration at one time to offer hosting services (as a reseller). Heck no – I gave up that idea a long time ago when I realized I wasn’t techie enough πŸ˜†

admin

I've been trying to find my way online for more years than I care to admit.

8 thoughts to “Moving Hosts + MySQL Errors”

  1. It’s do-able, but you have to hack a few things out of your MySQL dump πŸ™‚

    For example, MySQL 4.0 doesn’t support the “DEFAULT CHARSET=latin1” stuff. You can also drop all the DISABLE or ENABLE KEYS lines.

  2. Thanks for the info Peter – too much headache for lazy me though! πŸ˜†

    I actually did try removing the DEFAULT CHARSET=latin1 line and I ended up with funky character encoding issues on the web copy. *What a mess*.

    hmmm but now that I know you know this MySQL stuff, and where to find you ;), methinks you should hide :lol:.

  3. I found a solution that you can use if you are able to run mysqldump at the server itself:

    “mysqldump -u username -ppassword –compatible=mysql40 database_name >FILENAME.sql”

    –compatible=mysql40 makes it compatible with older versions.

    The above worked for me to remove the annoying DEFAULT CHARSET problem πŸ™‚

  4. Can someone please give MrBister a mountain of gold or something, as that –compatible switch has just saved me from going bonkers.

    Was going from a 4.1 (Gentoo Linux) to a 4.0 (OpenBSD 3.8) and was getting very bogged down with some dodgy syntax issues.

    Thank you!

    Aegilops

  5. Hi,

    Interesting! I have a big problem with:

    #1064 – You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near β€˜CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

    Does anybody know a sollution??? I’m desperate with als a big headache

  6. Change

    “timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,”

    to

    “timestamp(14) NOT NULL,”

  7. How about adding compatibility when you export your database:

    SQL compatibility mode: MYSQL40
    Check this: Add IF NOT EXISTS

    I export and import without any issue.

    Correct me if m wrong.

Comments are closed.