Problem Mysql in master/master replication

Discussion in 'Installation/Configuration' started by diablo666, Jan 9, 2014.

  1. diablo666

    diablo666 Active Member HowtoForge Supporter

    I've a configuration of ispconfig in Cluster mode.

    Mysql is configure in Master/Master replication and all works fine.

    The only problem heappens when i'll delete a Database from the Ispconfig Interface.

    After deletion all goes ok, but if i'll check the status of mysql cluster i'll get the following error:

    Code:
    mysql> SHOW SLAVE STATUS \G;
    
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: XXX.XXX.XXX.XXX
                      Master_User: slaveuser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000667
              Read_Master_Log_Pos: 1567426
                   Relay_Log_File: mysqld-relay-bin.003528
                    Relay_Log_Pos: 1566155
            Relay_Master_Log_File: mysql-bin.000667
                 Slave_IO_Running: Yes
                Slave_SQL_Running: No
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 1008
                       Last_Error: Error 'Can't drop database 'DB_NAME'; database doesn't exist' on query. Default database: ''. Query: 'DROP DATABASE DB_NAME'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1566010
                  Relay_Log_Space: 1567926
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 1008
                   Last_SQL_Error: Error 'Can't drop database 'DB_NAME'; database doesn't exist' on query. Default database: ''. Query: 'DROP DATABASE DB_NAME'
    1 row in set (0.00 sec)
    
    I'll fix the error running the following commands on the slave

    Code:
    STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    START SLAVE;
    SHOW SLAVE STATUS \G;
    Any way to fix?
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Please make a bugreport in the bugtracker, we will have to add some code in ispconfig that prevents that the database gets deleted on on both nodes by ispconfig.
     
  3. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    You can fix this by adding

    Code:
    slave_skip_errors=1007,1008
    to your my.cnf
     
  4. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    This should not happen when you delete a database. function db_delete uses DROP DATABASE IF EXISTS

    But it (also) happens when you create a database that already exists.

    I think we could just change

    Code:
    query('CREATE DATABASE '
    to

    Code:
    query('CREATE DATABASE IF NOT EXISTS'
    in function db_insert

    If there should be some logging if a database already exists an we skip the creation, i could add a function db_exists. So we could use

    if (!db_exists(DATABASE)) create else LOG

    A function like db_exists may be used in other places, too (for example backup).
     

Share This Page