Duplicate erreor while creating indexes on EJBCA database

[Marc-bo]

Hi,

we have recently updated our EJBCA installation to MySLQ8. Following this, we are now looking at all possible optim, including the database.
In the doc we some some nice reco to add index in the database.

But while looking at this, my DBA got a strange error :

mysql> CREATE UNIQUE INDEX crldata_idx6 ON CRLData(issuerDN, crlPartitionIndex, deltaCRLIndicator, cRLNumber);

ERROR 1062 (23000): Duplicate entry 'CN=XXX1 CA,O=XX,DC=XX,DC=XX--1' for key 'CRLData.crldata_idx6'

Here is the request to find the duplicates :

SELECT
issuerDN, COUNT(issuerDN),
crlPartitionIndex, COUNT(crlPartitionIndex),
deltaCRLIndicator, COUNT(deltaCRLIndicator),
cRLNumber, COUNT(cRLNumber)
FROM
CRLData
GROUP BY
issuerDN,
crlPartitionIndex,
deltaCRLIndicator,
cRLNumber
HAVING
(COUNT(issuerDN) > 1) AND
(COUNT(crlPartitionIndex) > 1) AND
(COUNT(deltaCRLIndicator) > 1) AND
(COUNT(cRLNumber) > 1);

+------------------------------+-----------------+-------------------+--------------------------+-------------------+--------------------------+-----------+------------------+
| issuerDN | COUNT(issuerDN) | crlPartitionIndex | COUNT(crlPartitionIndex) | deltaCRLIndicator | COUNT(deltaCRLIndicator) | cRLNumber |COUNT(cRLNumber) |
+------------------------------+-----------------+-------------------+--------------------------+-------------------+--------------------------+-----------+------------------+
| CN=XXX2 CA,O=XX,DC=XX,DC=org | 2 | -1 | 2 | -1 | 2 | 21871 | 2 |
| CN=XXX1 CA,O=XX,DC=XX,DC=org | 2 | -1 | 2 | -1 | 2 | 9481 | 2 |
+------------------------------+-----------------+-------------------+--------------------------+-------------------+--------------------------+-----------+------------------+
2 rows in set (7.21 sec)

XXX1 and XXX2 are the 2 most used CA in this PKI server...and no real idea about the problem, even if it is an issue or not.

Does someone alreday saw this error, and what are the recommanded actions ?

Current config :
-EJBCA 7.4.3.2
-Wildfly 12
-MySQL 8.0 (AWS RDS)

Marc.

[primetomas]

Do you have two CRLs from the same issue with the same CRL number?

[Marc-bo]

According to "CA Structure and CRL" admin page, I have several CRL with the same number (I host 1 CA and more than 10 sub CA on this server).
My DBA suggest to create the index without the UNIQUE option, is it a good solution in my case ?

[primetomas]

It is the combination (IssuerDN, crlNumber) that must be unique. Each of your different CAs have different IssuerDN, so no problem with same CRLs numbers if IssuerDN is the same.
If in the CRLData table you have duplicates of the (issuerDN,crlNumber) pair, it is an issue, indicating some previous error state. Probably it's not the max(crtlNumber) that is duplicate, in which case it's no problem to delete one of the pair.