Life is Feudal Official Forum

[Halvdal]

Hi everyone,

is it possible to "clean" these two database tables?

[Piter_Ragnarson]

Hi. skill_raise_logs you can simply truncate.
For Geo_patch there is some procedures that clean this table. For example my script delete all server only instances and recalculate versions and change indexes.

USE ON YOUR RISK AND AFTER DUMP)))
RUN CODE IN HEIDISQL. THEN ON STOPPED SERVER RUN PROCEDURE p_geoOptimize();
Then start server.
DO not add this procedure to patch.sql becouse it can work too long and corrupt your base.

Code: Select all

-- Добавление ключей для оптимизации леса, объектов и гео
ALTER TABLE `geo_patch`
   ADD INDEX `GeoDataID` (`GeoDataID`) USING BTREE;

-- Дамп структуры для процедура db_name.p_geoOptimize (оптимизаиця геопатча)
DROP PROCEDURE IF EXISTS `p_geoOptimize`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_geoOptimize`()
BEGIN
declare goTerrId int unsigned default 442;

delete geo_patch from geo_patch where IsServerOnly = 1;

while goTerrId < 451 do
set @p = -1;
set @v = 1;
update geo_patch
join (select
 Id,
 Version,
 @v := if(@p=Version, @v, @v:=@v+1) as NewVersion,
 @p := Version
from
 geo_patch where geo_patch.TerID = goTerrId
order by Version, ID) tmp ON tmp.Id = geo_patch.Id
SET geo_patch.Version = tmp.NewVersion;

SET @ver = (SELECT Max(Version) as max FROM geo_patch WHERE TerID = goTerrId ORDER BY TerID);

UPDATE terrain_blocks
SET GeoVersion = @ver
WHERE ID = goTerrId;
set @p := 0;
set @v := 1;
set @ver := 2;
update geo_patch SET ChangeIndex = 0 where version = 2 and terid = goTerrId;
update geo_patch
join (select
 Id,
 Version,
 ChangeIndex,
 @v := if(@p=ChangeIndex, @v, @v:=@v+1),
 @p := if(@ver = Version, @v , @p := 1  and @v := 1) as nw,
 @ver := Version
from
 geo_patch where terid = goTerrId
order by Version, ID) tmp ON tmp.Id = geo_patch.Id
SET geo_patch.ChangeIndex = tmp.nw;

SET goTerrId = goTerrId+1;

end while;

UPDATE terrain_blocks SET CachedGeoVersion=NULL, CachedTerCRC=NULL, CachedServerGeoIdxCRC=NULL, CachedServerGeoDatCRC=NULL, CachedClientGeoIdxCRC=NULL, CachedClientGeoDatCRC=NULL, PackedTerCRC=NULL, PackedClientGeoIdxCRC=NULL, PackedClientGeoDatCRC=NULL, CachedClientGeoIdxSize=NULL, CachedClientGeoDatSize=NULL;

END//
DELIMITER ;


[Halvdal]

Ok thanks!

I used this on my Testserver first. The geo_patch ha 637.917 lines of code. After the procedure it has 544.986.

And after the procedure the table has 52,6 MiB instead of 41,2.

It seems that this doen't work as it should^^