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)
STOP SLAVE;

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

Restart the slave on the first server and check the status:
START SLAVE;
SHOW SLAVE STATUS \G
anzeigen.

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:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

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 mysql_clientdb_plugin.inc.php 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

slave_skip_errors=1007,1008

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 Reply to MrWolf Cancel reply

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

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