Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.

SQL help, anyone?

PolPol
edited December 2006 in Vanilla 1.0 Help
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).

Comments

  • 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...
  • 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
  • '' is an empty value, but not null I think (I am no mysql expert either).
  • 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 ;
This discussion has been closed.