Permission histories not saving - how to fix

[bdew 2,462]

The database that comes with the creative server template (possibly adventure as well, haven't checked) has an error in the table responsible for permission histories. This causes permission histories to not be saved, and all histories will be lost after the server restarts.

 

If you look at your server log you will see errors like this every time someone changes a permission on anything:

java.sql.SQLException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: PERMISSIONSHISTORY.PK)

To fix this, you will need the sqlite3 command line tool. On windows it's included with the server database (inside sqlite folder), otherwise you will need to install it.

 

Now from a command line, go to your server's sqlite folder run "sqlite3 wurmplayers.db"

 

And paste the following commands into it:

BEGIN TRANSACTION;
CREATE TABLE `PERMISSIONSHISTORY_NEW` (
 `PK` INTEGER NOT NULL,
 `OBJECTID` BIGINT NOT NULL,
 `EVENTDATE` BIGINT NOT NULL,
 `PLAYERID` BIGINT NOT NULL,
 `PERFORMER` VARCHAR(40) NOT NULL,
 `EVENT` VARCHAR(255) NOT NULL,
 PRIMARY KEY(PK)
);
INSERT INTO PERMISSIONSHISTORY_NEW SELECT `PK`,`OBJECTID`,`EVENTDATE`,`PLAYERID`,`PERFORMER`,`EVENT` FROM `PERMISSIONSHISTORY`;
DROP TABLE `PERMISSIONSHISTORY`;
ALTER TABLE `PERMISSIONSHISTORY_NEW` RENAME TO `PERMISSIONSHISTORY`;
COMMIT TRANSACTION;
.exit

This will create a new table with the right structure, copy all existing data into it (just in case, it's most likely empty due to the bug) and remove the old one.

In theory this can be done on a running server, but doing a backup is highly recommended.

Edited February 16, 2016 by bdew

[Taufiq 57]

Thanks for the report. Issue was identified and should have been updated months ago (see here) and all new installs should have had the fix [Edit: but that doesn't seem to be the case]. If that isn't the case I'll have a chat with the others to see what's up with the release process.

 

Edit: Confirmed, fix was never properly propagated into release. Keenan's been working on automating the release process which should avoid similar errors in the future.

[bdew 2,462]

Just downloaded a fresh copy and checked, it's still not fixed in creative template that comes with the dedicated server. Adventure is fixed.

 

Edit: Same for the ones that come with the built in server.

Edited May 18, 2016 by bdew

[Taufiq 57]

Yes, you right - I just did the same. Sorry for the lateness, the release process was opaque to me and I'd assumed it had gone through. Should have checked to confirm.

[Quiver 1]

The same thing happens when you are in GM mode and manage restrictions on an some items.  You can change the quality and set it to no destroy/ no bash and other restrictions click update restrictions. After bringing the server down for a backup and bringing it back up again you click on manage restrictions and its all back to what it was before you set the restriction.  Very annoying especially after you have spent hours setting them. I hope this is fixed soon.

[Xyp 273]

Yeah one thing I hate about sqlite is no ALTER TABLE of any real use, just to change its name. Still not sure why this was chosen for WU, SQLite is mostly an android app database. Wurm comming to an android near you soon(tm)?

 

Then again I suppose if I really think about it from the Wurm team's perspective, it makes for much easier setup than someone having to know how to install, run, and manage a MySQL server.

Edited May 23, 2016 by Xyp

[bdew 2,462]

8 hours ago, Quiver said:

The same thing happens when you are in GM mode and manage restrictions on an some items.  You can change the quality and set it to no destroy/ no bash and other restrictions click update restrictions. After bringing the server down for a backup and bringing it back up again you click on manage restrictions and its all back to what it was before you set the restriction.  Very annoying especially after you have spent hours setting them. I hope this is fixed soon.

 

Is there a database error when that happens? If not it's probably a different bug, possibly worthy of a separate report.

 

2 hours ago, Xyp said:

SQLite is mostly an android app database. Wurm comming to an android near you soon(tm)?

 

SQLite was around for ages before android. It's a fast, lightweight in-process database engine. It was originally made for use in guided missile computers

 

[Xyp 273]

It is fast, light weight, lacking in capabilities, and only useful for small scale light weight processes. Has a high overhead in comparison to engine driven databases. One nifty thing about it however, since it is just a file, you can throw it around with clouds and for anything besides live applications you can serve it from geocentric driven locations. Example you can push it to google business, and have it spread to mini nodes all over. Not as efficient for mission critical applications as node.js but works as a cheap alternative hehe. Still I would prefer MySQL, and I got a server running using it, but I am afraid to use it on live servers due to not having any clue what the wurm devs might do to break it. Fixing a couple tables is much easier than repairing an entire database struture.

 

Its capability to be spread around like that, and so small with no engine to drive it, to be packaged into lightweight apps, is why its the android app go to. I do see with deeper thought why they chose to package wurm this way. I just wish they would make it a little more of an option than they did. Like I would really like to see in the GUI a radio ticker, use MySQL, SQLite, even maybe PostgreSQL (never used the last one but I know some people swear by it). More important things for them to do right now though, mod interface, UNDERGROUND HOUSES WOOT WOOT, and omgah shoulder armor im dieing here.

 

And... Last but not least edit... Again Always loved Tich work, omg bridges, omg Tich <3 but wtf holy mad scientist cow, why didn't they hire Keenan years ago. Also, no no not forgeting Taufiq, I know your work is more tedious trying to clean up the 7 years of wtfh, but it is important, thanks!!!

Edited May 23, 2016 by Xyp

[Xyp 273]

10 hours ago, Quiver said:

The same thing happens when you are in GM mode and manage restrictions on an some items.  You can change the quality and set it to no destroy/ no bash and other restrictions click update restrictions. After bringing the server down for a backup and bringing it back up again you click on manage restrictions and its all back to what it was before you set the restriction.  Very annoying especially after you have spent hours setting them. I hope this is fixed soon.

 

Permissionhistory is just a list of changes to permissions on houses, carts etc. It is what is called when you have your wand active and check for example right click a horse, and go to animals history.

 

I think, and I could investigate to be more sure if needed, that the settings you are talking about is held in Itemsettings. Any errors writing to the database should show in your logs, if you could please look and see. Let me know if you need me to find out for sure exactly which table those manage restriction settings are held in, I will do that for you if needed. First open your log and try a control + f in notepad and find itemsettings, see if that shows any errors like this one. Otherwise perhaps start another thread, with any related errors you might have on hand, and a list of server mods that you are running.

 

Cheers! Reports make a better Wurm!

[Quiver 1]

On ‎5‎/‎23‎/‎2016 at 2:59 AM, Xyp said:

 

Permissionhistory is just a list of changes to permissions on houses, carts etc. It is what is called when you have your wand active and check for example right click a horse, and go to animals history.

 

I think, and I could investigate to be more sure if needed, that the settings you are talking about is held in Itemsettings. Any errors writing to the database should show in your logs, if you could please look and see. Let me know if you need me to find out for sure exactly which table those manage restriction settings are held in, I will do that for you if needed. First open your log and try a control + f in notepad and find itemsettings, see if that shows any errors like this one. Otherwise perhaps start another thread, with any related errors you might have on hand, and a list of server mods that you are running.

 

Cheers! Reports make a better Wurm!

Can you please tell me where to find this log you are referring to so I can do a search for errors in the item settings? 

[bdew 2,462]

If you have ago's server mod launcher the log should be in the root folder of WU server, named server.log.0

 

With vanilla, it should be inside your world folder in logs/wurm.log.0 - though that log is less detailed.