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