Mariadb and Maxscale Failover causing weird bug

Hello,

I am opening this case to find a solution to a weird problem that i am facing with my Master-slave Topology.

I have 2 backends (Master and Slave) on which Maxscale is installed to handle traffic and to perform failovers.

The client has some stream processes that take place during the night (1 am to 8am), When my Master node (lets called it M1) is serving traffic, we do not have any issues with the processes, but when promoting my slave (S1) to master, we get errors when performing the same stream processes at night. Errors like Foreign Key Violation.

Example :

26/06/2024 05:42:19 : DLNEGOCE -
05:42:19 : Lancement du traitement des adh<E9>rents 266,332,348,363,544,560,579,611,614,625,626,627,297,258,259,628,629,884 sur le pid 2147
05:42:19 : Lancement du traitement des adh<E9>rents 667,678,717,766,773,783,810,825,826,837,953 sur le pid 2148
PHP Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`gedimat`.`FACTUREDETAIL`, CONSTRAINT `FACTUREDETAIL_ibfk_1` FOREIGN KEY (`ID_FACTURE`) REFERENCES `FACTURE` (`ID_FACTURE`)) in /home/wwwroot/gedimatflux/www/include/class/class.db.php:277
Stack trace:
#0 /home/wwwroot/gedimatflux/www/include/class/class.db.php(277): PDO->exec()
#1 /home/wwwroot/gedimatflux/admin/inc.cron.php(1499): ownPDO->exec()
#2 /home/wwwroot/gedimatflux/admin/cron_dlnegoce.php(2014): _insertFactureDetail()
#3

thrown in /home/wwwroot/gedimatflux/www/include/class/class.db.php on line 277

This causes the stream processes to fail.

Here are the troubleshooting i performed :

1. Checksum the databases on both backends (using pt-table-checksum) --> No differences between data

2. the foreign_key_checks is 1 on both servers, we do not want to change this to 0, as the same streaming processes is working fine when M1 is the master.

3. Downgrade to a minor 10.3 and upgrade to 10.6 --> same behaviour.

Thank you for your help !

Regards,
Noor