Jump to content


Photo

Problem moving MySQL database from one server to another.


  • Please log in to reply
3 replies to this topic

#1 kerunt

kerunt
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 09 August 2006 - 03:39 AM

Hi all,

I have a MySQL database running on my local server. When I export everything (structure + data) through phpMyAdmin and try to import this (also through phpMyAdmin) on a webhost which I just purchased, I get the following error:

#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, PRIMARY KEY

This error appears after attempting to run the following:
CREATE TABLE `accounts` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `commander` varchar(20) NOT NULL default '',
  `ward` varchar(20) NOT NULL default '',
  `coords` varchar(8) NOT NULL default '',
  `continent` int(2) NOT NULL default '0',
  `country` int(2) NOT NULL default '0',
  `location` int(2) NOT NULL default '0',
  `credits` int(16) NOT NULL default '0',
  `score` int(16) NOT NULL default '0',
  `base_strength` int(16) NOT NULL default '0',
  `military_strength` int(16) NOT NULL default '0',
  `clan` varchar(30) default NULL,
  `clan_rank` int(1) NOT NULL default '0',
  `admin` int(1) NOT NULL default '0',
  `ip` varchar(15) NOT NULL default '',
  `registration_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `commander` (`commander`),
  UNIQUE KEY `tribe` (`ward`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

The strange thing is that I can import this code just fine on my local server.

My local server:
- phpMyAdmin 2.6.1
- MySQL 4.1.9

My webhost's server:
- phpMyAdmin 2.8.0.2
- MySQL 4.1.10

What could be the problem?

Thanks!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 04:07 AM

Strange... my initial reaction was a version issue, but it doesn't seem to be the case...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 kerunt

kerunt
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 09 August 2006 - 07:29 PM

I seem to have "gotten around" the problem.
I changed the field types from TIMESTAMP to DATETIME, which got rid of that problem, but the script still gave an error near DEFAULT CHARSET=latin1, so getting rid of that too did the trick.


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 August 2006 - 07:32 PM

Hmmm... I don't understand the TIMESTAMP vs. DATETIME; but at some point they did change CHARACTER_SET to CHARSET.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users