Last week we ran into an interesting problem with our MySQL master/slave replication. During the week (when we’re working) the propagation delay from master to slave is less than a second but for some reason on Saturdays the delay jumps up for several minutes (still trying to figure this one out). This caused a problem where multiple records would try to be inserted for the same data (read fails to find the new row which causes it to assume there is a need for a new record).

To get around this issue we used the instructions on https://blog.engineyard.com/2014/advanced-read-write-splitting-with-phps-mysqlnd to switch to using the eventual_consistency filter in our mysqlnd_ms config:

{
    "application": {
        "master": {
            "master_0": {
                "host": "127.0.0.1",
                "port": "3306",
                "user": "user",
                "password": "pass",
                "db": "application"
            }
        },
        "slave": {
            "slave_0": {
                "host": "127.0.0.1",
                "port": "3307",
                "user": "user",
                "password": "pass",
                "db": "application"
            }
        },
        "filters": {
            "quality_of_service": {
                "eventual_consistency": {
                    "age": 1
                }
            }
        },
        "master_on_write": 1,
        "server_charset" : "utf8"
    }
}

After a service apache2 restart we received the following error message:

Error while building page SQLSTATE[HY000] [2000] (mysqlnd_ms) Error in configuration. Last filter is multi filter. Needs to be non-multi one. Stopping

This is one of those horrible error messages where a Google Search returns the source code with the error in it as one of the few results.

This is what I learned while trying to debug this problem.

The filters in the mysqlnd config are either multi, which means they return multiple possible servers, or non-multi, which means then return a single value. The quality_of_service filter is a multi filter which returns any records that are less than age (from the config) seconds_behind_master (from show slave).

In order to fix this error the last filter must be a non-multi filter. In our case we added the roundrobin filter.

{
    "application": {
        "master": {...},
        "slave": {...},
        "filters": {
            "quality_of_service": {
                "eventual_consistency": {
                    "age": 1
                }
            },
            "roundrobin" : {
            
            }
        },
        "master_on_write": 1,
        "server_charset" : "utf8"
    }
}

Hopefully, other people will find this helpful.