Uploaded image for project: 'EJBCA'
  1. EJBCA
  2. ECA-3487

Regression: Unique certificatedata_idx12 is not detected

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: EJBCA 6.0.0
    • Fix Version/s: EJBCA 6.1.1
    • Component/s: PKI core
    • Labels:
      None
    • Environment:
      JBoss 7.1.1.Final
      5.5.36+maria-1~precise (with galera extensions)
      OpenJDK Runtime Environment (IcedTea 2.4.4) (7u51-2.4.4-0ubuntu0.12.04.2)
    • Issue discovered during:
      Integration

      Description

      To reproduce:

      • Apply the index
        CREATE UNIQUE INDEX certificatedata_idx12 ON CertificateData (serialNumber, issuerDN);
      • Start JBoss
        The message "Custom certificate serial number not allowed since there is no unique index on" is shown during startup in the JBoss log.

      Details:
      MariaDB [(none)]> show indexes from ejbca.CertificateData;
      ----------------------------------------------------------------------------------------------------------------------------------------------------

      Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

      ----------------------------------------------------------------------------------------------------------------------------------------------------

      CertificateData 0 PRIMARY 1 fingerprint A 6 NULL NULL   BTREE    
      CertificateData 0 certificatedata_idx12 1 serialNumber A 6 NULL NULL   BTREE    
      CertificateData 0 certificatedata_idx12 2 issuerDN A 6 NULL NULL   BTREE    

      ...

      CREATE TABLE `CertificateData` (
      `fingerprint` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `base64Cert` longtext,
      `cAFingerprint` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `certificateProfileId` int(11) NOT NULL,
      `expireDate` bigint(20) NOT NULL,
      `issuerDN` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `revocationDate` bigint(20) NOT NULL,
      `revocationReason` int(11) NOT NULL,
      `rowProtection` longtext,
      `rowVersion` int(11) NOT NULL,
      `serialNumber` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `status` int(11) NOT NULL,
      `subjectDN` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
      `subjectKeyId` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `tag` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `type` int(11) NOT NULL,
      `updateTime` bigint(20) NOT NULL,
      `username` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`fingerprint`),
      UNIQUE KEY `certificatedata_idx12` (`serialNumber`,`issuerDN`),
      KEY `certificatedata_idx2` (`username`),
      KEY `certificatedata_idx4` (`subjectDN`),
      KEY `certificatedata_idx5` (`type`),
      KEY `certificatedata_idx6` (`issuerDN`,`status`),
      KEY `certificatedata_idx7` (`certificateProfileId`),
      KEY `certificatedata_idx11` (`subjectKeyId`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

      19:59:04,005 INFO [org.cesecore.audit.impl.log4j.Log4jDevice] (MSC service thread 1-7) 2014-03-25 19:59:04+01:00;CERT_STORED;SUCCESS;CERTIFICATE;CORE;Internal database constraint test;-46512
      0866;499602D2;checkUniqueIndexTestUserNotToBeUsed_fjasdfjsdjfsad;msg=Certificate stored for username 'checkUniqueIndexTestUserNotToBeUsed_fjasdfjsdjfsad', fp=caba75f68c833c3c2d33f3f5052b7d5a7
      6e80383, subjectDN 'CN=Allow certificate serial number override 1', issuerDN 'CN=CA for EJBCA test certificates', serialNo=499602D2.
      19:59:04,044 INFO [org.cesecore.audit.impl.log4j.Log4jDevice] (MSC service thread 1-7) 2014-03-25 19:59:04+01:00;ACCESS_CONTROL;SUCCESS;ACCESSCONTROL;CORE;Internal database constraint test;;
      ;;resource0=/ca/-465120866
      19:59:04,182 INFO [org.cesecore.audit.impl.log4j.Log4jDevice] (MSC service thread 1-7) 2014-03-25 19:59:04+01:00;CERT_STORED;SUCCESS;CERTIFICATE;CORE;Internal database constraint test;-46512
      0866;499602D2;checkUniqueIndexTestUserNotToBeUsed_fjasdfjsdjfsad;msg=Certificate stored for username 'checkUniqueIndexTestUserNotToBeUsed_fjasdfjsdjfsad', fp=05a219d835622653192c30eeeee8f01f9
      18b30fb, subjectDN 'CN=Allow certificate serial number override 2', issuerDN 'CN=CA for EJBCA test certificates', serialNo=499602D2.
      19:59:04,213 INFO [org.cesecore.certificates.certificate.CertificateStoreSessionBean] (MSC service thread 1-7) Custom certificate serial number not allowed since there is no unique index on
      (issuerDN,serialNumber) on the 'CertificateData' table.
      19:59:04,223 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (MSC service thread 1-7) SQL Error: 1062, SQLState: 23000
      19:59:04,224 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (MSC service thread 1-7) Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      19:59:04,225 WARN [com.arjuna.ats.arjuna] (MSC service thread 1-7) ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff7f000101:340688b0:5331d1f0:46,
      org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization@7544d045 >: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: D
      uplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateStoreSessionLocal$$$view89.checkForUniqueCertificateSerialNumberIndexInTransaction(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.cesecore.certificates.certificate.CertificateStoreSessionBean.isUniqueCertificateSerialNumberIndex(CertificateStoreSessionBean.java:1085) [cesecore-ejb.jar:]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateStoreSessionLocal$$$view89.isUniqueCertificateSerialNumberIndex(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.cesecore.certificates.certificate.CertificateCreateSessionBean.isUniqueCertificateSerialNumberIndex(CertificateCreateSessionBean.java:624) [cesecore-ejb.jar:]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateCreateSessionLocal$$$view93.isUniqueCertificateSerialNumberIndex(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.ejbca.ui.web.admin.configuration.StartServicesServlet.ejbcaInit(StartServicesServlet.java:286)
      at org.ejbca.ui.web.admin.configuration.StartServicesServlet.init(StartServicesServlet.java:141)
      at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1202)
      ...
      at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
      Caused by: org.hibernate.exception.ConstraintViolationException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
      ...
      ... 122 more
      Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:132)
      ...
      at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
      ... 136 more
      Caused by: org.mariadb.jdbc.internal.common.QueryException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:858)
      at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:907)
      at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:282)
      ... 144 more
      19:59:04,283 INFO [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (MSC service thread 1-7) HHH000010: On release of batch it still contained JDBC statements
      19:59:04,285 ERROR [org.jboss.ejb3.invocation] (MSC service thread 1-7) JBAS014134: EJB Invocation failed on component CertificateStoreSessionBean for method public abstract void org.cesecore.certificates.certificate.CertificateStoreSessionLocal.checkForUniqueCertificateSerialNumberIndexInTransaction(): javax.ejb.EJBTransactionRolledbackException: Transaction rolled back
      at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleEndTransactionException(CMTTxInterceptor.java:115) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateStoreSessionLocal$$$view89.checkForUniqueCertificateSerialNumberIndexInTransaction(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.cesecore.certificates.certificate.CertificateStoreSessionBean.isUniqueCertificateSerialNumberIndex(CertificateStoreSessionBean.java:1085) [cesecore-ejb.jar:]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateStoreSessionLocal$$$view89.isUniqueCertificateSerialNumberIndex(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.cesecore.certificates.certificate.CertificateCreateSessionBean.isUniqueCertificateSerialNumberIndex(CertificateCreateSessionBean.java:624) [cesecore-ejb.jar:]
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
      ...
      at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
      at org.cesecore.certificates.certificate.CertificateCreateSessionLocal$$$view93.isUniqueCertificateSerialNumberIndex(Unknown Source) [cesecore-ejb-interface.jar:]
      at org.ejbca.ui.web.admin.configuration.StartServicesServlet.ejbcaInit(StartServicesServlet.java:286)
      at org.ejbca.ui.web.admin.configuration.StartServicesServlet.init(StartServicesServlet.java:141)
      at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1202)
      ...
      at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
      Caused by: javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.
      at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1177)
      at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:117)
      at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:75)
      at org.jboss.as.ejb3.tx.CMTTxInterceptor.endTransaction(CMTTxInterceptor.java:92) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
      ... 113 more
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
      ...
      at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1165)
      ... 116 more
      Caused by: org.hibernate.exception.ConstraintViolationException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
      ...
      at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
      ... 136 more
      Caused by: org.mariadb.jdbc.internal.common.QueryException: Duplicate entry '1234567890-CN=CA for EJBCA test certificates' for key 'certificatedata_idx12'
      at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:858)
      at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:907)
      at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:282)
      ... 144 more

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                tomas Tomas Gustavsson
                Reporter:
                johan Johan Eklund
                Verified by:
                Johan Eklund
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: