Problem moving MySQL database from one server to another.

[kerunt]

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:
[code]
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 ;
[/code]

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!

[fenway]

Strange... my initial reaction was a version issue, but it doesn't seem to be the case...

[kerunt]

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.

[fenway]

Hmmm... I don't understand the TIMESTAMP vs. DATETIME; but at some point they did change CHARACTER_SET to CHARSET.