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

Error on Startup: key PRIMARY in userEvents


  • Hi,

    after (updateing ubuntu) rebooting after a long time I get this error message at startup:

    01.05.2010 22:49:23 org.apache.catalina.core.ApplicationContext log
    INFO: Initializing Spring root WebApplicationContext
    01.05.2010 22:49:30 org.apache.catalina.core.StandardContext listenerStart
    SCHWERWIEGEND: Exception sending context initialized event to listener instance of class com.serotonin.mango.MangoContextListener
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into userEvents (eventId, userId, silenced) values (?,?,?)]; Duplicate entry '725-3' for key 'PRIMARY'; nested exception is java.sql.BatchUpd
    ateException: Duplicate entry '725-3' for key 'PRIMARY'
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:292)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:606)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:618)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:865)
    at com.serotonin.mango.db.dao.EventDao.insertUserEvents(EventDao.java:121)
    at com.serotonin.mango.rt.EventManager.raiseEvent(EventManager.java:115)
    at com.serotonin.mango.rt.event.type.SystemEventType.raiseEvent(SystemEventType.java:95)
    at com.serotonin.mango.rt.EventManager.resetHighestAlarmLevel(EventManager.java:216)
    at com.serotonin.mango.rt.EventManager.initialize(EventManager.java:244)
    at com.serotonin.mango.MangoContextListener.eventManagerInitialize(MangoContextListener.java:339)
    at com.serotonin.mango.MangoContextListener.contextInitialized(MangoContextListener.java:92)
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3972)
    at org.apache.catalina.core.StandardContext.start(StandardContext.java:4467)
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:791)
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:771)
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:526)
    at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:637)
    at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:563)
    at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:498)
    at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1277)
    at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:321)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1053)
    at org.apache.catalina.core.StandardHost.start(StandardHost.java:722)
    at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1045)
    at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
    at org.apache.catalina.core.StandardService.start(StandardService.java:516)
    at org.apache.catalina.core.StandardServer.start(StandardServer.java:710)
    at org.apache.catalina.startup.Catalina.start(Catalina.java:593)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:289)
    at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:414)
    Caused by: java.sql.BatchUpdateException: Duplicate entry '725-3' for key 'PRIMARY'
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1693)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1108)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:299)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:880)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:590)
    ... 33 more
    01.05.2010 22:49:30 org.apache.catalina.core.StandardContext listenerStop
    SCHWERWIEGEND: Exception sending context destroyed event to listener instance of class com.serotonin.mango.MangoContextListener
    org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into userEvents (eventId, userId, silenced) values (?,?,?)]; Duplicate entry '726-3' for key 'PRIMARY'; nested exception is java.sql.BatchUpdateException: Duplicate entry '726-3' for key 'PRIMARY'
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:292)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:606)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:618)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:865)
    at com.serotonin.mango.db.dao.EventDao.insertUserEvents(EventDao.java:121)
    at com.serotonin.mango.rt.EventManager.raiseEvent(EventManager.java:115)
    at com.serotonin.mango.rt.event.type.SystemEventType.raiseEvent(System

    I havent logged in for a longer period so there is a overflow????

    Any idea where to look?

    Arne


  • The keys that were being inserted (726-3, representing eventId and userId) are very small - very unlikely there is any kind of overflow going on. Is this MySQL?


  • Yes,

    This is mysql the numbers in userEvent ar much higher than the numbers in evnets so it will insert numbers in userEvents, which already.

    After deleting all values in userEvents and Events its running. Ive got a backup ;-)

    Arne


  • There should be a foreign key between events and eventUsers, so having event ids values in the latter not in the former should not have been possible. What version of MySQL are you using? I believe older versions didn't enforce constraints completely.


  • It 5.1.41 and the version in ubuntu 09.10

    maybe its that the table userEvents is a myISAM Table.
    The statement type=InnoDB on the table in line 274 createTables-mysql.sql is missing - bug/typo?.

    Arne


  • It looks that MYISAM does not suppport foreign keys orums.mysql.com/read.php?21,213549,213549

    so maybe run a script on 1.8.3 if mysql db is used.
    Switch that table to innodb and try to create the foreign keys that are missing (at least in my db, which I have restored from backups).

    Arne


  • Yes, there should have been a type=InnoDB in there. I've added this script to the db upgrade for the next version:

    
    alter table userEvents engine=InnoDB;
    delete from userEvents where eventId not in (select id from events);
    delete from userEvents where userId not in (select id from users);
    alter table userEvents add constraint userEventsFk1 foreign key (eventId) references events(id) on delete cascade;
    alter table userEvents add constraint userEventsFk2 foreign key (userId) references users(id) on delete cascade;
    
    

  • running the script line by line in MYSQL Query Browser fixes the database.

    If you want me to test this with the next release post me a rc of that.

    So I consider this bug as closed.

    Arne