Fix for negative Guild Points on rebirth servers

[lemoniscool]

Hi!
On servers that feature a rebirth the guilds and guild members earn a lot of GP which leads to negative GP when the guild or the guild member achieves more than ~2,2b GP the following query will fix that and will set the GP to the maximum value it can be. But this will work on the Database only, the Gameserver will still update the value inside its memory and send a packet to the client which will then update the value to a negative value .. the value will only be reset ingame after restarting the gameserver

Thanks to Caosfox for the hint to use triggers, you helped me a lot man!

greetz

USE SRO_VT_SHARD
GO

CREATE TRIGGER TR_GPCHECK_CHAR ON _GuildMember AFTER UPDATE
AS
BEGIN
    declare @NewGP as int = (select GP_Donation from inserted)
    declare @CharID as int = (select CharID from inserted)
    if(@NewGP > 2147483647 or @NewGP < 0)
    begin
        update _GuildMember set GP_Donation = 2147483647 where CharID = @CharID
    end
END
GO

CREATE TRIGGER TR_GPCHECK ON _Guild AFTER UPDATE
AS
BEGIN
    declare @NewGP as int = (select GatheredSP from inserted)
    declare @GuildID as int = (select ID from inserted)
    if(@NewGP > 2147483647 or @NewGP < 0)
    begin
        update _Guild set GatheredSP = 2147483647 where ID = @GuildID
    end
END
GO 

[MentaL]

[Comandante]

Haven't got that issue yet but it's good to know :)) thanks for sharing that trigger

[MarcoTozzi]

Will trigger help in this case? Because from my experience it will be only in database but in game you will still have negative, cuz edits to Guild/related can't be done while server is running.
And I see the code, just wonder if you test it and work for you :)

[lemoniscool]

ah damn no you're right, i just checked the database side where it worked just fine .. thought the server would update the info once in a while from the database ill recheck and see if i can make anything working without many errors .. i used a CHECK-constraint before which even worked on client side but it caused a lot overlap count in shardmanager and thousands of errors every second

[sladlejrhfpq]

well, I'm not sure this will work, but try it if you can:

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_EditGuildStat]    Script Date: 06/22/2013 01:42:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

----
	ALTER  procedure [dbo].[_EditGuildStat]
		@Name		varchar(64),
		@lvl		tinyint,
		@sp		int,
		@LastCrestRev	int,
		@CurCrestRev	int
	as
		-- not existing guild
		declare @id int
		set @id = 0
		select @id = [ID] from _guild where [Name] = @Name
		if (@@rowcount = 0 or @id is null or @id = 0)
		begin
			select -2
			return
		end
		
		IF(@sp > 2147483647)
		BEGIN
			SELECT @sp = 2147483647
		END
		
		-- failed to update
		update _guild set Lvl = @lvl, GatheredSP = @sp, LastCrestRev = @LastCrestRev, CurCrestRev = @CurCrestRev where [ID] = @id
		if (@@error <> 0 or @@rowcount = 0)
		begin
			select -4
			return
		end
		select 1
		return

[denise456]

Thank u good job bro

[Isoline]

Thanks man !! nice work :)

[lemoniscool]

@sladlejrhfpq: no this SP doesnt work for client side and if im right it doesnt work for server side either afaik the SP isnt even executed by the server

[Comandante]

What about resetting gp on the rebirth?

[lemoniscool]

the problem is, if the gameserver does succeed without any error it will change the GP inside its memory and send a packet to the client changeing the gP to a negative value .. im already working on a solution for it

//Edit:
just to keep you updated .. ive found the packet thats being sent by the gameserver to increase the GP of a guild when someone donates GP manually (will use this to test the whole thing and then check for all other packets):

11 00 F7 78 00 00 2A C3 19 00 00 08 00 00 00 44 00 00 00 05 00 00 00

11 00 F7 78 00 00 2A = Unknown (Always the same propably the first 2 Bytes are to Identify the packet, the others i dont know)
C3 19 00 00 = Unknown (4 Bytes Integer), changes from char to char but is not the Char ID
08 00 00 00 = Guild ID (4 Bytes Integer)
44 00 00 00 = Unknown (4 Bytes Integer)
05 00 00 00 = Amount of GP to add to guild (4 Bytes Integer)

Note: to convert the hex values in here flip them first, for example to convert the GuildID from the package to decimal you first have to make 08 00 00 00 to 00 00 00 08 which then can be converted with the windows calculator and equals 8 in decimal .. another example would be to convert 1053846 GP from decimal to hexadecimal integer for the packet you need to convert it to hex first which would be 00 10 14 96 so inside the packet it would be 96 14 10 00

anyways .. all i need now is some kind of filter (proxy) that when recieving the packet above reads the gp of that guild from database and add the amount of gp the packet should add to it, if it is more than 2147483647 it should calculate how many gp can be added max and change the packet .. and then send it to the shardmanager .. its not too hard at all if i knew how to write a proxy for the gameserver .... ill keep you updated here. btw i changed the thread to development instead of release, if a mod would move it to development section would be nice

//Edit2:
found a way to filter packets by writing a dll that hooks the send function of the gameserver .. only thing annoying is that you need to inject the dll manually but it works.. to test the hook i wrote a simple packet counter you can see on the screen below only thing left now is the filtering and packet editing i explained above :D

[LogiN]

Hey! you research the gameserver and find the offset that works the function of SP, making the offset and use it as a boolean, compile you DLL and test.

Code:

bool MaxSkillPoint ( char * ini )
{
	DWORD SkillPoint;
	SkillPoint = GetPrivateProfileInt("GameServer","MaxSP",1500000000,ini);
	*(unsigned int *) (0x000000) = SkillPoint ;
	return 1;
}

you need define this process..

[MrSaxobeat]

i Use this Querry and Bug Union Guild, Bad Release....

[denise456]

i Use this Querry and Bug Union Guild, Bad Release....

Same Here

[lemoniscool]

its not a release, its a development thread, im done with it but due to the last problems with my latest release im not releasing anything anymore so .. figure it out yourself guys

[Gnako]

May just asking? .. Isn't it the same as in Java that int has the max trigger at 2,147 .... Then it should be just to change the "INT" to a "LONG" and the problem should be solved? :o