Many useful query for Admin

Just the code you want in Query Analyzer

Delete no member Guild ( guild have 1 GM only ) :

Quote:

---

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where EXISTS (
SELECT m.G_Name,count(*) as memb from GuildMember m
where guild.g_name=m.G_name
group by m.G_Name
having count(*) <2
)

---

Delete guildmember with no guild ( when you edited wrong then make bug )

Quote:

---

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guildmember
where NOT EXISTS (
SELECT * from Guild
where guild.g_name=guildmember.G_name
)

---

Top 10 Guild ( as members )

Quote:

---

SELECT top 10 g.g_name,count(all m.g_name)as thanhvien from Guild as g join Guildmember as m
on g.g_name like m.g_name
group by g.g_Name
having count(*) > 20
order by count(*) DESC

---

Top 5 guild ( as resets all members in guild )

Quote:

---

select top 5 g.g_name,sum(c.resets) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc

---

Top 5 online time

Quote:

---

select top 10 c.name,resets,s.onlinehours from character c full join memb_stat s
on c.accountid=s.memb___id
where (c.accountid in ( select top 10 memb___id from memb_stat
order by onlinehours desc ))
and (c.name in ( select top 1 c.name from character c where c.accountid=s.memb___id order by resets desc))
order by onlinehours desc

---

Top average resets (per player in guild ).Edit 10 to value you want . That's value of min member of a guild.

Quote:

---

select top 5 g.g_name,sum(c.resets)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc

---

rename account

Quote:

---

select * from accountcharacter
where id='9242085'

update accountcharacter
set id='ha1710'
where id='9242085'

select * from accountcharacter
where id='ha1710'
-----------------------
select * from character
where accountid='9242085'

update character
set accountid='ha1710'
where accountid='9242085'

select * from character
where accountid='ha1710'
----------------------
select * from memb_info
where memb___id='9242085'

update memb_info
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_info
where memb___id='ha1710'
----------------------
select * from memb_stat
where memb___id='9242085'

update memb_stat
set memb___id='ha1710'
where memb___id='9242085'

select * from memb_stat
where memb___id='ha1710'
-----------------------
select * from warehouse
where accountid='9242085'

update warehouse
set accountid='ha1710'
where accountid='9242085'

select * from warehouse
where accountid='ha1710'

---

delete account with nomore connect from 2007-06-01

Quote:

---

delete from memb_info
where memb___id in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from accountcharacter
where id in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from character
where accountid in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

delete from warehouse
where accountid in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

---

delete account that no connect ( blank account ) or longtime no connect

Quote:

---

delete from memb_info
where memb___id not in (
select memb___id from memb_stat)

delete from accountcharacter
where id not in (
select memb___id from memb_stat)

delete from character
where accountid not in (
select memb___id from memb_stat)

delete from warehouse
where accountid not in (
select memb___id from memb_stat)

---

Character not in account :

Quote:

---

select accountid,character.name from character where accountid in (
select accountid from character
where name not in (select gameid1 from accountcharacter where id=accountid )
and name not in (select gameid2 from accountcharacter where id=accountid )
and name not in (select gameid3 from accountcharacter where id=accountid )
and name not in (select gameid4 from accountcharacter where id=accountid )
and name not in (select gameid5 from accountcharacter where id=accountid )
)

---

delete guild , guildmember that wrong :

Quote:

---

alter table guild nocheck constraint all
alter table guildmember nocheck constraint all
delete from guild
where g_master not in ( select name from character )
delete from guildmember
where name not in ( select name from character )
delete from guildmember
where G_Name not in ( select G_name from guild )

---

back up server :

Quote:

---

--trunkating
BACKUP LOG muonline WITH NO_LOG

USE [Muonline]
GO
--shrinking
DBCC SHRINKDATABASE(N'Muonline')

Declare @dateBackup Varchar(100)
Set @dateBackup = 'c:\muonline_' +
Convert(varchar, datepart( year , Getdate() )) + '-'+
Convert( varchar , datepart( month , Getdate() ) ) + '-'+
Convert( varchar, datepart( day , Getdate() ) ) + '.bak'
--saving backup with name and date.
BACKUP DATABASE [Muonline] TO DISK = @dateBackup WITH NOFORMAT, NOINIT, NAME = N'Muonline-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

---

Sorry my bad English . If you think my guide useful , please take a look here to help my problem . Im sadly with it :

Quote:

---

I own 1 Muonline server . Last 2 week, my server has been flood . I dont know how the hacker attacked my server . This is a part of joinserver when being attacked :
Quote:
11:48:14a [dasxv] (222.253.205.187) User Login
RecvQ Count : 279
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 276
RecvQ Count : 275
11:48:18a [flowea] (222.253.205.187) User Login
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 275
RecvQ Count : 274
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:22a [contnue] (222.254.31.212) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:23a [Pizza] (192.168.1.10) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
RecvQ Count : 269
RecvQ Count : 268
RecvQ Count : 267
RecvQ Count : 266
11:48:24a [henrr1972] (123.22.113.137) User Login
RecvQ Count : 267
RecvQ Count : 266
RecvQ Count : 265
RecvQ Count : 264
[SubserverVIPR1][192.168.1.10:55903] GameServer Connect
RecvQ Count : 263
RecvQ Count : 262
RecvQ Count : 261
11:48:25a [trav103] (118.68.176.71) User Login
RecvQ Count : 263
As you see , when being flooded , join server notice much error " RecvQ Count" . At this time , all players playing get lag and dis and cant come back into game , just click on subserver bar and nothing happen ( i think because joinserver cant process at this time ) . Then i start to find solutions. I see that when turn off apache server , the flood stop immediately. So this's the first i got . Tried to change another web : mutoolz , muweb 0.8 , ... but all fail . still being flooded . Then i upgrade apacheserver to lasted version included lasted php , change webserver port ,mod_security2, mod_dosevasive for apache server, login box with .htaccess, setup security firewall ( comodo firewall pro , norton , kis , outpost firewall pro ,...) , block all port ( just open 44405 , 55901 , 55903 ) , setup ISA server to mornitor tcp-ucp connect , request , use all anti flood function ( limit TCP-UDP connection , http request per second ) but ALL FAIL . They nonstop flood my server from day to day ( except when i turn off my webserver ) . I tried search google about that but no result , i tried ask many people to help but they dont know what's my Muonline Joinserver :( . I know that my problem not really in Muonline Server Help . But i dont know what should i do now , where can i find help now . All my players going to crazy now , and me too .
If you got this problem too or know what i should do please help . Dont ask me to search anymore . I tried " recvq count" , "recvq" , "muonline recvq count" in google search but no results.
Thanks for reading .

---

thanks, very useful

This guide is very usefull...

Thank you

Nice ...

try to make your own web page, simple and nice, but eficient...no install folder etc...directly..with no password protected etc..and user..cas that mean someone can acces it...if dosen't have...normal php like a banal html with a image in the center..noone can fload nothing at it...cas dosen't have acces...only you in the base files...i used to muweb and mutoolz, and got same problem...not flood.but people blast etc..and i maded my own web...clasic..normal and nice..but eficient...try too.

the top 5 online don

Very nice, these scripts are very good !

[quote=didi3d;3283678]the top 5 online don

tnx for the info..,..

Nice, script`s are good !

Thanks!

I need modificy this query:

Top 5 guild ( as resets all members in guild )

Quote:
select top 5 g.g_name,sum(c.resets) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
order by reset desc



I need this query modificy. I need create medium level. This look that: select all guildmembers(resets) and delete on player count.... 180 resets / delete on 30 players = medium level 6



Someone can create that query? :)

could your explain more please . i dont really understand your mean,

I am necessary to calculate the middle level of guild. That common amount of players which in a guild divided by the amount of resets.
For example: 150 resets / we divide by 30 players = we get the middle level of guild = 50 resets

Wait a little . im busy now . i will give you later

thanks, i w8 your :)

Here you got . Test it and reply results ( i didnt test )

select top 5 g.g_name,sum(c.resets)/count(all g.g_name) as reset from guildmember g join character c
on g.name like c.name
group by g.g_name
having count(all g.g_name)>10
order by reset desc

Edit 10 to value you want . That's value of min member of a guild.

Somebody may to make me a reset scrip.To make res from 300-350 lv. and give point 300lv=301 points, 302lv=302 point.......350lv=350 point

and no AutoReset if somebody want :juggle::poster_ss I stop here

Quote:

---

if(character level>300 AND character level<350) {
update [level up points] = 'character level',cLeve='1',Exp='0'
....
}

---

:sq_yellow

Help :busted_co

Quote:

---

Originally Posted by vailoelle

Somebody may to make me a reset scrip.To make res from 300-350 lv. and give point 300lv=301 points, 302lv=302 point.......350lv=350 point

and no AutoReset if somebody want :juggle::poster_ss I stop here

:sq_yellow

Help :busted_co

---

Sorry but i cant understand what you want. You want a query to run in Query Analyzer or a query to add in your autoreset script ?

Quote:

---

Originally Posted by levinthan9

Sorry but i cant understand what you want. You want a query to run in Query Analyzer or a query to add in your autoreset script ?

---

I want heroes in my server to may a make res in lv 300-350 and give point 300-350 ( but res to make in web NOT AutoRes).Whow to make this :sq_yellow

Sorry again for my English.

What web you are using ?

Quote:

---

Originally Posted by levinthan9

What web you are using ?

---

MuWeb 0.8.... if need I change web.

edit character.class.php . function reset($charactername)


this's 1 example that im using:

PHP Code:

---

function reset($charactername)
{
          if ((isset($_SESSION['pass'])) && (isset($_SESSION['user']))); 
                     {
                           require("config.php");
                           $loginid = "$_SESSION[user]";


                           $online_check = $db->Execute("SELECT ConnectStat FROM MEMB_STAT WHERE memb___id=?",array($loginid));
                           $row2 = $online_check->fetchrow();

                           $result = $db->Execute("Select Clevel,Resets,Money,LevelUpPoint,class From Character where Name=?",array($charactername));
                           $row = $result->fetchrow();


                            $resetup=$row[1] + (1);
                            
                            if (($row[1]>=0) and ($row[1]<20))
                            {$resetlvlmustbe=100;
                             $resetmoeny=$row[2]-2000000;
                             $resetpt=200 * $resetup;                                                 
                            }
                            elseif (($row[1]>=20) and ($row[1]<40))
                            {$resetlvlmustbe=200;
                             $resetmoeny=$row[2]-5000000;
                             $resetpt=200 * $resetup;    
                                                         
                            }

$moneyreset=$row[2]-$resetmoeny;

                            if ($row2[0] != 0){ $error=1;
                                         show_error("Logout of game man"); 
                                                  }
                            elseif ($resetmoeny < 0){ $error=1;
                                         show_error("oh man . you must have  $moneyreset Zen to Reset!"); 
                                                    }

                            elseif ($row[0] < $resetlvlmustbe){ $error=1;
                                         show_error("your level must be  $resetlvlmustbe to reset! "); 
                                                                   }
                            if($error!=1){

                                         $sql_reset_script="Update character set [strength]='25',[dexterity]='25',[vitality]='25',[energy]='25',[leadership]='25',[MapNumber]='0',[MapPosX]='123',[MapPosY]='123',[clevel]='1',[experience]='0',[money]='$resetmoeny',[LevelUpPoint]='$resetpt',[resets]='$resetup' where name=?";
                                         $sql_reset_script2="UPDATE character Set [inventory]=CONVERT(varbinary(1080), null),[magiclist]= CONVERT(varbinary(180), null) Where name=?";


                                    $sql_reset_exec = $db->Execute($sql_reset_script,array($charactername));
                                    $sql_reset_exec2 = $db->Execute($sql_reset_script2,array($charactername)); 


---

Quote:

---

Originally Posted by levinthan9

edit character.class.php . function reset($charactername)


this's 1 example that im using:

---

Hi thanks for help , but don't work.Res dont work 1 pic.
2 pic only heroes "Lord" may to make res he is 350 lv another "Low level to reset".
I replace function reset($charactername) with you code (3 pic)

http://img233.imageshack.us/img233/4...8145kj0.th.jpg http://img233.imageshack.us/img233/7...2997la6.th.jpghttp://img143.imageshack.us/img143/7...2903yp5.th.jpg http://forum.ragezone.com/%5Burl=htt...g%5D%5B/url%5D

Wrong . You muse add my code in function reset($charactername) not function add_stats.
That's main code.

Inorder to make it work, you must edit your characters_manager.php file in inc folder.

Disable this things

//$resetmoeny=$info_result[4]-($muweb['resetmoney']);
//if($resetmoeny < 0){$reset_="$reset_no_zen";}
//elseif($info_result[2] < $muweb['resetlevel']){$reset_="$reset_no_level";}
//elseif($info_result[2] >= $muweb['resetlevel'])

Only let this :

$reset_="$reset_allow";

[QUOTE=didi3d;3283678]the top 5 online don

If you have a version with extra warehouses use this:

delete from ExtWarehouse
where memb___id in (
select memb___id from memb_stat where disconnecttm<'2007-06-01')

Quote:

---

Originally Posted by levinthan9

Wrong . You muse add my code in function reset($charactername) not function add_stats.
That's main code.

---

Yes I make this but I wrong at pic.

Quote:

---

Originally Posted by levinthan9

Inorder to make it work, you must edit your characters_manager.php file in inc folder.

Disable this things

//$resetmoeny=$info_result[4]-($muweb['resetmoney']);
//if($resetmoeny < 0){$reset_="$reset_no_zen";}
//elseif($info_result[2] < $muweb['resetlevel']){$reset_="$reset_no_level";}
//elseif($info_result[2] >= $muweb['resetlevel'])

Only let this :

$reset_="$reset_allow";

---

This too make
.......now give me this error http://img88.imageshack.us/img88/778...5865rs3.th.jpg

this line is the last and have onlly

PHP Code:

---

?> 


---

Just to say my server is 1.00.16 beta 41 if this have mether

bump

good thread, helped me :).

levinthan9


thank a lot

thanks for bumping this! Well done

Bump after 2 years

select memb___id from memb_stat where disconnecttm<'2007-06-01')

it the Date format above is Year-Month-Date?

could you add to it a script that allow admins to use M for teleport? 1.02c

many thanks!

Code:

---

Quote:
I own 1 Muonline server . Last 2 week, my server has been flood . I dont know how the hacker attacked my server . This is a part of joinserver when being attacked :
Quote:
11:48:14a [dasxv] (222.253.205.187) User Login
RecvQ Count : 279
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 276
RecvQ Count : 275
11:48:18a [flowea] (222.253.205.187) User Login
RecvQ Count : 278
RecvQ Count : 277
RecvQ Count : 276
RecvQ Count : 275
RecvQ Count : 274
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:22a [contnue] (222.254.31.212) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
11:48:23a [Pizza] (192.168.1.10) User Login
RecvQ Count : 273
RecvQ Count : 272
RecvQ Count : 271
RecvQ Count : 270
RecvQ Count : 269
RecvQ Count : 268
RecvQ Count : 267
RecvQ Count : 266
11:48:24a [henrr1972] (123.22.113.137) User Login
RecvQ Count : 267
RecvQ Count : 266
RecvQ Count : 265
RecvQ Count : 264
[SubserverVIPR1][192.168.1.10:55903] GameServer Connect
RecvQ Count : 263
RecvQ Count : 262
RecvQ Count : 261
11:48:25a [trav103] (118.68.176.71) User Login
RecvQ Count : 263
As you see , when being flooded , join server notice much error " RecvQ Count" . At this time , all players playing get lag and dis and cant come back into game , just click on subserver bar and nothing happen ( i think because joinserver cant process at this time ) . Then i start to find solutions. I see that when turn off apache server , the flood stop immediately. So this's the first i got . Tried to change another web : mutoolz , muweb 0.8 , ... but all fail . still being flooded . Then i upgrade apacheserver to lasted version included lasted php , change webserver port ,mod_security2, mod_dosevasive for apache server, login box with .htaccess, setup security firewall ( comodo firewall pro , norton , kis , outpost firewall pro ,...) , block all port ( just open 44405 , 55901 , 55903 ) , setup ISA server to mornitor tcp-ucp connect , request , use all anti flood function ( limit TCP-UDP connection , http request per second ) but ALL FAIL . They nonstop flood my server from day to day ( except when i turn off my webserver ) . I tried search google about that but no result , i tried ask many people to help but they dont know what's my Muonline Joinserver :( . I know that my problem not really in Muonline Server Help . But i dont know what should i do now , where can i find help now . All my players going to crazy now , and me too .
If you got this problem too or know what i should do please help . Dont ask me to search anymore . I tried " recvq count" , "recvq" , "muonline recvq count" in google search but no results.
Thanks for reading .

---

I have same problem like you, my server have been flooded by some idiot , and i can't even run server.

i don't know what to do now, only one way use different IP on webserver and remote to DB server.

how to imply this into the main page?. any tutorials?