Error log connecting to new schema/DB

AcadiusLost · Post by **AcadiusLost** » Mon Aug 27, 2007 4:47 pm

Hialmar set us up a new DB and schema last night, so I pointed the OAS2 at it to test- however it didn't generate the tables like it was supposed to. this is the xp_mysql.txt file:

Code: Select all

* Log level set to 2 (everything)
* Connecting to server vault.alandfaraway.org
* Plugin initialized.
* Registering under function class SQL
* Executing:  CREATE TABLE IF NOT EXISTS `areas` (`ID` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,`ServerID` smallint(8) UNSIGNED NOT NULL,`Name` varchar(64) NOT NULL,`Tag` varchar(64) NOT NULL,`Visits` int(10) UNSIGNED NOT NULL default 0,`X` smallint(5) default NULL,`Y` smallint(5) default NULL,`Z` smallint(5) default NULL,`IsSafe` bool NOT NULL default false,`IsInside` bool NOT NULL default false,`IsNatural` bool NOT NULL default false,PRIMARY KEY (`ID`), UNIQUE KEY (`ServerID`, `Tag`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'false,`IsInside` bool NOT NULL default false,`IsNatural` bool N.
* Executing: CREATE TABLE IF NOT EXISTS `cdkeys` (`CDKey` varchar(8) NOT NULL,`IsBanned` bool NOT NULL default false,PRIMARY KEY (`CDKey`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'false,PRIMARY KEY (`CDKey`)) ENGINE=MyISAM DEFAULT CHARSET=lati.
* Executing: CREATE TABLE IF NOT EXISTS `characters` (`ID` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,`ServerID` smallint(5) UNSIGNED NOT NULL,`PlayerID` mediumint(8) UNSIGNED NOT NULL,`Name` varchar(32) NOT NULL,`Level` tinyint(3) UNSIGNED NOT NULL default 1,`Race` tinyint(3) UNSIGNED NOT NULL,`Subrace` tinyint(3) UNSIGNED default NULL,`Deity` varchar(32) default NULL,`Gender` tinyint(3) UNSIGNED NOT NULL,`HP` smallint(5) UNSIGNED NOT NULL,`XP` mediumint(8) UNSIGNED NOT NULL default 0,`GP` int(10) UNSIGNED NOT NULL default 0,`Wealth` int(10) UNSIGNED NOT NULL default 0,`Ethics` tinyint(3) UNSIGNED NOT NULL,`Morals` tinyint(3) UNSIGNED NOT NULL,`Class1` smallint(5) UNSIGNED NOT NULL,`Level1` tinyint(3) UNSIGNED NOT NULL default 1,`Class2` smallint(5) UNSIGNED default NULL,`Level2` tinyint(3) UNSIGNED default NULL,`Class3` smallint(5) UNSIGNED default NULL,`Level3` tinyint(3) UNSIGNED default NULL,`STR` tinyint(3) UNSIGNED NOT NULL,`CON` tinyint(3) UNSIGNED NOT NULL,`DEX` tinyint(3) UNSIGNED NOT NULL,`INT` tinyint(3) UNSIGNED NOT NULL,`WIS` tinyint(3) UNSIGNED NOT NULL,`CHA` tinyint(3) UNSIGNED NOT NULL,`Location` varchar(128) default NULL,`Damage` smallint(5) UNSIGNED NOT NULL default 0,`Deaths` smallint(5) UNSIGNED NOT NULL default 0,`Status` int(10) UNSIGNED NOT NULL default 0,`IsOnline` bool NOT NULL default false,`IsDeleted` bool NOT NULL default false,`IsPlayable` bool NOT NULL default true,PRIMARY KEY (`ID`), UNIQUE KEY (`PlayerID`, `Name`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'false,`IsDeleted` bool NOT NULL default false,`IsPlayable` bool.
* Executing: CREATE TABLE IF NOT EXISTS `ipaddress` (`ID` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,`CDKey` varchar(8) NOT NULL,`IPAddress` varchar(16) NOT NULL,`IsBanned` bool NOT NULL default false,PRIMARY KEY (`ID`), UNIQUE KEY (`CDKey`, `IPAddress`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'false,PRIMARY KEY (`ID`), UNIQUE KEY (`CDKey`, `IPAddress`)) EN.
* Executing: CREATE TABLE IF NOT EXISTS `item_properties` (`ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,`ItemProperty` smallint(5) NOT NULL,`Description` varchar(128) default NULL,`IsIllegal` bool NOT NULL default 0,PRIMARY KEY  (`ID`), UNIQUE KEY (`ItemProperty`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'DEFAULT CHARSET=latin1' at line 1.
* Executing: CREATE TABLE IF NOT EXISTS `logs` (`ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,`ServerID` smallint(5) UNSIGNED NOT NULL,`CharacterID` mediumint(11) UNSIGNED default NULL,`Event` varchar(32) NOT NULL,`Description` varchar(128) default NULL,`Date` timestamp NOT NULL default CURRENT_TIMESTAMP,PRIMARY KEY  (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'CURRENT_TIMESTAMP,PRIMARY KEY  (`ID`)) ENGINE=MyISAM DEFAULT CH.
* Executing: CREATE TABLE IF NOT EXISTS `players` (`ID` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,`CDKey` varchar(8) NOT NULL,`Name` varchar(32) NOT NULL,`FirstLogin` datetime,`LastLogin` datetime,`LastLogout` datetime,`Logins` int(10) UNSIGNED NOT NULL default 0,`TimeOnline` mediumint(8) UNSIGNED NOT NULL default 0,`IsDM` bool NOT NULL default false,`IsBanned` bool NOT NULL default false,PRIMARY KEY (`ID`), UNIQUE KEY(`CDKey`, `Name`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'false,`IsBanned` bool NOT NULL default false,PRIMARY KEY (`ID`).
* Executing: CREATE TABLE IF NOT EXISTS `pwdata` (`Name` varchar(64) NOT NULL,`Tag` varchar(64) NOT NULL,`Key` varchar(64) NOT NULL,`Value` varchar(128) default NULL,`Expiration` smallint(5) UNSIGNED NOT NULL default 0,`Last` timestamp NOT NULL default CURRENT_TIMESTAMP,PRIMARY KEY (`Name`, `Tag`, `Key`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'CURRENT_TIMESTAMP,PRIMARY KEY (`Name`, `Tag`, `Key`)) ENGINE=My.
* Executing: CREATE TABLE IF NOT EXISTS `pwobject` (`Name` varchar(64) NOT NULL,`Tag` varchar(64) NOT NULL,`Key` varchar(64) NOT NULL,`Value` varchar(128) default NULL,`Expiration` smallint(5) UNSIGNED NOT NULL default 0,`Last` timestamp NOT NULL default CURRENT_TIMESTAMP,PRIMARY KEY (`Name`, `Tag`, `Key`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'CURRENT_TIMESTAMP,PRIMARY KEY (`Name`, `Tag`, `Key`)) ENGINE=My.
* Executing: CREATE TABLE IF NOT EXISTS `servers` (`ID` smallint(5) UNSIGNED NOT NULL,`Name` varchar(64) NOT NULL,`IPAddress` varchar(21) NOT NULL,PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'DEFAULT CHARSET=latin1' at line 1.
* Executing: CREATE TABLE IF NOT EXISTS `spelltrack` (`ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,`CharacterID` mediumint(11) UNSIGNED NOT NULL,`Class` smallint(5) NOT NULL,`Uses` varchar(128) default NULL,PRIMARY KEY (`ID`), UNIQUE KEY (`CharacterID`, `Class`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
! SQL Error: 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 'DEFAULT CHARSET=latin1' at line 1.
* Executing: SELECT Value FROM pwdata WHERE Name='OAS 2 Test Server' and Tag='alfa_00' and `Key`='ACR_TIME_SERVERTIME'
! SQL Error: Table 'alfa_oas2.pwdata' doesn't exist.
* Executing: INSERT INTO pwdata (Name, Tag, `Key`, Value, Expiration) VALUES ('OAS 2 Test Server','alfa_00','ACR_TIME_SERVERTIME', '664354740', 0) ON DUPLICATE KEY UPDATE Value='664354740', Expiration=0
! SQL Error: 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 'ON DUPLICATE KEY UPDATE Value='664354740', Expiration=0' at lin.
* Executing: SELECT pwdata.Name, pwdata.Tag, pwdata.Value, characters.ID, characters.Status FROM characters JOIN players on players.ID=characters.PlayerID JOIN pwdata on pwdata.Name=characters.Name WHERE pwdata.Tag=players.CDKey and pwdata.`Key`='ACR_DTH_LOCATION' and characters.Status = characters.Status | 1
! SQL Error: Table 'alfa_oas2.characters' doesn't exist.
* Executing: SELECT * FROM servers WHERE ID=0
! SQL Error: Table 'alfa_oas2.servers' doesn't exist.
* Executing: INSERT INTO servers (ID, Name, IPAddress) VALUES(0,'ALFA OAS Test server','67.172.180.55:5127')
! SQL Error: Table 'alfa_oas2.servers' doesn't exist.
* Executing: UPDATE characters SET IsOnline=0
! SQL Error: Table 'alfa_oas2.characters' doesn't exist.

At first I thought the problem might be that the schema wasn't called "nwnx" in case that was coded in somewhere in the scripts- now it looks more like a problem with the create table syntax.

Is this a problem with the persistency code, or some incompatibility in the SQL server configuration? Ideas?

indio · Post by **indio** » Mon Aug 27, 2007 8:36 pm

I had a lot of trouble getting nwn2 to create the tables, so I set them up manually first in the database itself. Worked fine.

ç i p h é r · Post by **ç i p h é r** » Mon Aug 27, 2007 9:11 pm

It might just be a MySQL version issue. It looks like it's choking on the "bool" syntax.

Hialmar, can you tell us what MySQL version you're running?

AcadiusLost · Post by **AcadiusLost** » Mon Aug 27, 2007 9:19 pm

It's pining for past Infra Administrators!

Or not. Question will be, is it easier to change the table creation code, or to upgrade the SQL server?

ç i p h é r · Post by **ç i p h é r** » Mon Aug 27, 2007 10:27 pm

We can switch to an alternate syntax rather easily, but I think we should upgrade to the latest version of MySQL if possible, assuming of course that it's the root cause of the problem.