Sign in to follow this  
razoreqx

1.0.0.6 Abort due to constraint violation (NOT NULL constraint failed: GMMESSAGES.ID)

Recommended Posts

Not sure if this is an issue with 1.0.0.6 but i dont remember seeing these errors in the past versions.

 

 

[12:38:23 PM] WARNING com.wurmonline.server.Players: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: GMMESSAGES.ID)
java.sql.SQLException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: GMMESSAGES.ID)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.execute(DB.java:810)
    at org.sqlite.core.DB.executeUpdate(DB.java:847)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:86)
    at com.wurmonline.server.Players.addGmMessage(Players.java:3926)
    at com.wurmonline.server.Players.sendGmMessage(Players.java:2181)
    at com.wurmonline.server.creatures.Communicator.reallyHandle_CMD_MESSAGE(Communicator.java:6226)
    at com.wurmonline.server.creatures.Communicator.reallyHandle(Communicator.java:2392)
    at com.wurmonline.communication.SocketConnection.tick(SocketConnection.java:615)
    at com.wurmonline.communication.SocketServer.tick(SocketServer.java:172)
    at com.wurmonline.server.Server.run(Server.java:2422)
    at java.util.TimerThread.mainLoop(Unknown Source)
    at java.util.TimerThread.run(Unknown Source)

Share this post


Link to post
Share on other sites

[06:47:36 PM] INFO com.wurmonline.server.creatures.Communicator: Devoniklon- action request failed. No such creature. com.wurmonline.server.creatures.NoSuchCreatureException: No such creature for id: 2136022969990401
[06:47:41 PM] WARNING com.wurmonline.server.Players: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: GMMESSAGES.ID)
java.sql.SQLException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (NOT NULL constraint failed: GMMESSAGES.ID)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.execute(DB.java:810)
    at org.sqlite.core.DB.executeUpdate(DB.java:847)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:86)
    at com.wurmonline.server.Players.addGmMessage(Players.java:3926)
    at com.wurmonline.server.Players.sendGmMessage(Players.java:2181)
    at com.wurmonline.server.creatures.Communicator.reallyHandle_CMD_MESSAGE(Communicator.java:6226)
    at com.wurmonline.server.creatures.Communicator.reallyHandle(Communicator.java:2392)
    at com.wurmonline.communication.SocketConnection.tick(SocketConnection.java:615)
    at com.wurmonline.communication.SocketServer.tick(SocketServer.java:172)
    at com.wurmonline.server.Server.run(Server.java:2422)
    at java.util.TimerThread.mainLoop(Unknown Source)
    at java.util.TimerThread.run(Unknown Source)

Share this post


Link to post
Share on other sites

I have never had this particular error. In fact I just checked and my GMMESSAGES table is completely empty. Do you know what triggers a write to this table?

Share this post


Link to post
Share on other sites

I'll have to go back and look.    This is a new one for us.

Share this post


Link to post
Share on other sites

I think it's triggered by someone writing in the GM chat channel. The server tries to log it in the database but the table has an error.

 

I've checked the current database templates - adventure is ok, creative is borked. Same thing as the permissions history error.

 

 

Share this post


Link to post
Share on other sites

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 `GMMESSAGES_NEW` (
	`ID`	INTEGER NOT NULL,
	`TIME`	BIGINT NOT NULL,
	`SENDER`	VARCHAR(200) NOT NULL,
	`MESSAGE`	VARCHAR(200) NOT NULL,
	PRIMARY KEY(ID)
);
INSERT INTO `GMMESSAGES_NEW` SELECT `ID`,`TIME`,`SENDER`,`MESSAGE` FROM `GMMESSAGES`;
DROP TABLE `GMMESSAGES`;
ALTER TABLE `GMMESSAGES_NEW` RENAME TO `GMMESSAGES`;
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.
 

  • Like 2

Share this post


Link to post
Share on other sites

thanks bdew.  

We had a freeze yesterday.   The server just stopped responding.. You know the *refreshing* message.    Ive seen this in the past when backups or the map update run at the same time but its never lasted more than a few seconds..  

We had this happen yesterday but never recovered.   Backup was not running nor the map update.   

No errors in the server.log   no crash files..   Nothing.       My wrapper program that runs each cluster will not detect these as a server failure and since its java i really dont know how to have it do some type of keep alive to see if this is occurring. 

So the server sat in a continual " refresh paint until i manually crashed the server.  I run FireDaemon Pro as the wrapper.    I can have it call external programs as part of the service.   I probably need to figure out a way to make a RMI call or something to this effect to see if this refresh condition lasts more than 2 queries in a 5 min period.    I would consider this a lock and could then issue a restart to the service running the cluster.    I just need to figure out how or what application would be best suited to attempt to access the DB.  

 

Thoughts?

 

 

Edited by razoreqx

Share this post


Link to post
Share on other sites
7 minutes ago, bdew said:

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 `GMMESSAGES_NEW` (
	`ID`	INTEGER NOT NULL,
	`TIME`	BIGINT NOT NULL,
	`SENDER`	VARCHAR(200) NOT NULL,
	`MESSAGE`	VARCHAR(200) NOT NULL,
	PRIMARY KEY(ID)
);
INSERT INTO `GMMESSAGES_NEW` SELECT `ID`,`TIME`,`SENDER`,`MESSAGE` FROM `GMMESSAGES`;
DROP TABLE `GMMESSAGES`;
ALTER TABLE `GMMESSAGES_NEW` RENAME TO `GMMESSAGES`;
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.
 

 

 

Thanks i'll run this but I dont think it had anything to do with the freeze we saw yesterday but will certainly clean up the message logs for this table.

Share this post


Link to post
Share on other sites

We talk in the GM channel all the time, nothing in our GMMESSAGES database and nothing ever shows in the console. So that is not the trigger.

 

Ohhh have you ever warned anyone with the actual warning feature? I have never used that, maybe thats it?

Edited by Xyp

Share this post


Link to post
Share on other sites
5 hours ago, Xyp said:

We talk in the GM channel all the time, nothing in our GMMESSAGES database and nothing ever shows in the console. So that is not the trigger.

 

we talk in GM as well.. its not the trigger.. My guess its one of the mods.  Im digging through the source code on each one.

 

EDIT:  I take that back.. This was the trigger for that error message after testing with another GM.   

Running that sql script fixed this issue btw.  (though this wasnt what was causing our game hang)  Still tracking down that offender.

 

Edited by razoreqx

Share this post


Link to post
Share on other sites

If you are going to attempt to access the database in any way shape or form beyond a quick read while the server is running, make sure you have pooled db turned on in the wurm.ini

  • Like 1

Share this post


Link to post
Share on other sites
Just now, Xyp said:

If you are going to attempt to access the database in any way shape or form beyond a quick read while the server is running, make sure you have pooled db turned on in the wurm.ini

 

Yup already enabled..   Thanks for the hint.

Share this post


Link to post
Share on other sites

You can use VisualVM to get a thread dump from a java process to see what's it doing if it's completely stuck (usually some kind of threading deadlock).

 

For RMI i have a handy tool here, it currently can only broadcast messages and shutdown the server but i could add any other call that's in the RMI API.

 

Edit: Looking at the code i'm still pretty sure the error above should be triggered when talking in GM if the table is borked (and if it isn't it should log stuff into the database).

 

Are you guys sure you're talking in GM channel as opposed to MGMT? :P

Edited by bdew
  • Like 1

Share this post


Link to post
Share on other sites
5 minutes ago, bdew said:

You can use VisualVM to get a thread dump from a java process to see what's it doing if it's completely stuck (usually some kind of threading deadlock).

 

For RMI i have a handy tool here, it currently can only broadcast messages and shutdown the server but i could add any other call that's in the RMI API.

 

Edit: Looking at the code i'm still pretty sure the error above should be triggered when talking in GM if the table is borked (and if it isn't it should log stuff into the database).

 

Are you guys sure you're talking in GM channel as opposed to MGMT? :P

 

 

As for the TOOL!!! HOT DAMN!   How the hell did i miss that!

As for VisualVM.   I'll check it out.   It would need to have some type of command line and condition it can return to FireDaemon Pro to signal a lock state.    

Share this post


Link to post
Share on other sites
1 minute ago, razoreqx said:

As for VisualVM.   I'll check it out.   It would need to have some type of command line and condition it can return to FireDaemon Pro to signal a lock state. 

 

It can't, it's a debugging tool. What i mean is that next time it hangs you can use it (it even can work remotely, though i'm not sure what exactly it needs for that) to check why and hopefully get some info for CC or mod authors to fix it.

Share this post


Link to post
Share on other sites

I've added 2 new commands to the RMI tool, isrunning and playercount which might be useful to detect if the server is alive.

 

It's not 100%, and depending on how the server is exactly hanged it might return success even if it's not responding to players, but it's a start.

Share this post


Link to post
Share on other sites
4 hours ago, bdew said:

I've added 2 new commands to the RMI tool, isrunning and playercount which might be useful to detect if the server is alive.

 

It's not 100%, and depending on how the server is exactly hanged it might return success even if it's not responding to players, but it's a start.

 

Checking this out now.    Even though the server hangs its still holding the player count so it wouldnt help me in a hang..   BUT

 

   This would work great for a nightly maintenance reboot though.    I'm adding it as a dependency for the service now.   

Share this post


Link to post
Share on other sites
6 hours ago, bdew said:

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 `GMMESSAGES_NEW` (
	`ID`	INTEGER NOT NULL,
	`TIME`	BIGINT NOT NULL,
	`SENDER`	VARCHAR(200) NOT NULL,
	`MESSAGE`	VARCHAR(200) NOT NULL,
	PRIMARY KEY(ID)
);
INSERT INTO `GMMESSAGES_NEW` SELECT `ID`,`TIME`,`SENDER`,`MESSAGE` FROM `GMMESSAGES`;
DROP TABLE `GMMESSAGES`;
ALTER TABLE `GMMESSAGES_NEW` RENAME TO `GMMESSAGES`;
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.
 

 

 

Cannot start a transaction within a transaction: BEGIN TRANSACTION;

Share this post


Link to post
Share on other sites

If using SQLite Browser, remove 

BEGIN TRANSACTION;

and also

COMMIT TRANSACTION;
.exit

That gets handled with the run and write buttons.

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this