MYSQL Duplicate entry '0'. No Valid License
- Finn Gale
Analytics
Scenario
When using JEMH for the first time on a new, or recently updated instance using a MySQL Database JEMH may display a ‘No Valid License' Error despite a valid license being available. Additionally, in the JEMH Logs consistent SQLIntegrityConstraintViolationException are thrown by JEMH attempting to create new Entities in the Database.
Example Log:
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'AO_78C957_LIMIT.PRIMARY'
2025-02-25 12:06:35,168 http-nio-8080-exec-9 url: /rest/jemh/latest/banner/jaxbtest; user: admin DEBUG [emh.service.loader.JEMHLimitLoader] CacheLoadTime for com.javahollic.jira.emh.api.ao.IJEMHLimitEntityDef was: 2 mS
2025-02-25 12:06:35,168 http-nio-8080-exec-9 url: /rest/jemh/latest/banner/jaxbtest; user: admin ERROR [jira.emh.service.DefaultJEMHCacheManagerImpl] Cache exception loading Limits value: For input string: ""
2025-02-25 12:06:35,168 http-nio-8080-exec-9 url: /rest/jemh/latest/banner/jaxbtest; user: admin DEBUG [jira.emh.service.DefaultJEMHCacheManagerImpl] Cache value for key: cache.profileUpdatedInfoCache is: -1
2025-02-25 12:06:35,168 http-nio-8080-exec-9 url: /rest/jemh/latest/banner/jaxbtest; user: admin DEBUG [jira.emh.service.DefaultJEMHCacheManagerImpl] Retrieving cache value for key: cache.profileUpdatedInfoCache
2025-02-25 12:06:35,168 http-nio-8080-exec-6 url: /rest/jemh/latest/auditing/operationalSummary/anyAlertStatusErrors; user: admin ERROR [emh.ui.ao.JEMHActiveObjectServiceImpl] Failed to create default limit entity with key: cache.profileUpdatedInfoCache. Error: There was a SQL exception thrown by the Active Objects library:
Database:
- name:MySQL
- version:8.0.34
- minor version:0
- major version:8
Driver:
- name:MySQL Connector/J
- version:mysql-connector-j-8.1.0 (Revision: 7b6f9a337afe6ccb41823df485bf848ca7952b09)
Example JEMH UI Error:
Cause
This is likely caused by a miss-configuration of the mysqld.cnf file as described in the following Atlassian setup documentation:
The linked page includes a list of requirements, including the removal of the following line from the mysqld.cnf file.
sql_mode = NO_AUTO_VALUE_ON_ZEROImpact
Configurations which include this line will prevent Entities from being created where the Entity includes an AUTO_INCREMENT column, (which all IDs for AO Entities are) from being created unless the AUTO_INCREMENT column is null (which it cannot ever be in the case of IDs). This will prevent JEMH and more broadly Jira from creating new Entities.
Solution
The solution is to reconfigure your MySQL Database to use the correct mysqld.cnf configuration as outlined by Atlassian, and restart the database and Jira instance. We always recommend creating a backup of your database when performing updates which include a potential database change such as Major version changes.