|
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
|
|
|