We have been seeing many variants of the MySQLIntegrityConstraintViolationException error message in CloudStack (CloudStack Duplicate entry). This can occur when creating or starting a VM or when deleting it, during account setup and during the startup of the management server.
The causes can vary, from errors caused by MySQL STRICT_MODE changes to real duplicate entries in the database, but also from wrong AUTO_INCREMENT values in various tables CloudStack uses.
To troubleshoot the root issue, first search the management-server.log-file:
tailf /var/log/cloudstack/management/management-server.log | grep -i duplicate
and also activate the SQL general query log to drill down on the specific query, e. g.:
mysql -p; USE cloud; SET GLOBAL general_log_file="/tmp/fulllog.log"; SET GLOBAL general_log = on; SET GLOBAL log_output = 'file';
From there you can start trouble-shooting: – first try to restart the management server: service cloudstack-management restart – verify the STRICT_MODE settings of your MySQL-instance if you have recently upgraded MySQL or MariaDB and also verify if CloudStack has the correct DB-setup for it; – verify if the tables like account and user have the correct AUTO_INCREMENT-values and in case correct those values:
ALTER TABLE account AUTO_INCREMENT=<LASTID+1>;
ALTER TABLE user AUTO_INCREMENT=<LASTID+1>;
After finishing with the SQL updates, do not forget to disable the general logging:
SET GLOBAL general_log = off;