Life is Feudal Official Forum

[Toccs]

Afternoon all,

I was having a look in the database files for our server and noticed a version number tied to the geoid of a square in geo_patch.

Do you know if changing the version of that square would effectively roll back any changes made to it?

[Uno]

I think it's more for back up purpouse. You would need to roll it back with a proper backup, just changing a revision number doesn't change data. AFAIK.

[Toccs]

I think it's more for back up purpouse. You would need to roll it back with a proper backup, just changing a revision number doesn't change data. AFAIK.

What if the revision number is tied to recent changes made to the square and kept in the currnet database instead of being overwritten?

I think its possible that the data could be kept in the database i'll have to create a test enviroment to give it a go though but if i find anything i'll be sure to document it here.

EDIT: To be clear i'm more thinking along the lines that if i set the version number to 0 then it would basicly return that square to the default.

[Uno]

So in your opinion every change in the map is stored in the database? Have you looked into the size of a fresh db and one that is 2-3 weeks old?

[Maegfaer]

I can confirm it's possible. I successfully removed tunnels from the database manually on a test server the week before.

It's a matter of finding the correct versions by comparing the geo ID with the position of your character when you stand in the tunnel. You then need to look at the Level Flags, IIRC the final tunnel action that actually digs a hole in the terrain starts with Level Flags number 22. You then need to delete all entries of that version, this will remove the tunnel, but one more tunnel action ingame will open up the terrain again. To prevent that, remove all the previous versions with the same geo ID(s) as well.

After that you'll have a big gap in the Version sequence of that table, that needs to be fixed, so you need to move down all Versions above the ones that you deleted by the number of deleted versions. It's probably best to write an easily adaptable SQL Query for that.

Final step is to update the terrain_blocks table, the Geo Version field for the right Terrain ID. In the geo_patch table note the Ter ID number of the tunnel that you deleted, and decrease the Geo Version field in the terrain_blocks table of the corresponding ID by the number of Versions you deleted.

Of course this is all experimental, definitely try this on a test server first, and even on live servers, make backups first. I did not completely finish testing this, so I may have missed details that might be different sometimes, definitely try to reproduce this in different situations and for longer tunnels first. I've so many things on my to-mod list now I didn't finish testing this one.

[Everburn3]

I can confirm it's possible. I successfully removed tunnels from the database manually on a test server the week before.

It's a matter of finding the correct versions by comparing the geo ID with the position of your character when you stand in the tunnel. You then need to look at the Level Flags, IIRC the final tunnel action that actually digs a hole in the terrain starts with Level Flags number 22. You then need to delete all entries of that version, this will remove the tunnel, but one more tunnel action ingame will open up the terrain again. To prevent that, remove all the previous versions with the same geo ID(s) as well.

After that you'll have a big gap in the Version sequence of that table, that needs to be fixed, so you need to move down all Versions above the ones that you deleted by the number of deleted versions. It's probably best to write an easily adaptable SQL Query for that.

Final step is to update the terrain_blocks table, the Geo Version field for the right Terrain ID. In the geo_patch table note the Ter ID number of the tunnel that you deleted, and decrease the Geo Version field in the terrain_blocks table of the corresponding ID by the number of Versions you deleted.

Of course this is all experimental, definitely try this on a test server first, and even on live servers, make backups first. I did not completely finish testing this, so I may have missed details that might be different sometimes, definitely try to reproduce this in different situations and for longer tunnels first. I've so many things on my to-mod list now I didn't finish testing this one.

You are like a god to me

[Aggem8]

Can you release the script?

[Maegfaer]

Haven't made one. Might do it sometime next week, not sure. There are tons of things I want to do, so little time...

[Aggem8]

First part of the script

DELETE t.* FROM geo_patch t JOIN geo_patch t1 ON(t.GeoDataID = t1.GeoDataID) WHERE t1.LevelFlags = '22';

After that you'll have a big gap in the Version sequence of that table, that needs to be fixed, so you need to move down all Versions above the ones that you deleted by the number of deleted versions. It's probably best to write an easily adaptable SQL Query for that.

Having some problems understand what you mean here/making a script for this.

[Adamld85]

This would be extremely useful to server admins. There are a lot of griefers in this game logging on to servers and just terraforming peoples bases and doing their best to ruin the map.

Any chance anyone knows how to complete the query or would it also require scripting?

[Aggem8]

It must be this query

DELETE t.* FROM geo_patch t JOIN geo_patch t1 ON(t.Version = t1.Version) WHERE t1.LevelFlags = '22';

If we have to delete all versions of the LevelFlags that also have 22..

I'm working on the next query

[Aggem8]

How do you fix the hole? That is my biggest problem..

[Aggem8]

First Part

Code: Select all

DELETE t.* FROM geo_patch t JOIN geo_patch t1 ON(t.Version = t1.Version) WHERE t1.LevelFlags = '22';

Second part

Code: Select all

UPDATE geo_patch gp JOIN
       (select version, (@ver := @ver + 1) as newver
        from (select distinct version from geo_patch) gp2 cross join
             (select @ver := 0) vars
        order by version
       ) gp2
       ON gp.version = gp2.version
    set gp.Version = gp2.newver;

[Adamld85]

Running up a test server now to test this out. Will let you know if there are any problems.

Great work, if this does what it should you have helped the LiF community immeasurably.

[Adamld85]

At this stage it is getting stuck on the first query. It initially was causing an SQL Query timeout and I've updated the timeout duration to allow it longer to run and at the moment it's still running. Hard to tell if it's doing anything.

EDIT: Ok I've cancelled the query and then changed DELETE to SELECT and the amount of rows it is fetching is the same as before running the DELETE so it's not actually deleting anything.

Strange as the SELECT successfully gets all the rows matching the criteria, for some reason it just won't delete.

[Adamld85]

Ok doesn't look like it works unfortunately. The server starts up with errors for the terrain and once started the clients cannot connect and also show terrain errors. See attached log.

http://server.7gamers.com.au/owncloud/p ... ae8a397d6f

[Scrubbs]

I was thinking of writing a python script for this with a "for" loop that incremented the Version automatically after deleting / wiping the info. Example:

Find tunnel info and all versions associated with it
Delete all rows associated with that modfication (noted by the level flag of 22 and the versions before that that change from 60000 decrementing to 0)
reset all "Version" entries to 0 for the TerID associated with the tunnel
Use python script or SQL query to increment the entries from 1 to X (max entries for that TerID)
Change Version entry in terrain_blocks to reflect new Version Max entry

Would that work? Seems like it would. Maybe just a simple SQL query would work too, but I couldn't find a simple enough one that did the incrementing job.

I tried something like the following but kept getting a syntax error:

Code: Select all

SET @newver := 0;
UPDATE geo_patch
SET Version = (@newver := @newver + 1)
WHERE TerID= xxx
ORDER BY id;

Not a pro at SQL so if you see something horribly wrong, let me know

EDIT: OK so I noticed where and why this wouldn't work, but working on a new plan using this as a basis

EDIT 2: Alright so I think I got it. It's a bit time consuming, but it seems to work.

Steps:

Find out the geodataid of the tile you want to work with by standing on it in the game. Find that id in the database and delete everything for it except the changeindex 1 entry. I then ran the following SQL queries:

Code: Select all

UPDATE geo_patch SET Version = 1 WHERE TerID = XXX;

Where the TerID matches whatever ID your geodataid is in. I then ran the following SQL query:

Code: Select all

SET @newver := 1;
UPDATE geo_patch SET Version = (@newver := @newver +1) WHERE TerID = XXX AND ChangeIndex = 1;

This gives me an accurate number of total changes made for the terrain ID that I'll put into the terrain_blocks table. Now, if it hasn't been very long since you made the tunnel and now decided you want to get rid of it, the amount of fixing the matching changeindex 2s 3s 4s, etc should be minimal. You'll have a bunch of version 1s left in the DB if your tunnel was big with changeindexes above 1. You'll need to match these based on ID and geodataid to see what version they need to be assigned. This is where I need to make a SQL query that will take the version as a variable from the current id - 1 and set the version to that number. Example:

id 202 terid 449 version 1 changeindex 2 geodataid 11771773
id 201 terid 449 version 567 changeindex 1 geodataid 117771773

id 202 should be version 567 to match, but it's not. I need a SQL query that will go "take version 567 from id 201 and set id 202 version to 567, if that makes sense. I'm sure I could write a Python script to do that (or all of this), but I'm trying to keep it SQL only for now. There are some functions and procedures in this DB, so it should be doable, I've just not done that before with MySQL.

Now, I had a tunnel system that started in TerID 446 and apparently went into TerID 449, so just editing the tile for the tunnel entrance didn't work. However finding the info for the actual tunnel tiles looks like it holds the data for ALL the adjoining tunnels, but I can't be sure. You may have to edit all the information each time per TerID your tunnel spans, or you may have to edit out each tile. Haven't researched that far.

I invite anyone to also try this out and let me know if I'm way off base

[Adamld85]

I destroyed my test server database with the last test so I'll set up a new duplicate and try what you have suggested tomorrow morning.

[Vanethian]

Would be useful to have a script for all of this. I hope someone will find something easy and workable.

The tunnels issue is another important issue, just received a complain from a player days ago who made tunnels in a wrong place.

[Adamld85]

Tunnels are one of the biggest problems for me as a server admin. They are used for griefing and there is nothing that can be done about it until we have something like this script working.

If it is very time consuming to make the fixes then the griefing can still occur but if we have a quick script that we can run without painstaking effort then it becomes less of a problem.

It would be great if we could get some dev assistance since these are their issues we're tackling.

[Aggem8]

First Part

Code: Select all

SELECT t.* FROM geo_patch t JOIN geo_patch t1 ON(t.Version = t1.Version) WHERE t1.LevelFlags = '22';

Code: Select all

SELECT t.* FROM geo_patch t JOIN geo_patch t1 ON(t.GeoDataID = t1.GeoDataID) WHERE t1.LevelFlags = '22';

Then delete all the id's you get from both select

Second part

Code: Select all

UPDATE geo_patch gp JOIN
       (select version, (@ver := @ver + 1) as newver
        from (select distinct version from geo_patch) gp2 cross join
             (select @ver := 0) vars
        order by version
       ) gp2
       ON gp.version = gp2.version
    set gp.Version = gp2.newver;

Third
Set the right Geoversion in the terrain_blocks table

But the problem in this is, it dont just delete all tunnels it will also delete what people have been digging and flatting out.

So 22 is not just tunnels...

[Adamld85]

But the problem in this is, it dont just delete all tunnels it will also delete what people have been digging and flatting out.

So 22 is not just tunnels...

That is indeed a problem. Perhaps if we isolated it by location first to only catch terrain data in an area we know we do not need to preserve terraforming.

Maybe then we can apply the butcher's knife knowing we've already localised the area?

[Balagor]

Make hourly saves from the server and rollback if someone gets griefed.

I wouldn't touch that database, last time people did minor changes to it, they had to reset the whole server because an update screwed them over.

[Adamld85]

Making the entire server lose an hour of work is still effective griefing.

[Balagor]

Making the entire server lose an hour of work is still effective griefing.

uh.. was I saying something else? Did I say it wasn't griefin? I was merely suggesting not to touch that database unless you want to increase the probability of corruption on the next patch by a massive percent.

[Chessmaster42]

Just chiming in here that I have also been working on this off and on. Now that I have a more solid idea of how that terrain data is logged I'm hoping that tonight when I get home from work I will dive into this full-force. I do database management (among many other things) as part of my job using Oracle and MySQL so this kinda thing is right up my alley

I'll see if I can get a stored proc together to run this so that we don't have to keep running individual queries.

Also, I think it might be possible to trigger the stored proc from client-side by piggy-backing off of say the one for creating a container. And what we could so is have it check for a specific item in a specific quantity and quality in the SQL query and if all conditions are met then it would call our "terrain fixer" stored proc using the geoid data from the character in the initial stored proc.

To keep that secure we could use some item that's not yet implemented like say dropping 42 pieces of the God's Favor meta-currency onto the ground.

Thoughts?

[Scrubbs]

Sorry, I don't know much about SQL, but I was wondering (kind of along the same vein) if the holes in the world (looks like a lot of them happen along terrain block lines, like where TerID 446 hits TerID 449 for example) come from the NULL geo_patch entries in the DB? Like some entries will have no altitude or substance data at all, yet will be alongside other datablocks that do. I haven't messed with that at all to see if it fixes the hole by filling in those NULL entries.

[Chessmaster42]

Sorry, I don't know much about SQL, but I was wondering (kind of along the same vein) if the holes in the world (looks like a lot of them happen along terrain block lines, like where TerID 446 hits TerID 449 for example) come from the NULL geo_patch entries in the DB? Like some entries will have no altitude or substance data at all, yet will be alongside other datablocks that do. I haven't messed with that at all to see if it fixes the hole by filling in those NULL entries.

That's nothing to do with what we're working on here but merely a side-effect of how the game engine renders the terrain. It's actually a fairly common problem in games that have alterable height/shape terrain. Skirting the terrain helps along the patch edges but sometimes weird things happen between two tiles of a patch when the game engine just can't tessellate the polygons of the tile to match everything up.

As for the topic at hand, I am still tinkering with the SQL but instead of deleting and rewriting the terrain data I'm working towards the route of seeing what can be done by adding in more data to the geo_patch log table. So far not much luck but I think it might be possible and should definitely be cleaner/safer if I can get it working.

[Maegfaer]

I am still tinkering with the SQL but instead of deleting and rewriting the terrain data I'm working towards the route of seeing what can be done by adding in more data to the geo_patch log table. So far not much luck but I think it might be possible and should definitely be cleaner/safer if I can get it working.

I completely agree, after having a closer look at the database and doing some experiments unrelated to this topic, invalidating records is a lot safer than trying to delete them.

[Chessmaster42]

I've partially decoded the LevelFlags column. The following are the bit values as far as I can determine:

128 - Normal Terrain / No corner height change
64 - Something about crops
32 - Something about crops
16 - Corner 1 height changed
8 - Corner 2 height changed
4 - Corner 3 height changed
2 - Corner 4 height changed
1 - Has substance

I've also got a trigger to run the delete tunnel proc:

Code: Select all

CREATE DEFINER=`root`@`localhost` TRIGGER `t_adminUndoTunnel` AFTER INSERT ON `movable_objects` FOR EACH ROW BEGIN
   SET @godsFavorCount = (SELECT COUNT(*) FROM items WHERE ContainerID = NEW.RootContainerID AND ObjectTypeID = 1062);
   SET @location = (SELECT GeoID FROM `character` WHERE ID = NEW.OwnerID);
   SET @altitude = (SELECT GeoAlt FROM `character` WHERE ID = NEW.OwnerID);
   IF @godsFavorCount > 0 THEN
      CALL p_deleteTunnel(@location, @altitude);
   END IF;
END

The trigger works by dropping the 'Gods Favor' metacurrency item onto the ground in a new dropped items bag. And this part works. However, I'm still working on the procedure but hopefully I'll have something more this weekend.