Search code examples
mariadbhaproxyef-core-2.0galera

Cannot add or update a child row error on Mariadb Galera cluster

When I run performance tests on my web-server, around 2% of the requests return this error:

Cannot add or update a child row: a foreign key constraint fails.

Here is a quick description of my setup

web server (.net core 2 on centos 7) -> Ha Proxy (roundrobin mode) -> 3 MariaDb servers setup in a multi-master cluster

A simplified version of the webserver code would be:

  1. Receive request from client
  2. Insert a row in table A
  3. Insert a row in table B with foreign key to table A.

Note: Since I use .net Core (with EF) the connections to database are open and close between each insert.

Is it possible that data are not propagated to each node before the second query is fired? When I switch the haproxy to source mode (all connections from the same host will go to the same database server) I don't get this error anymore. But then I don't get the benefit of having a multi-master cluster.

How can I avoid this problem?

Thanks for the help

Solution

  • This smells like a variant on the critical read problem. This will probably solve it:

    SET SESSION wsrep_sync_wait = 7;
do your statements
SET SESSION wsrep_sync_wait = 0;