I'm going to be setting up a DEV server and modifying the code and seeing what I can come up with. I know there is another thread that will mask the tunnel but not fill it. I wonder if removing the rebuild function will work or break the server.
CREATE DEFINER=`root`@`%` PROCEDURE `test_tun_removal`(IN `GeoTerID` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE CurrentVersion INT;
DECLARE CorrectVersion INT;
DECLARE BeginVersion INT;
DECLARE cur1
CURSOR FOR
SELECT Distinct(Version)
FROM geo_patch
WHERE TerID = GeoTerID AND Version >= BeginVersion;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
declare exit handler for sqlexception
begin
rollback;
resignal;
end;
CREATE Temporary TABLE `ark_tuntemp` (
`ID` INT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into ark_tuntemp ( ID ) (
SELECT ID from geo_patch
WHERE Version in ( select Version from geo_patch where Substance = 0 ) and TerID = GeoTerID
);
SET BeginVersion = ( SELECT Version from geo_patch where ID = ( SELECT ID from ark_tuntemp limit 1 ) ) - 1 ;
SET CorrectVersion = BeginVersion;
IF CorrectVersion < 2 THEN
SET CorrectVersion = 2;
END IF;
START TRANSACTION;
DELETE FROM geo_patch where ID in ( Select ID from ark_tuntemp );
open cur1;
version_loop: LOOP
FETCH cur1 into CurrentVersion;
IF done THEN
LEAVE version_loop;
END IF;
UPDATE geo_patch SET Version = CorrectVersion WHERE Version = CurrentVersion;
SET CorrectVersion = CorrectVersion + 1;
END LOOP;
close cur1;
COMMIT;
UPDATE terrain_blocks SET GeoVersion = ( select Version from geo_patch where TerID = GeoTerID order by Version desc limit 1 ) WHERE ID = GeoTerID;
drop temporary table if exists ark_tuntemp;
END
Alakarsiann wrote:Figured out a new version that takes only seconds to do per world terrain tile. =) Much better than the hours the previous one took. Testing some more first.
-Alakar
http://arkhaya.com/
CREATE PROCEDURE `test_tun_removal`(IN `GeoTerID` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
CREATE Temporary TABLE `ark_tuntemp` (
`ID` INT NULL,
INDEX `ID` (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TEMPORARY TABLE `ark_geo_versions` (
`Version` INT(11) NULL DEFAULT NULL,
`NewVersion` INT(11) NULL DEFAULT NULL,
INDEX `Version` (`Version`),
UNIQUE INDEX `NewVersion` (`NewVersion`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into ark_tuntemp ( ID ) (
SELECT ID from geo_patch
WHERE Version in ( select Version from geo_patch where ( Substance = 0 or LevelFlags = 128 )and TerID = GeoTerID ) and TerID = GeoTerID
);
-- DELETE TUNNELS
DELETE FROM geo_patch where ID in ( Select ID from ark_tuntemp );
-- RE-INDEX
INSERT INTO ark_geo_versions ( Version ) ( SELECT Version from geo_patch where TerID = GeoTerID group by Version);
SET @n = 1;
UPDATE ark_geo_versions SET NewVersion = @n:=@n+1;
UPDATE geo_patch g
JOIN ark_geo_versions v on v.Version = g.Version
SET g.Version = v.NewVersion WHERE g.TerID = GeoTerID;
UPDATE terrain_blocks SET GeoVersion = ( select Version from geo_patch where TerID = GeoTerID order by Version desc limit 1 ) WHERE ID = GeoTerID;
drop temporary table if exists ark_tuntemp;
DROP TEMPORARY TABLE IF EXISTS ark_geo_versions;
END
call test_tun_removal(442);
call test_tun_removal(443);
call test_tun_removal(444);
call test_tun_removal(445);
call test_tun_removal(446);
call test_tun_removal(447);
call test_tun_removal(448);
call test_tun_removal(449);
call test_tun_removal(450);
Alakarsiann wrote:
- Code: Select all
CREATE PROCEDURE `test_tun_removal`(IN `GeoTerID` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
CREATE Temporary TABLE `ark_tuntemp` (
`ID` INT NULL,
INDEX `ID` (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TEMPORARY TABLE `ark_geo_versions` (
`Version` INT(11) NULL DEFAULT NULL,
`NewVersion` INT(11) NULL DEFAULT NULL,
INDEX `Version` (`Version`),
UNIQUE INDEX `NewVersion` (`NewVersion`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
insert into ark_tuntemp ( ID ) (
SELECT ID from geo_patch
WHERE Version in ( select Version from geo_patch where ( Substance = 0 or LevelFlags = 128 )and TerID = GeoTerID ) and TerID = GeoTerID
);
-- DELETE TUNNELS
DELETE FROM geo_patch where ID in ( Select ID from ark_tuntemp );
-- RE-INDEX
INSERT INTO ark_geo_versions ( Version ) ( SELECT Version from geo_patch where TerID = GeoTerID group by Version);
SET @n = 1;
UPDATE ark_geo_versions SET NewVersion = @n:=@n+1;
UPDATE geo_patch g
JOIN ark_geo_versions v on v.Version = g.Version
SET g.Version = v.NewVersion WHERE g.TerID = GeoTerID;
UPDATE terrain_blocks SET GeoVersion = ( select Version from geo_patch where TerID = GeoTerID order by Version desc limit 1 ) WHERE ID = GeoTerID;
drop temporary table if exists ark_tuntemp;
DROP TEMPORARY TABLE IF EXISTS ark_geo_versions;
END
This is what I have so far that works really well. It will remove all tunnels but there are some side effects in areas where people have tried to tunnel but not finished building one where some rock and other things might be pushed up a bit at the entrance. I am sure it just means that there are more things to find, but searching for Substance 0 which is Air or LevelFlags set to 128 (Tunnel Walls) seems to get most when you include everything else in that terrain ID with the same version.
To use serverwide just
- Code: Select all
call test_tun_removal(442);
call test_tun_removal(443);
call test_tun_removal(444);
call test_tun_removal(445);
call test_tun_removal(446);
call test_tun_removal(447);
call test_tun_removal(448);
call test_tun_removal(449);
call test_tun_removal(450);
Afterwards remove all references to cached patches from the terrain_blocks table (will write basic function for this later) and start server. First few loads will take clients a little longer to load into as they are not able to use the cached terrain until server has underwent "midhight maintenance."
-Alakar
http://arkhaya.com/
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 WHERE `ID`>0;
Alakarsiann wrote:
- Code: Select all
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 WHERE `ID`>0;
That will reset them all.
-Alakar
DiWorm wrote:Ok, I test it, it works normaly Thank you for this script ^_^
BladeRavinger wrote:Stupid question..... well not really a smart observation
wouldnt we just clear the patch.sql of updates then add a script that finds and removes all tunnels, then every server restart the tunnels will be deleted, you can move the patch.sql so that un foreseen crashes dont ruin the tunnels