Original master fails to join maxscale as slave once he is back up

Question

In my projecto i am using docker compose to run an api (using tomcat). That api connect to a maxscale server that "connects" to other 3 databases (1 master and 2 slaves).

I've enabled replication on the mariadb side and it is recognized on the maxscale UI.

If i shut down my master one of the slaves gets promoted to a new master (the expected result). The problem comes when i turn my original master back on i get the following error:

db   | 2023-07-07  6:58:11 5 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MariaDB server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Internal MariaDB error code: 1593

The strange part is the fact that if I turn of the slave that was promoted to master and turn him back on he joins as a slave again no problem at all.

Docker compose file:

#Use docker compose up --build to run. Without the build we might have problems connecting to the db

version: '3'
#use this volumes if i want to use the same volume in multiple services
#then we call data:path/ in the services to use this volume
volumes:
  data:
services:
  #Database container
  db:
    container_name: db
    image: mariadb
    environment:
      MYSQL_USER: root
      #Password for my root user
      MYSQL_ROOT_PASSWORD: 123456
    #Copies the init.sql file that has all the initializations for the docker entry point to create the tables
    volumes:
      - ./Database/init.sql:/docker-entrypoint-initdb.d/init.sql
      - ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
    
    #Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
    #Port 50000 gets exposed to my machine but not other images
    ports:
      - "50000:3306"
    healthcheck:
      test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 3
    command:
      - '--log-bin'
      - '--server-id=3001'
      - '--log-basename=my_logs'
      - '--log-slave-updates'

      
  db2:
    container_name: db2
    image: mariadb
    environment:
      MYSQL_USER: root
      #Password for my root user
      MYSQL_ROOT_PASSWORD: 123456
    #Copies the init.sql file that has all the initializations for the docker entry point to create the tables
    volumes:
      - ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
    
    #Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
    #Port 50000 gets exposed to my machine but not other images
    ports:
      - "50010:3306"
    healthcheck:
      test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 3
    depends_on:
      db:
        condition: service_healthy
        restart: false
    command:
      - '--log-bin'
      - '--server-id=3002'
      - '--log-basename=my_logs'
      - '--log-slave-updates'
    

  db3:
    container_name: db3
    image: mariadb
    environment:
      MYSQL_USER: root
      #Password for my root user
      MYSQL_ROOT_PASSWORD: 123456
    #Copies the init.sql file that has all the initializations for the docker entry point to create the tables
    volumes:
      - ./Database/initSlaves.sql:/docker-entrypoint-initdb.d/initSlaves.sql
    
    #Maps my computers 50000 port to the 3306 on the container. Now i can access the database using localhost:50000
    #Port 50000 gets exposed to my machine but not other images
    ports:
      - "50020:3306"
    healthcheck:
      test: ["CMD", "/usr/local/bin/healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 3
    depends_on:
      db:
        condition: service_healthy
        restart: false
    command:
      - '--log-bin'
      - '--server-id=3003'
      - '--log-basename=my_logs'
      - '--log-slave-updates'
    
  #Need tomcat to host the api
  api:
    container_name: api
    image: tomcat
    #will place my jar/war file inside the webapps folder
    volumes:
      - ./api/target/api.war:/usr/local/tomcat/webapps/api.war
    #use localhost:50001 to access tomcat
    #but in the url for the database i have to use port 3306 (since that one is visible to my docker image)
    ports:
      - "50001:8080"
    depends_on:
      db:
        condition: service_healthy
        restart: false
      db2:
        condition: service_healthy
        restart: false
      db3:
        condition: service_healthy
        restart: false
  maxscale:
    image: mariadb/maxscale
    container_name: max
    ports:
      - "4006:4006" 
      - "4008:4008"
      - "50002:8989"
    environment:
      MYSQL_USER: maxscale
      #Password for my root user
      MYSQL_ROOT_PASSWORD: maxscale
    depends_on:
      db:
        condition: service_healthy
        restart: false
      db2:
        condition: service_healthy
        restart: false
      db3:
        condition: service_healthy
        restart: false
    volumes:
      - ./maxscale/maxscale.cnf:/etc/maxscale.cnf.d/my-maxscale.cnf

init.sql

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';
GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'%' IDENTIFIED BY 'maxscale' WITH GRANT OPTION;
GRANT PROXY ON ''@'%' TO 'maxscale'@'%' WITH GRANT OPTION;

CREATE USER 'repluser'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

FLUSH PRIVILEGES;

CREATE SCHEMA mydb;

use mydb;

CREATE  TABLE user ( 
    id                 INT  NOT NULL   AUTO_INCREMENT  PRIMARY KEY,
    email              VARCHAR(45)  NOT NULL     ,
    password           VARCHAR(512)  NOT NULL     ,
    name               VARCHAR(45)  NOT NULL     ,
    age                INT  NOT NULL     ,
    UNIQUE             (email)
 ) ENGINE = InnoDB;

CREATE  TABLE friends ( 
    userId             INT  NOT NULL     ,
    friendId           INT  NOT NULL     ,
    date               DATETIME  NOT NULL     ,
    CONSTRAINT pk_friends PRIMARY KEY ( UserId, FriendId )
 ) ENGINE = InnoDB;

CREATE INDEX friendId_idx ON friends ( userId  ASC, friendId  ASC ) VISIBLE;

CREATE  TABLE posts ( 
    userId             INT  NOT NULL     ,
    date               DATETIME  NOT NULL     ,
    text               VARCHAR(512)       ,
    image              MEDIUMBLOB       ,
    CONSTRAINT pk_posts PRIMARY KEY ( UserId, Date )
 ) ENGINE = InnoDB;

ALTER TABLE posts ADD CONSTRAINT FK_UserId FOREIGN KEY ( userId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE friends ADD CONSTRAINT FK_FriendId_User FOREIGN KEY ( userId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE friends ADD CONSTRAINT FK_FriendId_Friend FOREIGN KEY ( friendId ) REFERENCES user( id ) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO user (email,password,name,age) VALUES ("admin@admin.com","123456","Admin",31);

INSERT INTO user (email,password,name,age) VALUES ("user1@user.com","123456","user1",31);
INSERT INTO user (email,password,name,age) VALUES ("user2@user.com","123456","user2",31);
INSERT INTO user (email,password,name,age) VALUES ("user3@user.com","123456","user3",31);

INSERT INTO posts (userId,date,text,image) VALUES (2,now(),"Hello world",NULL);
DO SLEEP(3);
INSERT INTO posts (userId,date,text,image) VALUES (2,now(),"Hello world 2",NULL);
INSERT INTO posts (userId,date,text,image) VALUES (3,now(),"Hello world 3",NULL);

INSERT INTO friends (userId,friendId,date) VALUES (2,3,now());
INSERT INTO friends (userId,friendId,date) VALUES (2,4,now());

initSlaves.sql

CHANGE MASTER TO
  MASTER_HOST='db',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10;

FLUSH PRIVILEGES;

Ps: I've tried checking the server id on the main master server and he has 3001 while the slaves have 3002 and 3003. Any help would be appreciated.

Answer 1

For failover in MaxScale to work, you should be using GTID replication:

The backends must all use GTID-based replication, and the domain id should not change during a switchover or failover. Primary and replicas must have well-behaving GTIDs with no extra events on replica servers.