Solving MariaDB master-slave problems

In this article, you will find several error messages which you can come across (hopefully not of course) when using a MariaDB master-slave setup and the solutions you can use for it. The error messages which are treated are:

• Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID X-X-X, which is not in the master's binlog'.
• Error 'Can't create database 'databasename'; database exists' on query.
• Error 'Can't drop database 'databasename'; database doesn't exist' on query.

This article is written with MariaDB 10.0+ in mind.

Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID X-X-X, which is not in the master's binlog'.

When you see this message, your slave is probably trying to start from another GTID than the one your master is currently in. Fortunately, there are various solutions for this.

Step 1

 First, check the GTID of your master by executing the following command on your master VPS from an SQL Shell (mysql -u root -p):

SHOW MASTER STATUS;

Write down the value behind the line 'gtid_current_pos' and close the SQL shell (exit)

Step 2

There are three options to solve this problem: adjust the Global Transaction ID, set the Using_Gtid to Slave_Pos, or reset the slave completely.

Option 1: Adjust the GTID by executing the following commands on your slave in an SQL shell (mysql -u root -p):

STOP SLAVE;
SET GLOBAL gtid_slave_pos = 'x-x-x';
START SLAVE;

Replace the x-x-x value above with the value of 'gtid_current_pos' you just noted.

Option 2: There is a big chance that your slave uses the value that it gets from its own bin log as gtid_current_pos (the slave also builds a binlog when it functions as a master after a failover). This can be corrected as follows:

STOP SLAVE;
SET GLOBAL gtid_slave_pos = 'x-x-x';
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slavename', MASTER_PASSWORD='slavename_pw', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=slave_pos;
START SLAVE;

Replace the value x-x-x with the value of 'gtid_current_pos' you just noted.

What this does:

1. You set the gtid_slave_pos to the number of the master.
2. You tell that the gtid_current_pos uses the value of gtid_slave_pos at the end of the change master command.

Option 3: The slave is reset as follows (don't forget to change the master hot, slavename, password, etc. to that of your setup):

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slavename', MASTER_PASSWORD='slavename_pw', MASTER_PORT=3306, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID=current_pos;
START SLAVE;

Step 3

Finally, check that the status of your slave is in order:

SHOW SLAVE STATUS\G;

Error 'Can't create database 'databasename'; database  exists' on query / Error 'Can't drop database 'databasename'; database doesn't exist' on query.

This problem arises when the synchronization is not going well, for example after a split-brain situation, and can ensure that you cannot create or delete databases / tables / etcetera. If this is the case, you do not necessarily have to perform a dump from your master and import it on your slave, but you can try to skip the relevant synchronization step first. Use the following commands from an SQL shell on your slave:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
SHOW SLAVE STATUS \G;

Tthe last command is used to check that your slave is now replicating correctly.

Alternatively, you can let the slave pick up the replication from a specific point, to try to synchronize earlier changes. Use the commands below in an SQL shell, replacing x-x-x with the desired global transaction ID.

SET GLOBAL gtid_slave_pos='x-x-x'; 
START SLAVE UNTIL master_gtid_pos='x-x-x';

The error messages in this article are only a few which are possible in a MariaDB master-slave setup. Do you come across one that is not treated here, and would you like to see documentation about it? Please let us know in a message at the bottom of this article under 'comments' (including the specific error message).

Should you have any other questions left regarding this article, do not hesitate to contact our support department. You can reach them via the ‘Contact Us’ button at the bottom of this page.