[TUT] ConnLog fix *Matchserver

**00niels00** · 19-06-10

Intro:
Ok recentrly I was going trough the matchserver logs. And I've found stuff like:

Code:

MMatchDBMgr::InsertConnLog - Nome de objeto 'LogDB.dbo.ConnLog' inválido.

MMatchDBMgr::InsertConnLog - Nome de objeto 'LogDB.dbo.ConnLog' inválido.

In this tutorial I gonna explain how to fix this and make it functional again.

What is ConLog:
ConLog is a MSSQL table where connection info and such getting stored in. Some servers crash because the table doesn't exist.

Make it functional again:
* I'm using the july 2008 database in the 2007 database it has another name
Step1- Open Mssql.
Step2- Right click 'Databases' and add a new database called 'LogDB'.

Step3- Execute this script:

PHP Code:


USE [LogDB]

GO

/****** Object:  Table [dbo].[ConnLog]    Script Date: 06/19/2010 08:53:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ConnLog](

    [AID] [int] NULL,

    [IPPart1] [tinyint] NULL,

    [IPPart2] [tinyint] NULL,

    [IPPart3] [tinyint] NULL,

    [IPPart4] [tinyint] NULL,

    [CountryCode3] [char](3) NULL,

    [Time] [varchar](50) NULL

) ON [PRIMARY]



GO

SET ANSI_PADDING OFF

Credits to me.

In case the execute failed follow these steps:
Step1- You've just created LogDB, right click 'Tables' and add a new table. Call this table ConnLog.
Step2- Make the design like this:

Completed.
If you've done it right you should get logs from everyone who connects. Also that error in the log isn't there anymore.

Screeny:

Tested on localhost
I couldn't get country code to work. Maybe I will look for that but I think it isn't supported by the matchserver.

**MentaL**

**Number12** · 19-06-10

Very nice mate. I remember seeing someone also posting about this error. Ima try to find the post and then tell him about this.

**Linear88** · 19-06-10

You don't need LogDB, you could just modify it to use GunzDB instead.

**00niels00** · 19-06-10

Originally Posted by Linear88

You don't need LogDB, you could just modify it to use GunzDB instead.

I know it's a simple edit of SpInsertConnLog.

Code:

USE [GunzDB]
GO
/****** Object:  StoredProcedure [dbo].[spInsertConnLog]    Script Date: 06/19/2010 10:25:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROC [dbo].[spInsertConnLog] 
 @AID int
, @IPPart1 tinyint
, @IPPart2 tinyint
, @IPPart3 tinyint
, @IPPart4 tinyint
, @CountryCode3	char(3)
AS
 SET NOCOUNT ON
 INSERT INTO LogDB.dbo.ConnLog( AID, Time, IPPart1, IPPart2, IPPart3, IPPart4, CountryCode3)
 VALUES (@AID, GETDATE(), @IPPart1, @IPPart2, @IPPart3, @IPPart4, @CountryCode3)

But I like to keep it MAIET's way.

**Phoenix** · 19-06-10

Or just execute these to scripts to fix it.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spRegularUpdateConnLog]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spRegularUpdateConnLog]
AS
 SET NOCOUNT ON

 DECLARE @Year char(4)
 DECLARE @Mon char(2)
 DECLARE @OldDate smalldatetime
 DECLARE @OldTableName varchar(128)

 SET @OldDate = DATEADD( mm, -1, GETDATE() )
 SET @Year = DATEPART(yy, @OldDate)
 SET @Mon = DATEPART(mm, @OldDate)

 SET @OldTableName = ''ConnLog_'' + @Year + 
  CASE WHEN @Mon < 10 THEN ''0'' + CAST(@Mon AS char(1))
  ELSE CAST(@Mon AS char(2)) END

 EXEC sp_rename ''ConnLog'', @OldTableName

 CREATE TABLE ConnLog(
  id int IDENTITY
 , AID int NOT NULL
 , Time smalldatetime
 , IPPart1 tinyint NOT NULL
 , IPPart2 tinyint NOT NULL
 , IPPart3 tinyint NOT NULL
 , IPPart4 tinyint NOT NULL
 , CountryCode3 char(3) )

 CREATE NONCLUSTERED INDEX IX_ConnLog_AID
 ON ConnLog( AID )

 CREATE NONCLUSTERED INDEX IX_ConnLog_Time
 ON ConnLog( Time )

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetCharClan]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetCharClan] 
 @CID   int  
AS  
 SET NOCOUNT ON
 SELECT cl.CLID AS CLID, cl.Name AS ClanName FROM ClanMember cm(nolock), Clan cl(nolock) WHERE cm.cid=@CID AND cm.CLID=cl.CLID  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanList]  
 @Page INT,  
 @Backward INT  = 0  
AS  
SET NOCOUNT ON
BEGIN  
 DECLARE @PageHead INT  
 DECLARE @RowCount INT  
  
 IF @Backward = 0  
 BEGIN  
  SELECT @RowCount = ((@Page -1) * 15 + 1)  
    
  SET ROWCOUNT @RowCount  
  SELECT @PageHead = CLID FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY CLID DESC  
    
  SET ROWCOUNT 15  
  SELECT cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point  
  FROM Clan cl(NOLOCK), Character c(nolock)  
  WHERE cl.MasterCID=c.CID AND cl.DeleteFlag=0 AND cl.CLID<@PageHead   
  ORDER BY cl.CLID DESC  
 END  
 ELSE  
 BEGIN -- 역순  
  SELECT @RowCount = ((@Page -1) * 15 + 1)  
    
  SET ROWCOUNT @RowCount  
  SELECT @PageHead = CLID FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY CLID  
    
  SET ROWCOUNT 15  
  SELECT CLID, ClanName, Master, RegDate, EmblemUrl, Point  
  FROM  
  (  
   SELECT TOP 15 cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point  
   FROM Clan cl(NOLOCK), Character c(nolock)  
   WHERE cl.MasterCID=c.CID AND cl.DeleteFlag=0 AND cl.CLID>=@PageHead ORDER BY cl.CLID  
  ) AS t  
  ORDER BY CLID DESC  
 END  
END  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanListSearchByName]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanListSearchByName]
 @Name VARCHAR(24)  
AS  
SET NOCOUNT ON
BEGIN  
 SELECT TOP 20 cl.CLID AS CLID, cl.Name as ClanName, c.Name AS Master, cl.RegDate AS RegDate, cl.EmblemUrl AS EmblemUrl, cl.Point AS Point  
 FROM Clan cl(NOLOCK), Character c(NOLOCK)  
 WHERE cl.MasterCID=c.CID AND c.DeleteFlag=0 AND cl.Name=@Name   
 ORDER BY cl.CLID  
END  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanMember]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanMember]  
 @CLID  int  
AS  
 SET NOCOUNT ON
 SELECT cm.clid AS CLID, cm.Grade AS ClanGrade, c.cid AS CID, c.name AS CharName  
 FROM ClanMember cm(nolock), Character c(nolock)  
 WHERE CLID=@CLID AND cm.cid=c.cid  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingHistory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE  PROC [dbo].[spWebGetClanRankingHistory]  
 @Year INT,  
 @Month INT,  
 @Page INT,  
 @Backward INT = 0  
AS  
SET NOCOUNT ON
BEGIN  
 /* 한페이지에 20개씩 보여준다 (속도를위해 갯수 고정) */  
 DECLARE @RowCount INT  
 DECLARE @PageHead INT  
  
 IF @Backward = 0  
 BEGIN  
  SELECT @RowCount = ((@Page -1) * 20 + 1)  
  SELECT TOP 20 Ranking, ClanName as ClanName, Point, Wins, Losses, CLID FROM ClanHonorRanking(NOLOCK)   
  WHERE Year=@Year AND Month=@Month AND Ranking>0 AND Ranking >= @RowCount ORDER BY Ranking  
 END  
 ELSE  
 BEGIN  
  SELECT @RowCount = ((@Page -1) * 20 + 1)  
   
  SET ROWCOUNT @RowCount  
  SELECT @PageHead = Ranking FROM Clan(NOLOCK) WHERE DeleteFlag=0 ORDER BY Ranking DESC  
   
  SET ROWCOUNT 20  
  SELECT  Ranking, RankIncrease=0, ClanName, Point, Wins, Losses, CLID, EmblemUrl=NULL FROM  
  (  
   SELECT TOP 20 Ranking, ClanName, Point, Wins, Losses, CLID FROM ClanHonorRanking(NOLOCK)   
   WHERE Year=@Year AND Month=@Month AND Ranking>0 AND Ranking <= @PageHead ORDER BY Ranking DESC  
  ) AS t ORDER BY Ranking  
 END  
END  
  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingMaxPage]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanRankingMaxPage]  
AS  
SET NOCOUNT ON
BEGIN  
 DECLARE @MaxPage INT  
 SELECT TOP 1 @MaxPage = Ranking / 20 + 1 FROM Clan(NOLOCK) WHERE DeleteFlag=0 AND Ranking>0 ORDER BY Ranking DESC  
-- SELECT @MaxPage  
 RETURN @MaxPage  
END  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spWebGetClanRankingSearchByRanking]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROC [dbo].[spWebGetClanRankingSearchByRanking]  
 @Ranking INT  
AS  
SET NOCOUNT ON
BEGIN  
 SELECT TOP 20 Ranking, RankIncrease, Name as ClanName, Point, Wins, Losses, CLID, EmblemUrl FROM Clan(NOLOCK)   
 WHERE DeleteFlag=0 AND Ranking>0 AND Ranking=@Ranking ORDER BY Ranking  
END  

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConnLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConnLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[AID] [int] NOT NULL,
	[Time] [smalldatetime] NULL,
	[IPPart1] [tinyint] NOT NULL,
	[IPPart2] [tinyint] NOT NULL,
	[IPPart3] [tinyint] NOT NULL,
	[IPPart4] [tinyint] NOT NULL,
	[CountryCode3] [char](3) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteAccountLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DeleteAccountLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [varchar](24) NOT NULL,
	[AID] [int] NOT NULL,
	[RegDate] [smalldatetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServerLogStorage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ServerLogStorage](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[ServerID] [smallint] NOT NULL,
	[PlayerCount] [int] NOT NULL,
	[GameCount] [int] NOT NULL,
	[BlockCount] [int] NOT NULL,
	[NonBlockCount] [int] NOT NULL,
	[Time] [smalldatetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
END

Code:

USE [GunZDB]
GO
/****** Object:  StoredProcedure [dbo].[spInsertConnLog]    Script Date: 05/20/2009 23:46:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROC [dbo].[spInsertConnLog] 
 @AID int
, @IPPart1 tinyint
, @IPPart2 tinyint
, @IPPart3 tinyint
, @IPPart4 tinyint
, @CountryCode3	char(3)
AS
 SET NOCOUNT ON
 INSERT INTO ConnLog( AID, Time, IPPart1, IPPart2, IPPart3, IPPart4, CountryCode3)
 VALUES (@AID, GETDATE(), @IPPart1, @IPPart2, @IPPart3, @IPPart4, @CountryCode3)

**EvilControl** · 20-06-10

Thank You , Nice Release ^^

[TUT] ConnLog fix *Matchserver

Thread Tools

Search Thread

[TUT] ConnLog fix *Matchserver

Re: [TUT] ConnLog fix *Matchserver

Re: [TUT] ConnLog fix *Matchserver

Re: [TUT] ConnLog fix *Matchserver

Re: [TUT] ConnLog fix *Matchserver

Re: [TUT] ConnLog fix *Matchserver

Advertisement