change MYSQL USER default_hostgroup not effect immediately

[FelixChong]

Hi,

I found that after changing MYSQL USERS default_hostgroup, it's not effected immediately. I am not sure if it's a bug or incorrectly config. Below are the snapshots of my case.

1, By default, I have two server and one user (here the user default hostgroup is pointing to 0)

Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root     |          | 1      | 0       | 0                 | qing           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

Admin> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 127.0.0.1    | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 10.20.188.92 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

2, I run some tests and found the queries routed to hostgroup 0 as default correctly.

Admin> select * from stats_mysql_connection_pool;
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host     | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 127.0.0.1    | 3306     | ONLINE | 20       | 0        | 0      | 0       | 20638   | 804882          | 0               | 0          |
| 1         | 10.20.188.92 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 0          |
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
2 rows in set (0.01 sec)

3, I updated user's default hostgroup to 1 and load to runtime

Admin> update mysql_users set default_hostgroup = 1;
Query OK, 1 row affected (0.00 sec)

Admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

4, I found the queries are still routed to hostgroup 0 with those used connections.

Admin> select * from stats_mysql_connection_pool;
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host     | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 127.0.0.1    | 3306     | ONLINE | 20       | 0        | 20     | 0       | 536671  | 20929929        | 240             | 0          |
| 1         | 10.20.188.92 | 3306     | ONLINE | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 0          |
+-----------+--------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)

Sincerely hope someone can help to answer this. Thanks!

[tapuhi]

Hi @FelixChong , the new configuration for default hostgroup will only affect newly connected sessions, changing default hostgroup is not supposed to disconnect existing sessions from any host. Create some new connections with new mysql_users configuration and see if you're routed to hostgroup 1.

I hope I understood the use case correctly.

[FelixChong]

Hi @tapuhi , you are right, the new connections will route to hg 1 correctly.

But my use case want to affect those old connections immediately, may I know if any I can do to achieve this?

Thanks.

[renecannao]

@FelixChong : you can create new a new rule in mysql_query_rules, matching the specific user an setting the correct destination_hostgroup

[tapuhi]

I was thinking of that but then he will have to maintain those query rules for each user default group change. Would be nice if it was possible to move to new default historiography by draining the connections of a specific user and reconnect to the new host group , by waiting for those connections to be idle for example.

[renecannao]

@tapuhi , as you pointed already the new setting applies only to new connections. This because the Authentication module is queries only during the creation of new connections. Constantly checking if default_hostgroup has changed will have a performance impact. But perhaps is possible to trigger a further check only if LOAD MYSQL USERS TO RUNTIME was executed: therefore I will leave this issue open.

One further note: it is possible to terminate client connections running KILL CONNECTION in Admin.

[FelixChong]

@tapuhi @renecannao Thanks for your guys answer