Jump to content

New issue to annoy you with =P

Closed 1.0.17 1.1.8
CaptainNebulous

By CaptainNebulous,

CaptainNebulous

CaptainNebulous 25

Posted (edited)

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
CaptainNebulous

CaptainNebulous 25

Posted

Just had another wipe. The plugin again failed to save my server's wipe stats

McGee007

McGee007 1

Posted 

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

Billy Joe

Billy Joe 108

Posted

Changed Status from Pending to Closed

Changed Fixed In to 1.1.8

1.4m

Downloads

Total number of downloads.

6.9k

Customers

Total customers served.

102.4k

Files Sold

Total number of files sold.

2.1m

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.