Need help anyone how can i fix this?

[GameAdvisor]Finn · 12/07/2018, 17:05

Hi, Epvpers i have a problem on DP to NPC..
and its work .. but when have many players online.. the DP to NPC not work and we need to restart the server to work again.. and i see gamelog is not responding when i reinstall so i need to end task the gamelog to reinstall..
may i know what cause of this problem??

thank you in advance who answer my question thank you!

oldjayy · 12/09/2018, 11:57

Seems like poorly written SQL script or some kind of conflict when executing the stored procedure.

[GameAdvisor]Finn · 12/09/2018, 12:10

Quote:

Originally Posted by oldjayy

Seems like poorly written SQL script or some kind of conflict when executing the stored procedure.

so do you think the problem is SQL Script?
because dp to npc is work but when have at least 100+ Player ingame..
DP to NPC not work.. and when i try to uninstall ps_gamelog.exe ..ill get not responding..

{Skrillex} · 12/09/2018, 16:05

Quote:

Originally Posted by [GameAdvisor]Finn

so do you think the problem is SQL Script?
because dp to npc is work but when have at least 100+ Player ingame..
DP to NPC not work.. and when i try to uninstall ps_gamelog.exe ..ill get not responding..

Hi,
are you using this script with usp_Insert_Action_Log_E Stored procedure?
Or with a trigger?
Maybe of this script in the procedure your actionlog is overloaded

[GameAdvisor]Finn · 12/09/2018, 16:19

Quote:

Originally Posted by .:Unix:.

Hi,
are you using this script with usp_Insert_Action_Log_E Stored procedure?
Or with a trigger?
Maybe of this script in the procedure your actionlog is overloaded

i use usp_Insert_Action_Log_E Stored procedure
i think that also.. can you help how can i fix?

{Skrillex} · 12/09/2018, 16:27

Instead of a procedure use a trigger.

PHP Code:



USE [PS_GameLog]
GO
/****** Object: Trigger [dbo].[Action] Script Date: 12/09/2018 16:26:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Action]
on [PS_GameLog].[dbo].[ActionLog]
for insert
as


DECLARE [MENTION=292198]Action[/MENTION]Type tinyint,
@AT int, [MENTION=2805776]UserU[/MENTION]ID int, [MENTION=1134588]item[/MENTION]ID int, [MENTION=414337]count[/MENTION] tinyint
--put your DP-Script here.

END

Hope its easy to understand

[GameAdvisor]Finn · 12/09/2018, 16:31

Quote:

Originally Posted by .:Unix:.

Instead of a procedure use a trigger.

PHP Code:

USE [PS_GameLog] GO /****** Object: Trigger [dbo].[Action] Script Date: 12/09/2018 16:26:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[Action] on [PS_GameLog].[dbo].[ActionLog] for insert as DECLARE [MENTION=292198]Action[/MENTION]Type tinyint, @AT int, [MENTION=2805776]UserU[/MENTION]ID int, [MENTION=1134588]item[/MENTION]ID int, [MENTION=414337]count[/MENTION] tinyint --put your DP-Script here. END

Hope its easy to understand

i delete first the DP Script in Store Procedure right? before
add this trigger?

{Skrillex} · 12/09/2018, 16:32

Quote:

Originally Posted by [GameAdvisor]Finn

i delete first the DP Script in Store Procedure right? before
add this trigger?

Yes. Maybe you need to modify a bit. But should work

[GameAdvisor]Finn · 12/09/2018, 16:33

Quote:

Originally Posted by .:Unix:.

Yes. Maybe you need to modify a bit. But should work

Thanks .:Unix:. i hope this is work and no more bug DP

Quote:

Originally Posted by .:Unix:.

Yes. Maybe you need to modify a bit. But should work

its work but in a few hours its not working again.. i remove DP script on Stored Procedure and i query the trigger..

Velocity. · 12/11/2018, 18:30

Go ahead and give this a try..

Spoiler

{Skrillex} · 12/11/2018, 18:43

Quote:

Originally Posted by Velocity.

Go ahead and give this a try..

Spoiler

USE [PS_GameLog]
GO
/****** Object: Trigger [dbo].[GetDP] Script Date: 12/11/2018 9:13:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <[Dev]Velocity>
-- Create date: <12-11-18>
-- Description: <Get DP>
-- =============================================
CREATE trigger [dbo].[GetDP] on [PS_GameLog].[dbo].[ActionLog]
after insert, update
as
begin
declare @ID int = (select UserUID from inserted)
DECLARE @D int = (select CharID from inserted)
DECLARE @Type tinyint = (Select ActionType FROM inserted)
DECLARE @ varchar(100) = (Select Text2 FROM inserted)
DECLARE @ int = (Select Value2 FROM inserted)
DECLARE @4 int = (Select Value4 FROM inserted)

IF( @Type = 114 )
BEGIN
IF( @ = 94007 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 1 * @4 )
WHERE useruid = @ID
END

IF( @ = 94008 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 2 * @4 )
WHERE useruid = @ID
END
IF( @ = 94009 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 3 * @4 )
WHERE useruid = @ID
END
IF( @ = 94010 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 4 * @4 )
WHERE useruid = @ID
END
IF( @ = 94011 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 5 * @4 )
WHERE useruid = @ID
END
IF( @ = 94012 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 6 * @4 )
WHERE useruid = @ID
END
IF( @ = 94013 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 7 * @4 )
WHERE useruid = @ID
END
IF( @ = 94014 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 8 * @4 )
WHERE useruid = @ID
END
IF( @ = 94025 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 1 * @4 )
WHERE useruid = @ID
END
END
END

Hi,
Its again much code for the ActionLog. Its wayne if in procedure or in a trigger. You need to do the assebly way. There is no overloading.

Your server is "too" active for sql way.

Regards

Velocity. · 12/11/2018, 18:52

Quote:

Originally Posted by .:Unix:.

Hi,
Its again much code for the ActionLog. Its wayne if in procedure or in a trigger. You need to do the assebly way. There is no overloading.

Your server is "too" active for sql way.

Regards

I've had many players online and used a script similar to this one and never had any problems with it. anyway, goodluck.

[GameAdvisor]Finn · 12/12/2018, 12:14

Quote:

Originally Posted by Velocity.

Go ahead and give this a try..

Spoiler

USE [PS_GameLog]
GO
/****** Object: Trigger [dbo].[GetDP] Script Date: 12/11/2018 9:13:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <[Dev]Velocity>
-- Create date: <12-11-18>
-- Description: <Get DP>
-- =============================================
CREATE trigger [dbo].[GetDP] on [PS_GameLog].[dbo].[ActionLog]
after insert, update
as
begin
declare @ID int = (select UserUID from inserted)
DECLARE @D int = (select CharID from inserted)
DECLARE @Type tinyint = (Select ActionType FROM inserted)
DECLARE @ varchar(100) = (Select Text2 FROM inserted)
DECLARE @ int = (Select Value2 FROM inserted)
DECLARE @4 int = (Select Value4 FROM inserted)

IF( @Type = 114 )
BEGIN
IF( @ = 94007 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 1 * @4 )
WHERE useruid = @ID
END

IF( @ = 94008 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 2 * @4 )
WHERE useruid = @ID
END
IF( @ = 94009 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 3 * @4 )
WHERE useruid = @ID
END
IF( @ = 94010 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 4 * @4 )
WHERE useruid = @ID
END
IF( @ = 94011 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 5 * @4 )
WHERE useruid = @ID
END
IF( @ = 94012 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 6 * @4 )
WHERE useruid = @ID
END
IF( @ = 94013 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 7 * @4 )
WHERE useruid = @ID
END
IF( @ = 94014 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 8 * @4 )
WHERE useruid = @ID
END
IF( @ = 94025 )
BEGIN
UPDATE ps_userdata.dbo.users_master
SET point = point + ( 1 * @4 )
WHERE useruid = @ID
END
END
END

Thanks @

i hope this is work and not getting bug again .. in a few hours..

if still getting bug again.. i think i need to try unix said

by the way thanks again..

[MXD]Takeshi · 12/19/2018, 13:38

you can try to use something like this in your procedure

Code:

-- ActionType: 114 = Get AP by Selling Item to NPC
-- ActionType: 112 = Get AP by using it
SET @AT = 112

IF ( [MENTION=292198]Action[/MENTION]Type = @AT )
BEGIN

-- 50 AP Card

	IF (@ItemID = 100218)
	BEGIN 
	
		UPDATE PS_UserData.dbo.Users_Master 
		SET Point = (Point + [MENTION=635621]vaLue[/MENTION]4 * 50) 
		WHERE UserUID = [MENTION=2805776]UserU[/MENTION]ID
		
	END


-- 100 AP Card

	IF (@ItemID = 100219)
	BEGIN 
	
		UPDATE PS_UserData.dbo.Users_Master 
		SET Point = (Point + [MENTION=635621]vaLue[/MENTION]4 * 100) 
		WHERE UserUID = [MENTION=2805776]UserU[/MENTION]ID
		
	END

End

every time worked, never get error or other, anyway is possible ur action_log procedure is "broked" and for this u get some deadlock, can try to add a waiting before execute, so in this way you can create a sort of traffic light for the queue