SQL help, anyone?

Pol · November 2006

One user was having trouble with my User Wall extension; it turned out to be an SQL error that occurred while it was creating its database table. The SQL used to do so is as follows:

CREATE TABLE `LUM_UserWall` (
  `WallCommentID` int(8) NOT NULL auto_increment PRIMARY KEY,
  `UserID` int(10) NOT NULL default '0',
  `Date` datetime NOT NULL default '0000-00-00 00:00:00',
  `FromUserID` int(10) NOT NULL default '0',
  `Body` text NOT NULL default '',
  `RemoteIp` varchar(100) default NULL,
  KEY `UserID` (`UserID`)
);

Can anyone see any problems with this? For what it's worth, it says the error is near ';' at the end. And the extension seems to have worked fine for everyone else (or, at least, nobody else has reported this problem to me).

Minisweeper · November 2006

One thing which strikes me as a tiny bit odd is that Body is NOT NULL but set by default to ''...I'm no SQL expert though so that might be perfectly normal practice...

crash_D. · December 2006

another problem might be that the word Date is reserved in MySQL and a user can't use it... give the row name "Thedate" or "curdate", or something other than simply "Date"... but im no mysql expert either, only i remember that i had problems when i entitled a row desc (short for description) and the server thought it was a reserved desc (short of descend)... hope this helps

Dinoboff · December 2006

'' is an empty value, but not null I think (I am no mysql expert either).

Max_B · December 2006

IMO, this code bit is correct. It run ok on my 4.1 base. The previously mentioned points should not be relevant.
Notice though that an export statement defines the table "type". In case the default is not MyISAM this could be significant.

CREATE TABLE `LUM_UserWall` (
  `WallCommentID` int(8) NOT NULL auto_increment,
  `UserID` int(10) NOT NULL default '0',
  `Date` datetime NOT NULL default '0000-00-00 00:00:00',
  `FromUserID` int(10) NOT NULL default '0',
  `Body` text NOT NULL,
  `RemoteIp` varchar(100) default NULL,
  PRIMARY KEY  (`WallCommentID`),
  KEY `UserID` (`UserID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

SQL help, anyone?

Comments