Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

Navigation

    • Register
    • Login
    • Search
    • Recent
    • Tags
    • Popular

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

    Mango Automation general Discussion
    2
    8
    11157
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • A
      AlexHK last edited by

      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
      1 Reply Last reply Reply Quote 0
      • A
        AlexHK last edited by

        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>
        1 Reply Last reply Reply Quote 0
        • phildunlap
          phildunlap last edited by

          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
          1 Reply Last reply Reply Quote 0
          • A
            AlexHK last edited by

            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

            1 Reply Last reply Reply Quote 0
            • phildunlap
              phildunlap last edited by

              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...

              1 Reply Last reply Reply Quote 0
              • A
                AlexHK last edited by

                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.

                1 Reply Last reply Reply Quote 0
                • phildunlap
                  phildunlap last edited by

                  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...

                  1 Reply Last reply Reply Quote 0
                  • First post
                    Last post