search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.99k stars 974 forks source link

Down mysql server is shown as online in ProxySQL #1156

Open tapuhi opened 7 years ago

tapuhi commented 7 years ago 
mysql> select * from mysql_replication_hostgroups where writer_hostgroup=251;
+------------------+------------------+------------------+
| writer_hostgroup | reader_hostgroup | comment          |
+------------------+------------------+------------------+
| 251              | 1251             | mysql_mysql_test |
+------------------+------------------+------------------+
1 row in set (0.00 sec)

mysql> select * from mysql_servers where hostgroup_id in (251,1251);
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 251          | db-mysql-test0a| 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 251          | db-mysql-test0a | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test0a | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)

mysql>

Taking Down db-mysql-test0a : 

root@db-mysql-test0a:~ # ps -ef | grep mysqld
mysql    23049     1  4 08:53 ?        00:00:00 /usr/sbin/mysqld --defaults-file=/etc/mysql/my_mysql_test.cnf
root     23286  8922  0 08:54 pts/0    00:00:00 grep --color=auto mysqld
root@db-mysql-test0a:~ # systemctl stop mysql_mysql_test
ps -ef | grep mysqld
root@db-mysql-test0a:~ # ps -ef | grep mysqld
root     24180  8922  0 09:01 pts/0    00:00:00 grep --color=auto mysqld
root@db-mysql-test0a:~ #

Now in ProxySql db-mysql-test0a moved to the reader group and is still online:

mysql> select * from mysql_servers where hostgroup_id in (251,1251);
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 251          | db-mysql-test0a | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test0a | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test0a | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1251         | db-mysql-test1b | 3306 | ONLINE | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.00 sec)

mysql>

In the monitor table mysql_server_connect_log we can see he is unable to connect:

mysql> select * from mysql_server_connect_log where hostname='db-mysql-test0a';
+--------------------------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| hostname                       | port | time_start_us    | connect_success_time_us | connect_error                                                           |
+--------------------------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| db-mysql-test0a | 3306 | 1504169641065850 | 1285                    | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504169700869384 | 836                     | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504169799909607 | 894                     | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504169839271504 | 960                     | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504169932021029 | 2219                    | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504169970634326 | 1865                    | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504170011419434 | 1345                    | NULL                                                                    |
| db-mysql-test0a | 3306 | 1504170092984014 | 0                       | Can't connect to MySQL server on 'db-mysql-test0a' (107) |
| db-mysql-test0a | 3306 | 1504170135527828 | 0                       | Can't connect to MySQL server on 'db-mysql-test0a' (107) |
+--------------------------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql>
renecannao commented 7 years ago

Hi Avi. This is a perpetual source of confusion :) mysql_servers table is a configuration table. If a server is marked as ONLINE, i means that is configured to be online, not that this is its current status. mysql_server_connect_log , mysql_server_ping_log , stats_mysql_connection_pool are the place you should look for to know the current status, while mysql_servers is the configured status

renecannao commented 7 years ago

Note to myself: add this in the FAQ

tapuhi commented 7 years ago

missed your answer:-)

mysql_servers table is a configuration table.

That's the basic , Don't know how I got confused :-)

Thank anyway

tapuhi commented 6 years ago

Closed by mistake