Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17455

Slave data corruption when master is MySQL 5.5 in STATEMENT

    XMLWordPrintable

    Details

      Description

      After a fresh restore of a mariadb slave in STATEMENT MODE data get a logical corruption while other slaves in MySQL 5.5.55 do not.

      • Only change in config is log_slave_update=1 on a mariadb slave

       Slave_IO_State: Waiting for master to send event
                        Master_Host: 95.128.41.29
                        Master_User: replication
                        Master_Port: 3306
                      Connect_Retry: 60
                    Master_Log_File: mysql-bin.015237
                Read_Master_Log_Pos: 40162166
                     Relay_Log_File: mysqld-relay-bin.000076
                      Relay_Log_Pos: 88502261
              Relay_Master_Log_File: mysql-bin.015226
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: No
                    Replicate_Do_DB: tsce_unedic,test_affichage_unedic
                Replicate_Ignore_DB: 
                 Replicate_Do_Table: 
             Replicate_Ignore_Table: 
            Replicate_Wild_Do_Table: 
        Replicate_Wild_Ignore_Table: 
                         Last_Errno: 1452
                         Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`tsce_unedic`.`mouvment_history`, CONSTRAINT `mouvment_history_ibfk_4` FOREIGN KEY (`id_agent`) REFERENCES `agents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)' on query. Default database: 'tsce_unedic'. Query: 'INSERT INTO mouvment_history
      						(
      						action_date,
      						id_agent,
      						id_motif
      						)VALUES(
      						'2018-10-15 04:05:37',
      						'1111282050',
      						'1'
      						)'
                       Skip_Counter: 0
      

      CREATE TABLE `agents` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `nom` varchar(40) NOT NULL DEFAULT '',
        `prenom` varchar(40) NOT NULL DEFAULT '',
        `email` varchar(60) NOT NULL DEFAULT '',
        `login` varchar(100) NOT NULL,
        `mdp` varchar(100) NOT NULL,
        `dispo` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `dispo_cp` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `dispo_inscrit_secours` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'disponibilité pour le canal demande inscription de secours',
        `id_site_e` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_site_de` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_plateforme_e` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_plateforme_de` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_expert_e` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_expert_de` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `id_assedic2` mediumint(8) unsigned NOT NULL DEFAULT '0',
        `valid` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `req_compteur` int(10) unsigned NOT NULL DEFAULT '0',
        `last_mail` int(11) unsigned NOT NULL DEFAULT '0',
        `last_requete` bigint(11) NOT NULL DEFAULT '0',
        `login_time` int(11) unsigned NOT NULL DEFAULT '0',
        `is_sortant` tinyint(1) unsigned NOT NULL DEFAULT '0',
        `is_systeme` tinyint(4) NOT NULL DEFAULT '0',
        `id_manager` int(10) unsigned NOT NULL,
        `id_type_agent` bigint(20) NOT NULL,
        `id_agent_remplacant` int(10) NOT NULL COMMENT 'id agent de remplacement',
        `code_safir` varchar(255) DEFAULT NULL,
        `code_dt` varchar(255) DEFAULT NULL COMMENT 'code direction territorial',
        `exclude_update_agent` tinyint(4) DEFAULT NULL COMMENT 'liste des agents du canal mail.net à ne pas mettre à jour avec le batch du récurrent',
        PRIMARY KEY (`id`),
        UNIQUE KEY `login_2` (`login`),
        KEY `mdp` (`mdp`),
        KEY `dispo` (`dispo`),
        KEY `id_site_e` (`id_site_e`),
        KEY `id_site_de` (`id_site_de`),
        KEY `id_plateforme_e` (`id_plateforme_e`),
        KEY `id_plateforme_de` (`id_plateforme_de`),
        KEY `id_expert_e` (`id_expert_e`),
        KEY `id_expert_de` (`id_expert_de`),
        KEY `valid` (`valid`),
        KEY `req_compteur` (`req_compteur`),
        KEY `last_mail` (`last_mail`),
        KEY `is_sortant` (`is_sortant`),
        KEY `id_assedic2` (`id_assedic2`),
        KEY `is_systeme` (`is_systeme`),
        KEY `last_requete` (`last_requete`),
        KEY `id_type_agent` (`id_type_agent`),
        KEY `code_safir` (`code_safir`),
        KEY `email` (`email`),
        KEY `code_dt` (`code_dt`),
        KEY `exclude_update_agent` (`exclude_update_agent`),
        KEY `dispo_inscrit_secours` (`dispo_inscrit_secours`),
        KEY `nom` (`nom`),
        KEY `prenom` (`prenom`),
        KEY `dispo_cp` (`dispo_cp`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1111288060 DEFAULT CHARSET=latin1
      

      select max(id) from agents;
      +------------+
      | max(id)    |
      +------------+
      | 1111282049 |
      +------------+
      1 row in set (0.00 sec)
       
      select count(*) from agents; 
      +----------+
      | count(*) |
      +----------+
      |   123537 |
      +----------+
      

      nota on table definition strange auto increment value that does not refect the max(id) of the table
      AUTO_INCREMENT=1111288060

      On relay log:

      #181015  4:05:35 server id 2  end_log_pos 88497407      Intvar
      SET INSERT_ID=1111282050/*!*/;
      # at 88497691
      #181015  4:05:35 server id 2  end_log_pos 88499085      Query   thread_id=186671866     exec_time=0     error_code=0
      SET TIMESTAMP=1539569135/*!*/;
      INSERT INTO agents^M
                                              (id,^M
                                              nom,^M
                                              prenom,^M
                                              email,^M
                                              login,^M
                                              mdp,^M
                                              dispo,^M
                                              dispo_cp,^M
                                              id_site_e,^M
                                              id_site_de,^M
                                              id_plateforme_e,^M
                                              id_plateforme_de,^M
                                              id_expert_e,^M
                                              id_expert_de,^M
                                              id_assedic2,^M
                                              valid,^M
                                              req_compteur,^M
                                              last_mail,^M
                                              last_requete,^M
                                              login_time,^M
                                              is_sortant,^M
                                              is_systeme,^M
                                              id_type_agent,^M
                                              id_manager,^M
                                              id_agent_remplacant,^M
                                              code_safir ^M
         )VALUES(^M
                                              '0',^M
                                              'PUERMA',^M
                                              'Sandra',^M
                                              'sandra.puerma@pole-emploi.fr',^M
                                              'ISPU5740',^M
                                              'xxxx',^M
                                              '0',^M
                                              '1',^M
                                              '0',^M
                                              '1183',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '48',^M
                                              '1',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '0',^M
                                              '1',^M
                                              '1111171708',^M
                                              '0', ^M
                                              '81743'^M
                                              )ON DUPLICATE KEY UPDATE^M
                                              id                                      = '0',^M
                                              nom                             = 'PUERMA',^M
                                              prenom                          = 'Sandra',^M
                                              email                           = 'sandra.puerma@pole-emploi.fr',^M
                                              login                           = 'ISPU5740',^M
                                              mdp                             = 'xxxx',^M
                                              dispo                           = '0',^M
                                              dispo_cp                        = '1',^M
                                              id_site_e                       = '0',^M
                                              id_site_de                      = '1183',^M
                                              id_plateforme_e         = '0',^M
                                              id_plateforme_de        = '0',^M
                                              id_expert_e             = '0',^M
                                              id_expert_de            = '0',^M
                                              id_assedic2             = '48',^M
                                              valid                           = '1',^M
                                              req_compteur            = '0',^M
                                              last_mail                       = '0',^M
                                              last_requete            = '0',^M
                                              login_time                      = '0',^M
                                              is_sortant                      = '0',^M
                                              is_systeme                      = '0',^M
                                              id_type_agent           = '1',^M
                                              id_manager                      = '1111171708',^M
                                              id_agent_remplacant = '0',^M
                                              code_safir                      = '81743'
      /*!*/;
      

      From relay log extract :
      Nota: the '0' inside de id of the agent table
      on master binlog extract we get same '0' inside de id

      On working mysql slave this record get inserted in id 1111282050

      SET INSERT_ID=1111282050/!/;

      On mariadb the record is inserted with id 0 but auto increment get increased probably trigger by on duplicate key

      On a working slave

      select * from agents where id=1111282050\G  
      *************************** 1. row ***************************
                         id: 1111282050
                        nom: PUERMA
                     prenom: Sandra
                      email: sandra.puerma@pole-emploi.fr
                      login: ISPU5740
                        mdp: 048ini
                      dispo: 0
                   dispo_cp: 1
      dispo_inscrit_secours: 0
                  id_site_e: 0
                 id_site_de: 1183
            id_plateforme_e: 0
           id_plateforme_de: 0
                id_expert_e: 0
               id_expert_de: 0
                id_assedic2: 48
                      valid: 1
               req_compteur: 0
                  last_mail: 0
               last_requete: 0
                 login_time: 0
                 is_sortant: 0
                 is_systeme: 0
                 id_manager: 1111171708
              id_type_agent: 1
        id_agent_remplacant: 0
                 code_safir: 81743
                    code_dt: 81003
       exclude_update_agent: NULL
      

      On the broken mariadb slave:

      select * from agents where id=1111282050; 
      Empty set (0.00 sec)
       
       
      select * from agents where id=0\G 
      *************************** 1. row ***************************
                         id: 0
                        nom: PUERMA
                     prenom: Sandra
                      email: sandra.puerma@pole-emploi.fr
                      login: ISPU5740
                        mdp: 048ini
                      dispo: 0
                   dispo_cp: 1
      dispo_inscrit_secours: 0
                  id_site_e: 0
                 id_site_de: 1183
            id_plateforme_e: 0
           id_plateforme_de: 0
                id_expert_e: 0
               id_expert_de: 0
                id_assedic2: 48
                      valid: 1
               req_compteur: 0
                  last_mail: 0
               last_requete: 0
                 login_time: 0
                 is_sortant: 0
                 is_systeme: 0
                 id_manager: 1111171708
              id_type_agent: 1
        id_agent_remplacant: 0
                 code_safir: 81743
                    code_dt: NULL
       exclude_update_agent: NULL
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sujatha.sivakumar Sujatha Sivakumar
              Reporter:
              stephane@skysql.com VAROQUI Stephane
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: