Life is Feudal Official Forum

[Gchristopher]

There are a few server database tables that require maintenance for a server to stay running smoothly. Of these, geo_patch appears to be the most problematic.

The most obvious problem is that geo_patch records every terrain-related action (digging, mining, raise/lowering, tunnel decay and collapse), forever. So the table increases in size forever. In the long run, that is guaranteed to eventually degrade server performance.

There are some other reasons a server administrator might want to modify geo_patch:

- To rollback griefing more selectively than restoring a backup.
- To slow or accelerate tunnel decay.
- To restore terrain for new players.
- To restore limited resources, especially sand, clay, and particularly high-quality patches of raw materials, which are easily depleted and do not regenerate.

This thread is an attempt to start presenting, collecting and revising resources for evaluating, modifying, and repairing the geo_patch table.

[Gchristopher]

This post is reserved for summary information and collected links and resources:

Here are threads with valuable prior work and discussion:
Possible fix for tunnels *theory* (Database editing)
[MOD][WIP] Tunnel Remover v0.1.0
Tunnel Removal

TODO: Important notes:
- Decoding GeoDataID into TerID, X, Y
- Altitude
- Substance types
- Level Flag research
- Grouped changes (tunneling locking adjacent tiles, raising/lowering material falls downhill)

[Gchristopher]

Major Challenge: Renumbering the geo_patch Table

One approach to removing a group of changes to terrain is to delete them from the geo_patch table. Assuming deletion is done correctly, the geo_patch table will now have gaps in its ID and Version sequences that would never be present in the normal operation in the game.

Here is Version 0.1 of database procedures to renumber the ID and Version columns of geo_patch.

These are currently being tested. Use at your own risk and backup first!

Preliminary: Checking for Gaps in Sequences

There are two sequences maintained in geo_patch:
- the ID field, which is a unique number for every row in the table
- Version, which is a separate sequence for each TerID and may repeat with multiple ChangeIndex values for a single Version.

Query geo_data for gaps in ID:

Code: Select all

SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
    FROM geo_patch AS a, geo_patch AS b
    WHERE a.id < b.id
    GROUP BY a.id
    HAVING start < MIN(b.id);

Query geo_data for gaps in Version for a particular TerID. (446 in this example.)

Code: Select all

SELECT a.Version+1 AS start, MIN(b.Version) - 1 AS end
  FROM geo_patch AS a, geo_patch AS b
 WHERE a.Version < b.Version
   AND a.TerID = 446
   AND b.TerID = 446
GROUP BY a.Version
HAVING start < MIN(b.Version);

Procedures to Collapse Gaps in geo_data

Version:

This procedure runs very slowly unless indices are added to help the database deal with indexing by TerID and Version. Adding them seems harmless, but dropping them afterward might be a good idea.

Code: Select all

DROP INDEX geo_patch_Version ON geo_patch;
DROP INDEX geo_patch_TerID_Version ON geo_patch;
CREATE INDEX geo_patch_Version ON geo_patch (Version);
CREATE INDEX geo_patch_TerID_Version ON geo_patch (TerID, Version);

Procedure to collapse gaps in Version for a given TerID:

Code: Select all

-- p_renumberGeoPatchVersion
-- Procedure to collapse gaps in Version that may exist in the geo_patch
-- table following deletions.
DROP PROCEDURE IF EXISTS p_renumberGeoPatchVersion;
DELIMITER $$
CREATE PROCEDURE p_renumberGeoPatchVersion(in_TerID INT UNSIGNED)
BEGIN
  DECLARE ii            INT UNSIGNED;
  DECLARE maxVersion    INT UNSIGNED;
  DECLARE currCount     INT UNSIGNED;
  DECLARE next          INT UNSIGNED;

  SELECT 'Renumbering Version';
 
  -- Version can start greater than 0. Trust that the lowest Version for this TerID is correct.
  SET ii = (SELECT MIN(Version) FROM geo_patch WHERE TerID = in_TerID);

  SET maxVersion = (SELECT ii + COUNT(DISTINCT(Version)) - 1 FROM geo_patch WHERE TerID = in_TerID);
 
  WHILE ii <= maxVersion DO
    SET currCount = (SELECT COUNT(*) FROM geo_patch WHERE TerID = in_TerID AND Version = ii);
   
   
    IF currCount = 0 THEN
      SET next = (SELECT MIN(VERSION) FROM geo_patch WHERE TerID = in_TerID AND Version > ii);
     
      -- SELECT ii, currCount, next
     
      UPDATE geo_patch
         SET Version = ii
       WHERE Version = next;
       
      -- SET maxVersion = MaxVersion - 1
    END IF;
   
    SET ii = ii + 1;
   
    -- Loops are slow in databases. Print out progress to reduce anxiety over a long-running procedure.
    IF ii % 100 = 0 THEN
      SELECT ii, currCount;
    END IF;
  END WHILE;
 
  SELECT maxVersion;
 
  UPDATE terrain_blocks SET GeoVersion = ( SELECT MAX(Version) FROM geo_patch WHERE TerID = in_TerID) WHERE ID = in_TerID;

END$$
DELIMITER ;

Procedure to collapse gaps in ID:

Code: Select all

-- p_renumberGeoPatchID
-- Procedure to collapse gaps in ID that may exist in the geo_patch
-- table following deletions.
DROP PROCEDURE IF EXISTS p_renumberGeoPatchID;
DELIMITER $$
CREATE PROCEDURE p_renumberGeoPatchID()
BEGIN
  DECLARE ii            INT UNSIGNED;
  DECLARE maxID         INT UNSIGNED;
  DECLARE currCount     INT UNSIGNED;
  DECLARE next          INT UNSIGNED;


  SELECT 'Renumbering ID';
 
  -- ID can start greater than 0. Trust that the lowest ID.
  SET ii = (SELECT MIN(ID) FROM geo_patch);

  SET maxID = (SELECT ii + COUNT(*) - 1 FROM geo_patch);
 
  WHILE ii <= maxID DO
    SET currCount = (SELECT COUNT(*) FROM geo_patch WHERE ID = ii);
   
   
    IF currCount = 0 THEN
      SET next = (SELECT MIN(ID) FROM geo_patch WHERE ID > ii);
     
      -- SELECT ii, currCount, next
     
      UPDATE geo_patch
         SET ID = ii
       WHERE ID = next;
       
      -- SET maxVersion = MaxVersion - 1
    END IF;
   
    SET ii = ii + 1;
   
    -- Loops are slow in databases. Print out progress to reduce anxiety over a long-running procedure.
    IF ii % 100 = 0 THEN
      SELECT ii, currCount;
    END IF;
  END WHILE;
 
  SELECT maxID;
 
END$$
DELIMITER ;

Running the Procedures

Running the procedures for the TerID being edited is pretty simple. (Example for 446)

Code: Select all

START TRANSACTION;
-- do something
CALL p_renumberGeoPatchVersion(446);
CALL p_renumberGeoPatchID();

COMMIT; -- Or ROLLBACK, if something went wrong



Uh-oh! It still needs caching cleanup!

Clients keep a cache of the Version of each TerID. If you try to connect to the modified server, it will kick you because your client (correctly) thinks it has a newer version for the terrain.

This APPEARS to be mediated/maintained by a CRC value in the terrain_blocks table, that might be "fixed" by deleting it.

Basically, go back and delete all the CRC values for the TerID you just modified. (ID column on terrain_blocks).

e.g.

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 = 446;

I'm not at all convinced this is the absolute best solution, but preliminary testing seems to indicate it works. The server complained a lot when brought back up, but permitted the client to connect, and generated new CRC values on the next change to the terrain.

That definitely appears functional, but errors in the server log are worrisome, so it would be nice to find an alternate approach that doesn't generate any error messages at all.

[Gchristopher]

Major Challenge: Interpreting geo_patch changes, especially changes with multiple ChangeIndex values, interpreting LevelFlags bits, tunnel decay and voxel regeneration.

This post is reserved to collect notes on this topic. There's several good posts to collect here, but a complete explanation appears a ways off.

[Gchristopher]

A holy grail of geo_patch editing is the ability to collapse the (potentially hundreds) of changes to a particular GeoDataID (terrain X/Y) into a minimal number of changes that capture the current state of that terrain.

For example, if someone drops one layer of stone, then drops dirt on top of the stone, the tile has two changes.

If this person is a farmer, they might plow, farm, harvest, fertilize, pick up and drop that dirt several dozen times, each time creating a new change record, so that tile might accumulate several hundred records in geo_patch.

However, the only information that matters to future gameplay at the end of all this farming activity is that there's a layer of rock and a layer of dirt on top of that. All prior rows COULD be discarded, if there was a method for determining which changes were important and which can be safely discarded to history. Or possibly a routine for replacing the history entirely with a minimal set of changes that create the exact same patch to the terrain.

Collected work on this problem doesn't exist yet, but this post is reserved to collect information for that goal.

[Genhis]

Hello,

I would be able to renumber the geo_patch table if I had an original altitude of the tiles. From the old geo_patch table I was able to determine the correct altitude for all but those tiles where tunnel had been.

I was wondering if I could read the altitude from *.ter files that are in a game directory. Here's my original topic: how-can-i-find-out-default-altitude-for-a-tile-t17334/

Genhis.

[Gchristopher]

I bet it's possible. They don't appear to be compressed.

If I had to take a wild guess, a .ter file starts with a header value. (maybe version?) and then a 3d array of 32-bit values. (What's the X/Y dimensions of a Terrain block? I bet it's 4096. Hmm, there might be more than one value packed into that 32-bit number. Maybe quality? Maybe material? Maybe altitude for partially filled voxels?

If the Z-height for the game is 1023 or 1024, then the first part of the file would be the right amount of data for a simple X/Y/Z array with 1 32-bit value per voxel.

Later in the file are what looks like a bunch of 1 byte values.

There's a string table at the end of the file, containing material names. Maybe those 1 byte values are indices of material types of the terrain?

Oh, the first 24 bytes are the TerID, the same as the file name. (Why 24 bytes? Maybe it's bit-packed into the high bytes of a 32-bit value?)

There's my starting guess! Next up would be looking at known X/Y/Altitude patterns for a given TerID in-game and trying to find a matching pattern in the .ter file? Figuring out what altitude corresponds to a full voxel would help.

I have no idea on the .terdatcl and .ter2idxcl files.

[Gchristopher]

As of v 1.1.1.15, setting the CRC values to NULL now causes the game to crash.

Do any devs read these forums? Can you provide guidance on how to update the CRC values after removing rows from geo_patch and renumbering?

How about clearing the cache on the client side? Is that possible?

[Gchristopher]

Argh. There are files in the client data\terrains\cached\ directory that looked promising, but deleting them does not prevent the "client kicked, has newer version of terrain" event.

How do servers deal with the ever-growing geo_patch table? Pointless history records of tunnel decay are taking up 25% of the table.

[Gchristopher]

Had a success! After deleting rows from geo_patch, renumbering the table, and updating GeoVersion in terrain_blocks, the remaining challenge was to get the clients to download the new terrain instead of getting kicked with the "client have newer version of geo" error.

For this test, we set the CachedClientGeoDatCRC to NULL in the terrain_blocks record for that terrain ID:

Code: Select all

UPDATE terrain_blocks SET CachedClientGeoDatCRC=NULL WHERE ID = 449;

If this works for other tests, it looks like we're back to being capable of working to fight geo_patch bloat. At this point, 37% of the table is pointless records of tunnels degrading and growing every day.

[Halvdal]

Is there any news aubout that? I hope so...

[Eso]

There is a Tool at https://www.absoftware.it/LIFManager wich can delete the selected area and fix the table.
Its have also other utility.