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

Slave data corruption when master is MySQL 5.5 in STATEMENT

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

            stephane@skysql.com VAROQUI Stephane added a comment - - edited

            Moved back the relay slave to MySQL 5.5.63 instead of mariadb and the data is now inserted at the auto increment of the agents table, unfortunatly some mariadb 10.1 behind this relay server get corrupted as well.

            stephane@skysql.com VAROQUI Stephane added a comment - - edited Moved back the relay slave to MySQL 5.5.63 instead of mariadb and the data is now inserted at the auto increment of the agents table, unfortunatly some mariadb 10.1 behind this relay server get corrupted as well.
            stephane@skysql.com VAROQUI Stephane added a comment - https://github.com/MariaDB/server/pull/891

            Hello VAROQUI Stephane,

            The main concern reported as part MDEV-17455 is that
            "strange and invalid gap in auto-increment value".

            I see that your latest update links to MDEV-17377,
            Which has addressed the auto-increment issue.

            Can MDEV-17455 be marked as a duplicate of MDEV-17377.

            Please let us know.

            sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello VAROQUI Stephane, The main concern reported as part MDEV-17455 is that "strange and invalid gap in auto-increment value". I see that your latest update links to MDEV-17377 , Which has addressed the auto-increment issue. Can MDEV-17455 be marked as a duplicate of MDEV-17377 . Please let us know.

            Hi Sujatha

            Yes sure that issue was probably a duplicate . Since mixing versions was a temporary state i can’t reproduce it now

            There is an other replication issue that still get no attention that is pretty big and easy to fix in my opinion https://jira.mariadb.org/browse/MDEV-18672
            Hoping this one get attention , or be reported to Kristien Nielsen for a fix .

            Thanks a lot for coming back on this

            /svar

            stephane@skysql.com VAROQUI Stephane added a comment - Hi Sujatha Yes sure that issue was probably a duplicate . Since mixing versions was a temporary state i can’t reproduce it now There is an other replication issue that still get no attention that is pretty big and easy to fix in my opinion https://jira.mariadb.org/browse/MDEV-18672 Hoping this one get attention , or be reported to Kristien Nielsen for a fix . Thanks a lot for coming back on this /svar

            Hello VAROQUI Stephane,

            Thanks for mentioning MDEV-18672. We will look into it.

            sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - Hello VAROQUI Stephane, Thanks for mentioning MDEV-18672 . We will look into it.

            This is a duplicate of MDEV-17377 which is in fixed state. Hence closing this issue.

            sujatha.sivakumar Sujatha Sivakumar (Inactive) added a comment - This is a duplicate of MDEV-17377 which is in fixed state. Hence closing this issue.

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.