SQL error when importing an SQL dump of phpWCMS site

[colech]

What is wrong with this SQL? I am trying to transfer all MySQL data from my local machine to a new VPS server with these conditions...

FROM -> Local Windows XP/MySQL 4.1.7-nt/phpMyAdmin 2.6.0-pl3
TO -> Virtual Private Server with RedHat/MySQL 3.23.58/phpMyAdmin 2.5.7-pl1.

I am doing all export/import via phpMyAdmin. The generated SQL file is small, about 1MB.

What else do I need to tell you?

Code: Select all

Error

SQL-query : 

CREATE TABLE IF NOT EXISTS `phpwcms_address` (
`address_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`address_key` varchar( 255 ) NOT NULL default '',
`address_email` varchar( 255 ) NOT NULL default '',
`address_name` varchar( 255 ) NOT NULL default '',
`address_verified` int( 1 ) NOT NULL default '0',
`address_tstamp` timestamp NOT NULL defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address_subscription` blob NOT NULL ,
PRIMARY KEY ( `address_id` )
)ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =7

MySQL said:
#1064 - You have an error in your SQL syntax near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `address_subscription` blob NOT' at line 7

[Karla]

What is wrong with this SQL?

Code: Select all

`address_tstamp` timestamp NOT NULL defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Looks like there should be a space between:

defaultCURRENT_TIMESTAMP

like this:

Code: Select all

`address_tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

[colech]

I tried adding the space and received exactly the same error. Is there a problem with the TIMESTAMP type and what is assigned to it?

Code: Select all

CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

[Karla]

I'm not much w/ MySQL, but I remember a friend having a similar problem and I think it had to do with each of the servers being in a different time zone, or sumpin' like that. Just guessing, sorry...

[Peekay]

This may be of some help:

http://www.phpwcms.de/forum/viewtopic.php?p=26323#26323

[colech]

I have downloaded AMBT (which by the way I am very impressed with!) and created a backup of my database. When I ran the SQL I received the exact same error:

Code: Select all

Error

SQL-query :  

CREATE TABLE `phpwcms_address` (

`address_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`address_key` varchar( 255 ) NOT NULL default '',
`address_email` varchar( 255 ) NOT NULL default '',
`address_name` varchar( 255 ) NOT NULL default '',
`address_verified` int( 1 ) NOT NULL default '0',
`address_tstamp` timestamp NOT NULL defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address_subscription` blob NOT NULL ,
PRIMARY KEY ( `address_id` ) 
)ENGINE = MYISAM DEFAULT CHARSET = latin1 

MySQL said: 


#1064 - You have an error in your SQL syntax near 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `address_subscription` blob NOT' at line 7 

[Karla]

Your problem ~must be related to backward compatibility. The format for TIMESTAMP changed starting with ver 4.1. This is probably confusing ver 3.23:

http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

[colech]

I modified this:

Code: Select all

CREATE TABLE `phpwcms_address` ( 

`address_id` int( 11 ) NOT NULL AUTO_INCREMENT , 
`address_key` varchar( 255 ) NOT NULL default '', 
`address_email` varchar( 255 ) NOT NULL default '', 
`address_name` varchar( 255 ) NOT NULL default '', 
`address_verified` int( 1 ) NOT NULL default '0', 
`address_tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
`address_subscription` blob NOT NULL , 
PRIMARY KEY ( `address_id` ) 
) ENGINE = MYISAM DEFAULT CHARSET = latin1

...to the following:

Code: Select all

CREATE TABLE `phpwcms_address` ( 

`address_id` int( 11 ) NOT NULL AUTO_INCREMENT , 
`address_key` varchar( 255 ) NOT NULL default '', 
`address_email` varchar( 255 ) NOT NULL default '', 
`address_name` varchar( 255 ) NOT NULL default '', 
`address_verified` int( 1 ) NOT NULL default '0', 
`address_tstamp` timestamp NULL, 
`address_subscription` blob NOT NULL , 
PRIMARY KEY ( `address_id` ) 
)

I modified this line:

Code: Select all

NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

...removed this:

Code: Select all

ENGINE = MYISAM DEFAULT CHARSET = latin1

In addition to these changes, I also had to modify all field data of going into type TIMESTAMP from this:

Code: Select all

INSERT INTO `phpwcms_articlecat` VALUES (1,'About','',1,2004-11-04 13:52:33,1,1,0,0,0,'',0,1,0,0,5,'',1);

...to this with added single quote marks around the TIMESTAMP data:

Code: Select all

INSERT INTO `phpwcms_articlecat` VALUES (1,'About','',1,'2004-11-04 13:52:33',1,1,0,0,0,'',0,1,0,0,5,'',1);

Thankyou to all who gave input, and let me know if I did anything incorrectly. From the documentation it appeared that a default value of CURRENT_TIMESTAMP would be given to fields of type TIMESTAMP if the default value was set to NULL (at least for MySQL 3.23.x I know know about 4.1.x).