Tunnel Removal

Place for sharing your game modifications for Life is Feudal: Your Own

NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Tunnel Removal

Post by NavyS34l » 01 Jul 2015, 04:38

So, I was watching the log on boot today and saw this.. Looking at it, could tunnels not be removed as trees can be using a similar code as in this thread, 2nd post from machine: http://lifeisfeudal.com/forum/delete-poor-quality-trees-monthly-auto-uproot-t8393/

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.

Spoiler
Image


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 01 Jul 2015, 05:25

No they can't unfortunately. I've tried to accomplish this but I end up with a sequence error when booting the server even though all changes in the geo_patch get re sequenced so there aren't any gaps. I think the solution to this though would be finding a correct value to put in the CRC column in the terrain_blocks table. Though how that is calculated, I don't know.

-Alakar
http://arkhaya.com/


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 04 Jul 2015, 19:08

There has to be a default value or wildcard that will look to another table for the value. I have been digging a little but was pulled off on another project so it'll have to wait just a bit longer.
Image


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 05 Jul 2015, 04:46

I lied apparently. I believe I have found a way to remove tunnels, it CAN BE painfully slow though to run. So it would have to be done only during extended maintenance periods. (My first test without any optimizations took 90 minutes only on one Terrain Tile, with Optimizations but hardly any geo changes after the tunnelling it reduced the time to about 18 seconds. So with a lot of changes to the terrain it will still potentially take a long time to complete). I will be working on figuring this out a bit more and testing in the meantime. So hopefully that means Arkhaya can finally allow mining and ban infinite piles =).

-Alakar
http://arkhaya.com/


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 09 Jul 2015, 04:18

Woah seriously? Thats awesome! Would you be willing to share out the query? Id like to test that out.

And funny enough, I was looking to reach to you specifically about a couple things that you have done over there and get my compass in the right direction on a few things. Mind if I PM that over or maybe steam chat?
Image


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 15 Jul 2015, 03:50

Has anyone else been able to figure this out as well? I'm at a dead-end atm.
Image


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 24 Jul 2015, 20:26

Well I made some headway with this issue. One thing I seem to be having an issue with that I hope you guys can clarify..

If I remove a row from the geo_patch table in the database that then puts the puts the IDs off, which I assume need to be re-ordered.

However, which value do I use to subtract from the geoversion column under terrain_blocks? I assume it is one of the values listed in the row that was removed from the table above.

The only other question is does the CRC under terrain_blocks need to be adjusted as well?

Once I have that and it works I think I can whip up a quick query to remove the values and reorder as needed.

Any help is appreciated.
Image


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 25 Jul 2015, 19:44

Well i figured it out today.
Image


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 25 Jul 2015, 20:51

Code: Select all
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


Here is what I came up with so far, its very slow to run though for the terrain tiles. More I think needs to be done with getting the ceiling and some other issues. But this will at least remove all tunnels on the world tile that it is called on and reorganize that Terrain tile.

-Alakar
http://arkhaya.com/


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 27 Jul 2015, 12:29

Hey, I tried this out and its acting a little funny for me, maybe its just heidiSQL, it makes the table but wont display it and the procedure wont pull any data relating to the tile.. Ill figure it out..

To your point of slowness, did you try an indexing at all?
Image


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 27 Jul 2015, 22:14

It does not return any results or show you any results, it runs until it reorganizes all the changes. Which can be several hours per tile depending on how many changes have occurred.

-Alakar


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 28 Jul 2015, 02:47

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/


NavyS34l
 
Posts: 41
Joined: 15 Jun 2015, 16:12

Re: Tunnel Removal

Post by NavyS34l » 28 Jul 2015, 19:11

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/



that's awesome news, I did see you last post, just haven't had a chance to sit down and work on it some more. I'm curious to hear your approach on it. Hope to have some time tonight.
Image


DiWorm
 
Posts: 90
Joined: 22 Sep 2014, 04:58

Re: Tunnel Removal

Post by DiWorm » 01 Aug 2015, 18:30

any news about tunnel remover?


Revv
 
Posts: 76
Joined: 22 Sep 2014, 12:53

Re: Tunnel Removal

Post by Revv » 02 Aug 2015, 11:29

I would be very interested in this also!
Any more news on it?


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 03 Aug 2015, 21:33

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/


DiWorm
 
Posts: 90
Joined: 22 Sep 2014, 04:58

Re: Tunnel Removal

Post by DiWorm » 04 Aug 2015, 11:34

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/

Thanks a lot!

But can you give some example for remove all references to cached patches from the terrain_blocks table
dont understand whats need to do here

Set to NULL for TerCachedCRC?


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 04 Aug 2015, 15:03

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
 
Posts: 90
Joined: 22 Sep 2014, 04:58

Re: Tunnel Removal

Post by DiWorm » 04 Aug 2015, 16:30

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

Ok, I test it, it works normaly :) Thank you for this script ^_^


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 04 Aug 2015, 16:50

DiWorm wrote:Ok, I test it, it works normaly :) Thank you for this script ^_^


Thank you, I am still working on some more things on it to find other mining actions so we don't get the rock issue I described earlier.

-Alakar

User avatar
Ghost_swe79
True Believer
 
Posts: 41
Joined: 16 Apr 2015, 12:17
Location: Sweden

Re: Tunnel Removal

Post by Ghost_swe79 » 04 Sep 2015, 08:37

Have I understod this right to delete the tunnels, just go with one at the time as an quary all 3 of them and then restart the server?

Best regards


Muffinman88
 
Posts: 4
Joined: 18 Jan 2015, 03:29

Re: Tunnel Removal

Post by Muffinman88 » 16 Sep 2015, 14:40

hi guys ive been reading this post and dreaming of this for are lif server only prob is ive never done anything like this before and was wonderin if anyone could shed some light onto how i learn to run scripts


BladeRavinger
 
Posts: 8
Joined: 05 Jun 2015, 21:03

Re: Tunnel Removal

Post by BladeRavinger » 13 Oct 2015, 09:01

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


Alakar
Beta Tester
 
Posts: 202
Joined: 23 Jan 2015, 07:39

Re: Tunnel Removal

Post by Alakar » 13 Oct 2015, 22:04

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


I would advise against it because maintenance scripts that take a longer amount of time sometimes prevent the server from booting.

-Alakar


Dyfan
 
Posts: 2
Joined: 25 Sep 2015, 11:53

Re: Tunnel Removal

Post by Dyfan » 13 Oct 2015, 22:26

Hey! Thanks for redirecting me to this post! I am novice in scripting but is this script still working? Heard from someone who is more into scripting that the tunnel script was out-dated from the last patch and would not work? I really need to add this to my server, http://life-is-feudal.org/server/5228/ , because it's getting out of hand with loading times and lag.


Azeron
Alpha Tester
 
Posts: 24
Joined: 08 Oct 2014, 18:27
Location: Chicago, IL

Re: Tunnel Removal

Post by Azeron » 15 Oct 2015, 23:45

When running the SQL against MariaDB version5.5.39:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ” at line 15

What is the version that your running your scripts on ?


Flocky
 
Posts: 9
Joined: 06 Oct 2014, 19:16

Re: Tunnel Removal

Post by Flocky » 24 Nov 2015, 23:07

I would like to bring this discussion up again. Has anyone found a way to delete reinforced tunnels?


Cogollops
 
Posts: 40
Joined: 04 Dec 2015, 14:56
Location: Spain

Re: Tunnel Removal

Post by Cogollops » 23 Feb 2016, 00:35

Then the full code to use it is?
Image

Image


SomeoneD
Zealous Believer
 
Posts: 14
Joined: 10 Jun 2016, 22:49

Re: Tunnel Removal

Post by SomeoneD » 27 Oct 2016, 12:58

Has anyone found a way to delete reinforced tunnels? *HELP*


Eso
 
Posts: 2
Joined: 22 Feb 2019, 09:55

Re: Tunnel Removal

Post by Eso » 09 Mar 2019, 10:17

With this tool https://www.absoftware.it/LIFManager.
You can select an area on the map and delete things at your choiche:
Tunnels,
Geo Patches,
Trees,
Movable Objects,
Unmovable Objects.
After that you restart the server and the job its done :Yahoo!: .

Its have also others utility you can read in the software website.
Some of those is:
-Change the substance,altitude and quality of the surface material.
-Manage players inventory and more.

Return to Game mods