Jump to content

New issue to annoy you with =P

Closed 1.0.17 1.1.8
CaptainNebulous

By CaptainNebulous,

  • Report this file
    • CaptainNebulous

    So I finally got this working. Turned out I had to rename the file in the SQL to get it to work with my host's chosen file name. But after wipe I am now getting this in the logs

      

    [Oxide] 11:47 [Error] MySql handle raised an exception in 'BetterStatistics v1.0.17' plugin (MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'overall.nailgun = overall.nailgun + wipe.nailgun

WHERE

overall.stea...' at line 58)

at MySql.Data.MySqlClient.MySqlCommand.EndExecuteReader (System.IAsyncResult result) [0x00022] in <8d7380b3d3cf4bd8be324c584f371b44>:0

at (wrapper remoting-invoke-with-check) MySql.Data.MySqlClient.MySqlCommand.EndExecuteReader(System.IAsyncResult)

at Oxide.Core.MySql.Libraries.MySql+MySqlQuery.Handle () [0x0010e] in <021a1dba6a754e42a6cc7771fdfee190>:0


    And it doesn't save the all time data. Also when players try to look up other players' stats it says there is no wipe info for that player, even though their stats are clearly being saved in the wipe section and everyone can look up their own stats.

    I only made the following changes to the SQL to get it to work with my host : 
    Removed line #1 since my host does not allow that function, and it seems to be fairly irrelevant
    Renamed references to file name "BetterStatistics" to the file name my host chooses for me

    Here is the slightly modified DB I am using 

      

    CREATE DATABASE IF NOT EXISTS `christopher7_stats`;
USE `christopher7_stats`;

DROP PROCEDURE IF EXISTS `createTable`;
DELIMITER //
CREATE PROCEDURE `createTable`(
	IN `tableName` VARCHAR(200)
)
BEGIN
	SET @name = tableName;
	SET @st = CONCAT('
	   CREATE TABLE IF NOT EXISTS `' , @name, '` (
		`id` INT(4) NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(50) NULL DEFAULT NULL,
		`steamid` VARCHAR(18) NULL DEFAULT NULL,
		`connections` INT(11) NOT NULL DEFAULT 0,
		`playtime` INT(11) NOT NULL DEFAULT 0,
		`kills` INT(11) NOT NULL DEFAULT 0,
		`deaths` INT(11) NOT NULL DEFAULT 0,
		`bfired` INT(11) NOT NULL DEFAULT 0,
		`suicides` INT(11) NOT NULL DEFAULT 0,
		`wounded` INT(11) NOT NULL DEFAULT 0,
		`c4thrown` INT(11) NOT NULL DEFAULT 0,
		`satchelsthrown` INT(11) NOT NULL DEFAULT 0,
		`rocketsfired` INT(11) NOT NULL DEFAULT 0,
		`tcsdestroyed` INT(11) NOT NULL DEFAULT 0,
		`chickens` INT(11) NOT NULL DEFAULT 0,
		`boars` INT(11) NOT NULL DEFAULT 0,
		`deers` INT(11) NOT NULL DEFAULT 0,
		`horses` INT(11) NOT NULL DEFAULT 0,
		`wolves` INT(11) NOT NULL DEFAULT 0,
		`bears` INT(11) NOT NULL DEFAULT 0,
		`scientists` INT(11) NOT NULL DEFAULT 0,
		`helicopters` INT(11) NOT NULL DEFAULT 0,
		`bradleys` INT(11) NOT NULL DEFAULT 0,
		`ak47` INT(11) NOT NULL DEFAULT 0,
		`lr300` INT(11) NOT NULL DEFAULT 0,
		`m39` INT(11) NOT NULL DEFAULT 0,
		`sar` INT(11) NOT NULL DEFAULT 0,
		`m249` INT(11) NOT NULL DEFAULT 0,
		`hmlmg` INT(11) NOT NULL DEFAULT 0,
		`l96` INT(11) NOT NULL DEFAULT 0,
		`bolt` INT(11) NOT NULL DEFAULT 0,
		`mp5` INT(11) NOT NULL DEFAULT 0,
		`thompson` INT(11) NOT NULL DEFAULT 0,
		`custom` INT(11) NOT NULL DEFAULT 0,
		`pump` INT(11) NOT NULL DEFAULT 0,
		`doublebarrel` INT(11) NOT NULL DEFAULT 0,
		`spaz12` INT(11) NOT NULL DEFAULT 0,
		`m92` INT(11) NOT NULL DEFAULT 0,
		`python` INT(11) NOT NULL DEFAULT 0,
		`semipistol` INT(11) NOT NULL DEFAULT 0,
		`revolver` INT(11) NOT NULL DEFAULT 0,
		`waterpipe` INT(11) NOT NULL DEFAULT 0,
		`eoka` INT(11) NOT NULL DEFAULT 0,
		`compound` INT(11) NOT NULL DEFAULT 0,
		`crossbow` INT(11) NOT NULL DEFAULT 0,
		`bow` INT(11) NOT NULL DEFAULT 0,
		`head_hits` INT(11) NOT NULL DEFAULT 0,
		`torso_hits` INT(11) NOT NULL DEFAULT 0,
		`leftarm_hits` INT(11) NOT NULL DEFAULT 0,
		`rightarm_hits` INT(11) NOT NULL DEFAULT 0,
		`leftleg_hits` INT(11) NOT NULL DEFAULT 0,
		`rightleg_hits` INT(11) NOT NULL DEFAULT 0,
		`leftfoot_hits` INT(11) NOT NULL DEFAULT 0,
		`rightfoot_hits` INT(11) NOT NULL DEFAULT 0,
		`prototype17` INT(11) NOT NULL DEFAULT 0,
		`nailgun` INT(11) NOT NULL DEFAULT 0,
		PRIMARY KEY (`id`) USING BTREE,
		INDEX `steamid` (`steamid`) USING BTREE
	   )
	 ');
	 PREPARE myStatement FROM @st;
	 EXECUTE myStatement;
	 DEALLOCATE PREPARE myStatement;
 END//
DELIMITER ;


DROP PROCEDURE IF EXISTS `insertStats`;
DELIMITER //
CREATE PROCEDURE `insertStats`(
	IN `serverPrefix` VARCHAR(50)
)
BEGIN
	SET @name = serverPrefix;
	SET @st = CONCAT('
		INSERT INTO `' , @name, '_stats_overall` (name, steamid, playtime, connections, kills, deaths, bfired, suicides, wounded, rocketsfired, c4thrown, satchelsthrown, tcsdestroyed, chickens, boars, deers, horses, wolves, bears, scientists, helicopters, bradleys, ak47, lr300, m39, sar, hmlmg, m249, bolt, l96, mp5, thompson, custom, pump, doublebarrel, spaz12, m92, python, semipistol, revolver, waterpipe, eoka, compound, crossbow, bow, head_hits, torso_hits, leftarm_hits, rightarm_hits, leftleg_hits, rightleg_hits, leftfoot_hits, rightfoot_hits, prototype17, nailgun)
		SELECT name, steamid, playtime, connections, kills, deaths, bfired, suicides, wounded, rocketsfired, c4thrown, satchelsthrown, tcsdestroyed, chickens, boars, deers, horses, wolves, bears, scientists, helicopters, bradleys, ak47, lr300, m39, sar, hmlmg, m249, bolt, l96, mp5, thompson, custom, pump, doublebarrel, spaz12, m92, python, semipistol, revolver, waterpipe, eoka, compound, crossbow, bow, head_hits, torso_hits, leftarm_hits, rightarm_hits, leftleg_hits, rightleg_hits, leftfoot_hits, rightfoot_hits, prototype17, nailgun
		FROM `' , @name, '_stats_wipe`
		WHERE NOT EXISTS (SELECT * FROM ' , @name, '_stats_overall WHERE steamid = ' , @name, '_stats_wipe.steamid)
	');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS `updateData`;
DELIMITER //
CREATE PROCEDURE `updateData`(
	IN `serverPrefix` VARCHAR(50)
)
BEGIN
	SET @name = serverPrefix;
	SET @st = CONCAT('
		UPDATE
		    ' , @name, '_stats_wipe wipe,
		    ' , @name, '_stats_overall overall
		SET
			 overall.name = wipe.name,
		    overall.connections = overall.connections + wipe.connections,
		    overall.playtime = overall.playtime + wipe.playtime,
		    overall.kills = overall.kills + wipe.kills,
		    overall.deaths = overall.deaths + wipe.deaths,
		    overall.bfired = overall.bfired + wipe.bfired,
		    overall.suicides = overall.suicides + wipe.suicides,
		    overall.wounded = overall.wounded + wipe.wounded,
		    overall.rocketsfired = overall.rocketsfired + wipe.rocketsfired,
		    overall.c4thrown = overall.c4thrown + wipe.c4thrown,
		    overall.satchelsthrown = overall.satchelsthrown + wipe.satchelsthrown,
		    overall.tcsdestroyed = overall.tcsdestroyed + wipe.tcsdestroyed,
		    overall.chickens = overall.chickens + wipe.chickens,
		    overall.boars = overall.boars + wipe.boars,
		    overall.deers = overall.deers + wipe.deers,
		    overall.horses = overall.horses + wipe.horses,
		    overall.wolves = overall.wolves + wipe.wolves,
		    overall.bears = overall.bears + wipe.bears,
		    overall.scientists = overall.scientists + wipe.scientists,
		    overall.helicopters = overall.helicopters + wipe.helicopters,
		    overall.bradleys = overall.bradleys + wipe.bradleys,
		    overall.ak47 = overall.ak47 + wipe.ak47,
		    overall.lr300 = overall.lr300 + wipe.lr300,
		    overall.m39 = overall.m39 + wipe.m39,
		    overall.sar = overall.sar + wipe.sar,
		    overall.hmlmg = overall.hmlmg + wipe.hmlmg,
		    overall.m249 = overall.m249 + wipe.m249,
		    overall.bolt = overall.bolt + wipe.bolt,
		    overall.l96 = overall.l96 + wipe.l96,
		    overall.mp5 = overall.mp5 + wipe.mp5,
		    overall.thompson = overall.thompson + wipe.thompson,
		    overall.custom = overall.custom + wipe.custom,
		    overall.pump = overall.pump + wipe.pump,
		    overall.doublebarrel = overall.doublebarrel + wipe.doublebarrel,
		    overall.spaz12 = overall.spaz12 + wipe.spaz12,
		    overall.m92 = overall.m92 + wipe.m92,
		    overall.python = overall.python + wipe.python,
		    overall.semipistol = overall.semipistol + wipe.semipistol,
		    overall.revolver = overall.revolver + wipe.revolver,
		    overall.waterpipe = overall.waterpipe + wipe.waterpipe,
		    overall.eoka = overall.eoka + wipe.eoka,
		    overall.compound = overall.compound + wipe.compound,
		    overall.crossbow = overall.crossbow + wipe.crossbow,
		    overall.bow = overall.bow + wipe.bow,
		    overall.head_hits = overall.head_hits + wipe.head_hits,
		    overall.torso_hits = overall.torso_hits + wipe.torso_hits,
		    overall.leftarm_hits = overall.leftarm_hits + wipe.leftarm_hits,
		    overall.rightarm_hits = overall.rightarm_hits + wipe.rightarm_hits,
		    overall.leftleg_hits = overall.leftleg_hits + wipe.leftleg_hits,
		    overall.rightleg_hits = overall.rightleg_hits + wipe.rightleg_hits,
		    overall.leftfoot_hits = overall.leftfoot_hits + wipe.leftfoot_hits,
		    overall.rightfoot_hits = overall.rightfoot_hits + wipe.rightfoot_hits,
			overall.prototype17 = overall.prototype17 + wipe.prototype17
			overall.nailgun = overall.nailgun + wipe.nailgun
		WHERE
		    overall.steamid = wipe.steamid;
	');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS `wipeTime`;
DELIMITER //
CREATE PROCEDURE `wipeTime`(
	IN `serverPrefix` VARCHAR(50)
)
BEGIN
	SET @serverPrefix = serverPrefix;
	CALL updateData(@serverPrefix);
	CALL insertStats(@serverPrefix);
	
	SET @st = CONCAT('TRUNCATE TABLE ', @serverPrefix, '_stats_wipe');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END//
DELIMITER ;

     

    Edited by CaptainNebulous

    Share this comment

    Link to comment
    McGee007 
    DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createTable`(IN `tableName` VARCHAR(200))
BEGIN
	SET @name = tableName;
	SET @st = CONCAT('
	   CREATE TABLE IF NOT EXISTS `' , @name, '` (
	      `id` INT(4) NOT NULL AUTO_INCREMENT,
	      `name` VARCHAR(50) NULL DEFAULT NULL,
	      `steamid` VARCHAR(18) NULL DEFAULT NULL,
	      `connections` INT(11) NOT NULL DEFAULT 0,
	      `playtime` INT(11) NOT NULL DEFAULT 0,
	      `kills` INT(11) NOT NULL DEFAULT 0,
	      `deaths` INT(11) NOT NULL DEFAULT 0,
	      `bfired` INT(11) NOT NULL DEFAULT 0,
	      `suicides` INT(11) NOT NULL DEFAULT 0,
	      `wounded` INT(11) NOT NULL DEFAULT 0,
	      `c4thrown` INT(11) NOT NULL DEFAULT 0,
	      `satchelsthrown` INT(11) NOT NULL DEFAULT 0,
	      `rocketsfired` INT(11) NOT NULL DEFAULT 0,
	      `tcsdestroyed` INT(11) NOT NULL DEFAULT 0,
	      `chickens` INT(11) NOT NULL DEFAULT 0,
	      `boars` INT(11) NOT NULL DEFAULT 0,
	      `deers` INT(11) NOT NULL DEFAULT 0,
	      `horses` INT(11) NOT NULL DEFAULT 0,
	      `wolves` INT(11) NOT NULL DEFAULT 0,
	      `bears` INT(11) NOT NULL DEFAULT 0,
	      `scientists` INT(11) NOT NULL DEFAULT 0,
	      `helicopters` INT(11) NOT NULL DEFAULT 0,
	      `bradleys` INT(11) NOT NULL DEFAULT 0,
		   `ak47` INT(11) NOT NULL DEFAULT 0,
			`lr300` INT(11) NOT NULL DEFAULT 0,
			`m39` INT(11) NOT NULL DEFAULT 0,
			`sar` INT(11) NOT NULL DEFAULT 0,
			`m249` INT(11) NOT NULL DEFAULT 0,
			`hmlmg` INT(11) NOT NULL DEFAULT 0,
			`l96` INT(11) NOT NULL DEFAULT 0,
			`bolt` INT(11) NOT NULL DEFAULT 0,
			`mp5` INT(11) NOT NULL DEFAULT 0,
			`thompson` INT(11) NOT NULL DEFAULT 0,
			`custom` INT(11) NOT NULL DEFAULT 0,
			`pump` INT(11) NOT NULL DEFAULT 0,
			`doublebarrel` INT(11) NOT NULL DEFAULT 0,
			`spaz12` INT(11) NOT NULL DEFAULT 0,
			`m92` INT(11) NOT NULL DEFAULT 0,
			`python` INT(11) NOT NULL DEFAULT 0,
			`semipistol` INT(11) NOT NULL DEFAULT 0,
			`revolver` INT(11) NOT NULL DEFAULT 0,
			`waterpipe` INT(11) NOT NULL DEFAULT 0,
			`eoka` INT(11) NOT NULL DEFAULT 0,
			`compound` INT(11) NOT NULL DEFAULT 0,
			`crossbow` INT(11) NOT NULL DEFAULT 0,
			`bow` INT(11) NOT NULL DEFAULT 0,
			`head_hits` INT(11) NOT NULL DEFAULT 0,
			`torso_hits` INT(11) NOT NULL DEFAULT 0,
			`leftarm_hits` INT(11) NOT NULL DEFAULT 0,
			`rightarm_hits` INT(11) NOT NULL DEFAULT 0,
			`leftleg_hits` INT(11) NOT NULL DEFAULT 0,
			`rightleg_hits` INT(11) NOT NULL DEFAULT 0,
			`leftfoot_hits` INT(11) NOT NULL DEFAULT 0,
			`rightfoot_hits` INT(11) NOT NULL DEFAULT 0,
            `prototype17` INT(11) NOT NULL DEFAULT 0,
            `nailgun` INT(11) NOT NULL DEFAULT 0,
	      PRIMARY KEY (`id`) USING BTREE,
	      INDEX `steamid` (`steamid`) USING BTREE
	   )
	 ');
	 PREPARE myStatement FROM @st;
	 EXECUTE myStatement;
	 DEALLOCATE PREPARE myStatement;
 END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertStats`(IN `serverPrefix` VARCHAR(50))
BEGIN
	SET @name = serverPrefix;
	SET @st = CONCAT('
		INSERT INTO `' , @name, '_stats_overall` (name, steamid, playtime, connections, kills, deaths, bfired, suicides, wounded, rocketsfired, c4thrown, satchelsthrown, tcsdestroyed, chickens, boars, deers, horses, wolves, bears, scientists, helicopters, bradleys, ak47, lr300, m39, sar, hmlmg, m249, bolt, l96, mp5, thompson, custom, pump, doublebarrel, spaz12, m92, python, semipistol, revolver, waterpipe, eoka, compound, crossbow, bow, head_hits, torso_hits, leftarm_hits, rightarm_hits, leftleg_hits, rightleg_hits, leftfoot_hits, rightfoot_hits, prototype17, nailgun)
		SELECT name, steamid, playtime, connections, kills, deaths, bfired, suicides, wounded, rocketsfired, c4thrown, satchelsthrown, tcsdestroyed, chickens, boars, deers, horses, wolves, bears, scientists, helicopters, bradleys, ak47, lr300, m39, sar, hmlmg, m249, bolt, l96, mp5, thompson, custom, pump, doublebarrel, spaz12, m92, python, semipistol, revolver, waterpipe, eoka, compound, crossbow, bow, head_hits, torso_hits, leftarm_hits, rightarm_hits, leftleg_hits, rightleg_hits, leftfoot_hits, rightfoot_hits, prototype17, nailgun
		FROM `' , @name, '_stats_wipe`
		WHERE NOT EXISTS (SELECT * FROM ' , @name, '_stats_overall WHERE steamid = ' , @name, '_stats_wipe.steamid)
	');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateData`(IN `serverPrefix` VARCHAR(50))
BEGIN
	SET @name = serverPrefix;
	SET @st = CONCAT('
		UPDATE
		    ' , @name, '_stats_wipe wipe,
		    ' , @name, '_stats_overall overall
		SET
			 overall.name = wipe.name,
		    overall.connections = overall.connections + wipe.connections,
		    overall.playtime = overall.playtime + wipe.playtime,
		    overall.kills = overall.kills + wipe.kills,
		    overall.deaths = overall.deaths + wipe.deaths,
		    overall.bfired = overall.bfired + wipe.bfired,
		    overall.suicides = overall.suicides + wipe.suicides,
		    overall.wounded = overall.wounded + wipe.wounded,
		    overall.rocketsfired = overall.rocketsfired + wipe.rocketsfired,
		    overall.c4thrown = overall.c4thrown + wipe.c4thrown,
		    overall.satchelsthrown = overall.satchelsthrown + wipe.satchelsthrown,
		    overall.tcsdestroyed = overall.tcsdestroyed + wipe.tcsdestroyed,
		    overall.chickens = overall.chickens + wipe.chickens,
		    overall.boars = overall.boars + wipe.boars,
		    overall.deers = overall.deers + wipe.deers,
		    overall.horses = overall.horses + wipe.horses,
		    overall.wolves = overall.wolves + wipe.wolves,
		    overall.bears = overall.bears + wipe.bears,
		    overall.scientists = overall.scientists + wipe.scientists,
		    overall.helicopters = overall.helicopters + wipe.helicopters,
		    overall.bradleys = overall.bradleys + wipe.bradleys,
		    overall.ak47 = overall.ak47 + wipe.ak47,
		    overall.lr300 = overall.lr300 + wipe.lr300,
		    overall.m39 = overall.m39 + wipe.m39,
		    overall.sar = overall.sar + wipe.sar,
		    overall.hmlmg = overall.hmlmg + wipe.hmlmg,
		    overall.m249 = overall.m249 + wipe.m249,
		    overall.bolt = overall.bolt + wipe.bolt,
		    overall.l96 = overall.l96 + wipe.l96,
		    overall.mp5 = overall.mp5 + wipe.mp5,
		    overall.thompson = overall.thompson + wipe.thompson,
		    overall.custom = overall.custom + wipe.custom,
		    overall.pump = overall.pump + wipe.pump,
		    overall.doublebarrel = overall.doublebarrel + wipe.doublebarrel,
		    overall.spaz12 = overall.spaz12 + wipe.spaz12,
		    overall.m92 = overall.m92 + wipe.m92,
		    overall.python = overall.python + wipe.python,
		    overall.semipistol = overall.semipistol + wipe.semipistol,
		    overall.revolver = overall.revolver + wipe.revolver,
		    overall.waterpipe = overall.waterpipe + wipe.waterpipe,
		    overall.eoka = overall.eoka + wipe.eoka,
		    overall.compound = overall.compound + wipe.compound,
		    overall.crossbow = overall.crossbow + wipe.crossbow,
		    overall.bow = overall.bow + wipe.bow,
		    overall.head_hits = overall.head_hits + wipe.head_hits,
		    overall.torso_hits = overall.torso_hits + wipe.torso_hits,
		    overall.leftarm_hits = overall.leftarm_hits + wipe.leftarm_hits,
		    overall.rightarm_hits = overall.rightarm_hits + wipe.rightarm_hits,
		    overall.leftleg_hits = overall.leftleg_hits + wipe.leftleg_hits,
		    overall.rightleg_hits = overall.rightleg_hits + wipe.rightleg_hits,
		    overall.leftfoot_hits = overall.leftfoot_hits + wipe.leftfoot_hits,
		    overall.rightfoot_hits = overall.rightfoot_hits + wipe.rightfoot_hits,
            overall.prototype17 = overall.prototype17 + wipe.prototype17,
            overall.nailgun = overall.nailgun + wipe.nailgun
   
		WHERE
		    overall.steamid = wipe.steamid;
	');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `wipeTime`(IN `serverPrefix` VARCHAR(50))
BEGIN
	SET @serverPrefix = serverPrefix;
	CALL updateData(@serverPrefix);
	CALL insertStats(@serverPrefix);
	
	SET @st = CONCAT('TRUNCATE TABLE ', @serverPrefix, '_stats_wipe');
	PREPARE myStatement FROM @st;
	EXECUTE myStatement;
	DEALLOCATE PREPARE myStatement;
END$$
DELIMITER ;

    This works for us no issues

    Share this comment

    Link to comment
    1.1m

    Downloads

    Total number of downloads.

    5.7k

    Customers

    Total customers served.

    82.8k

    Files Sold

    Total number of files sold.

    1.6m

    Payments Processed

    Total payments processed.

    Customers

    Providers

    Resources

    About Us

    Codefling is the largest marketplace to buy and sell plugins, maps, tools, and more! Our modern platform makes it easy to find exciting new content while providing a feature-rich environment for creators to monetize their work!

    ×
    ×
    • Create New...

    Important Information

    We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.