Mysql Question

[Satisfaction]

I'm trying to re-import a mysql dump, however it keeps giving me errors at this particular table:

CREATE TABLE `phpads_images` (
`filename` varchar(128) NOT NULL default '',
`contents` mediumblob NOT NULL,
`t_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`filename`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;


The error I'm receiving is:
"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 'defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY "

Anybody have any ideas?

Cheers

[Esbee]

Originally Posted by Satisfaction

I'm trying to re-import a mysql dump, however it keeps giving me errors at this particular table:

CREATE TABLE `phpads_images` (
`filename` varchar(128) NOT NULL default '',
`contents` mediumblob NOT NULL,
`t_stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`filename`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;


The error I'm receiving is:
"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 'defaultCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY "

Anybody have any ideas?

Cheers

The "timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" stuff is redundant on MySQL4.1+ - see:
http://dev.mysql.com/doc/refman/5.0/...stamp-4-1.html

Change that line to plain 'ole
......, `t_stamp` timestamp,......

... and see if it works as you expect. It should.

The ' DEFAULT CHARSET=latin1' can cause problems as well if the dump was from an older version of MySQL headed into a newer version, or the dump was generated by a 3rd-party program/script that presumes it should be there. Try dumping it as well.

[Deek]

try and just put quotes around the timestamp part.

`t_stamp` timestamp NOT NULL default 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP',

Should fix you up.

[IceMaster]

First remove DEFAULT CHARSET=latin1 and try again.

[lawked]

mysqldump --help ... look at --compatible

I'm pretty sure this is the reason it's happening, solution was posted above.

[grumpy]

you use php myadmin?

this is the right way


CREATE TABLE `phpads_images` (
`filename` varchar(128) NOT NULL default '',
`contents` mediumblob NOT NULL,
`t_stamp` timestamp NOT NULL ,
PRIMARY KEY (`filename`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;