CloudStack MySQLIntegrityConstraintViolationException: Duplicate entry

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;