HAProxy PXC down

[ivomarino]

In case WP can't reach the backend DB for whatever reason it returns an HTTP 500 status code. It currently seems that HAProxy will not see such an error code as an issue and does not mark the node as down. The HAProxy section for backend is defined here: https://github.com/ttssdev/appflow/blob/master/playbooks/roles/lb/templates/etc/haproxy/haproxy-cfg.j2#L103 in the backend HAProxy checks Varnish then, like:

server compute-01 xxx.xxx.xxx.xxx:6081 cookie control01 check port 6081 inter 12000 rise 3 fall 3

Varnish should return the 500 from Apache2, we need to check this.

Better solution:

Another issue is that each node has 127.0.0.1 mysql.local as MySQL backend reference, here we should set the IP of the VIP keepalived address (port 3307), if possible, or the IP of the load balancer, this way each node will use HAProxy for MySQL queries and HAProxy always correctly knows which PXC is working and which not -- This allows us to not exclude a webserver service but just a PXC service.

[ivomarino]

Here are some config infos: http://stackoverflow.com/a/22102219

[ivomarino]

MySQL port is 3307 on the VIP:

netstat -tulpen | grep haproxy
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      0          1628382     5113/haproxy
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      0          1628380     5113/haproxy
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN      0          1628379     5113/haproxy

mysql --host=VIP --port=3307

[ivomarino]

we need to override DB_HOST (mysql.local will point to VIP in /etc/hosts):

    ...   
    config_env: true
    env_opt: |
      DB_HOST=mysql.local:3307
      CFG_URL_DEVELOPMENT='http://site.dev.foo.com'
    ...

so we basically override DB_HOST in .env:

...
DB_HOST=mysql.local
DB_HOST=mysql.local:3307
...

@ocean90 is it possible to print the actual MySQL connection info which WP is using?

[ocean90]

Something like mysqli_get_host_info()?

$ wp shell
wp> mysqli_get_host_info( $GLOBALS['wpdb']->dbh );
=> string(22) "mysql.local via TCP/IP"

[ivomarino]

works fine, thanks:

 % wp shell
wp> mysqli_get_host_info( $GLOBALS['wpdb']->dbh );
=> string(22) "mysql.local via TCP/IP"

but I need to print the port also or better the whole DB_HOST which contains the port also.

[ocean90]

To print the value of a constant you can use var_dump() like so:

$ wp eval "var_dump( DB_HOST );"
string(11) "mysql.local"

[ivomarino]

ok, this gives:

 % wp eval "var_dump( DB_HOST );"
string(11) "mysql.local"

but we would like to have:

 % wp eval "var_dump( DB_HOST );"
string(11) "mysql.local:3307"

this means that

...
DB_HOST=mysql.local
DB_HOST=mysql.local:3307
...

does not overwrite as expected, WP get's always the first entry. This means we need to edit https://github.com/ttssdev/appflow/blob/master/playbooks/roles/web/templates/wp_bedrock/env.j2#L8 but keeping in mind that this is only for e.g. staging and production, not dev for sure or situations where we only have one MySQL node.

we can also think to make DB_HOST and DB_PORT vars of a vhost definition, like:

...
DB_NAME={{ item.value.db_name }}
DB_USER={{ item.value.db_user }}
DB_PASSWORD={{ item.value.db_password }}
DB_HOST={{ item.value.db_host }}
DB_PORT={{ item.value.db_port }}
DB_PREFIX={{ item.value.db_prefix }}
...

this means also setting a default value for DB_HOST and DB_PORT if not otherwise specified in the vhost definition in order to not break existing configurations.

to make a sure test we need to take down MySQL on the node which currently has the VIP address.

[ivomarino]

For default vars we should http://stackoverflow.com/a/42109037.

[ivomarino]

with commit https://github.com/ttssdev/appflow/commit/8656fba970e380e3fc40ca51fb8baf0798508676 we've introduced db_connection_host var for vhost definitions:

    ...
    serveradmin: webmaster@foo.com
    db_name: foo_wp
    db_user: foo_wp
    db_password: RANDOM
    db_connection_host: mysql.local:3307 <- IF NOT GIVEN FALLBACK to mysql.local
    db_host: '%'
    db_prefix: foo_
    glusterfs_uploads: foo
    ...

this means all vhost definitions, where multiple nodes via PXC are involved, need to have this definition, this is not the case for hosts with a single MySQL instance like atlantis but for correctness we should define db_connection_host: mysql.local there.