SQL error when importing an SQL dump of phpWCMS site

Get help with installation and running phpwcms here. Please do not post bug reports or feature requests here.
Post Reply
colech
Posts: 178
Joined: Thu 25. Mar 2004, 01:01
Location: Washington, USA
Contact:

SQL error when importing an SQL dump of phpWCMS site

Post by 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
while !sleeping {
work_on(phpwcms);
}
// Check out our web development company!
// "Your business has character... don't hide it!"
Karla
Posts: 223
Joined: Tue 26. Oct 2004, 11:56

Re: SQL error when importing an SQL dump of phpWCMS site

Post by Karla »

colech wrote: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
Posts: 178
Joined: Thu 25. Mar 2004, 01:01
Location: Washington, USA
Contact:

Post by 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
while !sleeping {
work_on(phpwcms);
}
// Check out our web development company!
// "Your business has character... don't hide it!"
Karla
Posts: 223
Joined: Tue 26. Oct 2004, 11:56

Post by 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
Posts: 286
Joined: Sun 25. Jul 2004, 23:24
Location: UK

Post by Peekay »

colech
Posts: 178
Joined: Thu 25. Mar 2004, 01:01
Location: Washington, USA
Contact:

Post by 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 
while !sleeping {
work_on(phpwcms);
}
// Check out our web development company!
// "Your business has character... don't hide it!"
Karla
Posts: 223
Joined: Tue 26. Oct 2004, 11:56

Post by 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
Posts: 178
Joined: Thu 25. Mar 2004, 01:01
Location: Washington, USA
Contact:

Post by 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).
while !sleeping {
work_on(phpwcms);
}
// Check out our web development company!
// "Your business has character... don't hide it!"
Post Reply