SQL error: duplicate key value in a unique or primary key: USEREVENTSPK' defined on 'USEREVENTS'.

---

Any ideas how to fix this?

Server never crashed, but this might have happened a while ago. I didn't use Mango for quite a while.

Alex

Caused by: java.sql.SQLException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 22 more
Caused by: ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.insert(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(Unknown Source)
        at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
        at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
        at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
        ... 16 more
Exception in thread "main" org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into userEvents (eventId, userId, silenced) values (?,?,?)]; The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.; nested exception is java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:241)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:883)
        at com.serotonin.m2m2.db.dao.EventDao.insertUserEvents(EventDao.java:113)
        at com.serotonin.m2m2.rt.EventManager.raiseEvent(EventManager.java:108)
        at com.serotonin.m2m2.rt.event.type.SystemEventType.raiseEvent(SystemEventType.java:93)
        at com.serotonin.m2m2.Lifecycle.terminate(Lifecycle.java:214)
        at com.serotonin.m2m2.Main.main(Main.java:94)
Caused by: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeBatchElement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeBatch(Unknown Source)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:898)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        ... 7 more
Caused by: java.sql.SQLException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
        ... 19 more
Caused by: ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'USEREVENTSPK' defined on 'USEREVENTS'.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexChanger.insert(Unknown Source)
        at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(Unknown Source)
        at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
        at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
        at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
        ... 13 more

We are trying to wrap up this project, but the error still persists. I can't get this sorted our myself. I managed to figure out how to start the derby CLI (if it's called that way) but I have no idea to connect to the database to check for errors.

I am looking here:

http://wiki.apache.org/db-derby/DatabaseConsistencyCheck

I am a MySQL person with no idea about Java. So I am completely lost.

That's where I am:

server:~/mango$ java org.apache.derby.tools.ij
ij version 10.6
ij> connect /home/worker/mango/madb;
IJ ERROR: Unable to establish connection
ij> 

Hi Alex,

What sort of actions produce this error? Have you don't any work from the SQL console module in Mango before? I'm definitely scratching my head how this would have started, but it seems like your Events table's inserted IDs got cleared (or something) and the userEvents table still holds references to all the old events. Feel free to email me at phillip@infiniteautomation.com and we might be able to take a look.

• Phillip

Actually I have no idea. I just noticed one day it doesn't start up and this error is in the log when I try to start the deamon. Can't login, can't do anything.

So what I was hoping for is that you know how to repair the tables. It's a system that is now out of production, but would be good to get it back to work. Otherwise I have to start from scratch ;)

Next project we'll use MySQL and hopefully work with you guys doing the setup and all that (been in contact with Joel) .

Alex

Since I'm guessing you're not scada'ing for user events, you could just delete everything from that table (userEvents). Wish there were more details how this happened...

How would I delete everything from that table?

I am a MySQL guy ;)

I can get you more info on how that happened if you want me to. I can also zip/archive the complete folders + logs for you and share via Dropbox or Google Drive.

Yeah, if you could zip up the madb and the logs and email them that could be interesting when I find a spare moment.

Derby has a MySQL like language, and you could just give the directive 'delete from userEvents' to whatever your tool is.

Another possibility is to port the database into MySQL so that the better toolset is available. Mango has the ability to perform that database port (can be found in the "convert database" type fields in your Mango/classes/env.properties (or Mango/overrides/properties/env.properties if you overrode it) which you just boot with the first time and it should port everything, then modify the environment to use the MySQL. I wouldn't expect an issue in the conversion since it seems the IDs have just [somehow] gotten jumbled and inserts are failing. Doesn't look like your database is wholly corrupted or cannot be read, and it'll convert the whole database before it goes about its other business.

Edit: I'm beginning to wonder if I'm mistaken, and if only the pointValues table is converted...