ProxySQL 2 rules not working?

[Lex LcS]

Hi to all, 

i have a setup with ProxySQL 2 and 3 Galera cluster nodes on CentOS 7. 

The goal here is to only write on one galera node and read from all 3. At first i managed to achieve this however a friend of mine tried to replicate it and it failed. Tried to fix it by deleting the rules that my friend added and add the ones that worked for me without success. 

Here is what i did: 

DELETE FROM mysql_servers;

DELETE FROM scheduler;

DELETE FROM mysql_query_rules;

INSERT INTO mysql_servers (hostgroup_id, hostname, port, comment) VALUES (10, '10.9.8.155', 3306, 'write'), (10, '10.9.8.156', 3306, 'write'), (10, '10.9.8.157', 3306, 'write');

INSERT INTO mysql_servers (hostgroup_id, hostname, port, comment) VALUES (20, '10.9.8.155', 3306, 'read'), (20, '10.9.8.156', 3306, 'read'), (20, '10.9.8.157', 3306, 'read');

INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 10, 1);

INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 20, 1);

SET mysql-query_retries_on_failure=10;

INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 1000, '/usr/share/proxysql/tools/proxysql_galera_checker.sh', 10, 20, 1, 1, '/var/lib/proxysql/proxysql.log');

LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;

The result:

SELECT hostgroup_id, hostname, comment, status FROM mysql_servers;

+--------------+------------+---------+--------------+

| hostgroup_id | hostname   | comment | status       |

+--------------+------------+---------+--------------+

| 10           | 10.9.8.155 | write   | ONLINE       |

| 10           | 10.9.8.156 | write   | ONLINE       |

| 10           | 10.9.8.157 | write   | ONLINE       |

| 20           | 10.9.8.155 | read    | ONLINE       |

| 20           | 10.9.8.156 | read    | ONLINE       |

| 20           | 10.9.8.157 | read    | ONLINE       |

+--------------+------------+---------+--------------+

Problem is that we expect another result:

+--------------+------------+---------+--------------+

| hostgroup_id | hostname   | comment | status       |

+--------------+------------+---------+--------------+

| 10           | 10.9.8.155 | write   | ONLINE       |

| 10           | 10.9.8.156 | write   | OFFLINE_SOFT |

| 10           | 10.9.8.157 | write   | OFFLINE_SOFT |

| 20           | 10.9.8.155 | read    | ONLINE       |

| 20           | 10.9.8.156 | read    | ONLINE       |

| 20           | 10.9.8.157 | read    | ONLINE       |

+--------------+------------+---------+--------------+

The strangest part is that on a fresh setup all this works, i am for sure missing something but can't understand what. Any help will be appreciated

[Lex LcS]

SOLVED

Problem was that log rotate created the proxysql.log file under root user instead of proxysql so the proxysql server could not write to the log file and from here the rules problem. Once i fixed the file chown to proxysql all started to work as it should!