ISPConfig Cluster-Setup could crash the MySQL-replication 1

First note: what I describe here is not an Bug in ISPConfig, but rather a concatenation of unfortunate circumstances.

If you have two ore more database-servers in ISPConfig and they are also running in a mysql-replication (what is conventional), in certain circumstances the mysql-replication could crash with an error 1007 (Can’t create database).

You can test it easy: stop the slave on both servers (if the database mysql is excluded from replication, it is enough to stop the slave on one server)

and create the database testme on the first server.
Create the database on the second server, too.

Restart the slave on the first server and check the status:

You see something like:
Last_SQL_Error: Error 'Can't create database 'testme'; database exists' on query. Default database: 'testme'. Query: 'create database testme'

To restart the replication, stop the slave on the first server and skip the error before starting the slave again:


The reason is that on each server ISPConfig creates a new database. But by replication on MySQL level this is already in place. The CREATE DATABASE results in the error 1007. unfortunately it can not be avoided.

But there are a few solutions:

You can remove the 2. database-server from ISPConfig and use only the master-mastere-replication. Although this meant that the databases remain in the view of the websites highly available.Changes in ISPConfig would fail (create new databases, change paswords etc.), when to ISPConfig connected database server fails. Then change remains in the queue until the server is back online. That is in itself not very useful, but for smaller setups certainly be an option.

Using DRBD for the MySQL-Databases would also be a opportunity. I think it’s not optimal to map a MySQL replication on the file system level, especially the mysql database is identical on both servers. I quite like different root-accounts on each server.

The third possibility is to modify to consider before you create the database if it is already available. This can be achieved easily:

if (!$link->query('USE DATABASE '.$link->escape_string($data['new']['database_name']))) {
  // Create the new database
  if ($link->query('CREATE DATABASE '.$link->escape_string($data['new']['database_name']).$query_charset_table))

    $app->log('Created MySQL database: '.$data['new']['database_name'],LOGLEVEL_DEBUG);
  } else {
    $app->log('Unable to create the database: '.$link->error,LOGLEVEL_WARNING);
} else {
  $app->log('Database already exists: '.$link->error,LOGLEVEL_WARNING);
But even here the problem remains of a split-brains. If a DB server is down, then as soon as he is back online, tries to put the corresponding DB on the other server. Ergo: The replication collapses..

The fourth – and in my opinion best option – avoids the problem on the MySQL layer. Because the problem is not based on ISPConfig, but on MySQL, probably the best way. In MySQL on the slave-side (a master-master replication contains always on each server a master and a slave part) the error 1007 (DB is created, which is already in place) is ignored. The same should also be added for 1008 (error in DROP DATABASE, since the DB already doesn´t exists). Add in the mysqld-section to the my.cnf


and restart MySQL. From now on no more breaks replication if an existing database is to be created or and already deleted databse will be deleted.

Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “ISPConfig Cluster-Setup could crash the MySQL-replication