Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

ProxySQL always checked MySQL node offline,but no reason #2003

Closed
sherlock04 opened this issue Apr 8, 2019 · 1 comment
Closed

ProxySQL always checked MySQL node offline,but no reason #2003

sherlock04 opened this issue Apr 8, 2019 · 1 comment

Comments

@sherlock04
Copy link

sherlock04 commented Apr 8, 2019 •
edited by renecannao
Loading

ProxySQL version 2.0.2-1-g533442f4, codename Truls

I used the latest mysql_galera_hostgroups table's feature to configure the Galera cluster. Everything seems normal, but when the service runs for a period of time, ProxySQL always checked MySQL node offline hard ,but no reason ,this is configuration：

MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 100.119.148.33 | 3307 | 0         | ONLINE | 10     | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 100.119.150.4  | 3307 | 0         | ONLINE | 10     | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 100.119.144.14 | 3307 | 0         | ONLINE | 10     | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

MySQL [(none)]> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 11               | 12                      | 10               | 13                | 1      | 1           | 1                     | 500                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

After I restarted the service, everything seemed normal again. but several days of operation, I could see some value increase unnormal:

+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 0       | 0          |                    |
| mysql_query_rules | 1       | 1554085008 | 0x0000000000000000 |
| mysql_servers     | 1128    | 1554517020 | 0x81DC7F2BE7F05307 |
| mysql_users       | 1       | 1554085008 | 0xCCF85BDEC7D1107E |
| mysql_variables   | 0       | 0          |                    |
| proxysql_servers  | 1       | 1554085008 | 0x0000000000000000 |

I realized that version was increased because of load or save command,but I didn't do it.

this is the running log info：

2019-04-04 01:53:08 [INFO] Dumping mysql_servers
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 11           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868910989952 |
| 12           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869018939552 |
| 12           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868968567744 |
| 10           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868953927840 |
| 10           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093376 |
| 10           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869046418720 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2019-04-04 01:53:08 [INFO] Dumping current MySQL Servers structures for hostgroup 13
HID: 13 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 13 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2019-04-04 01:53:08 [INFO] Dumping mysql_servers
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 11           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868910989952 |
| 12           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869018939552 |
| 12           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868968567744 |
| 10           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868953927840 |
| 10           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093376 |
| 10           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869046418720 |
| 13           | 100.119.144.14 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139868953928960 |
| 13           | 100.119.148.33 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093536 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2019-04-04 01:56:18 MySQL_HostGroups_Manager.cpp:3892:update_galera_set_offline(): [WARNING] Galera: setting host 100.119.144.14:3307 offline because: primary_partition=NO
2019-04-04 01:56:18 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 10 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 11 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 12 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 12 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 13 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2019-04-04 01:56:18 [INFO] Dumping mysql_servers
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 10           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868953927840 |
| 13           | 100.119.148.33 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093536 |
| 12           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868968567744 |
| 12           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869018939552 |
| 11           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868910989952 |
| 10           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093376 |
| 10           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869046418720 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2019-04-04 01:56:18 [INFO] Dumping mysql_servers_incoming
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 12           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 13           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
| 13           | 100.119.148.33 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2019-04-04 01:56:18 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+----------------+------+
| mem_pointer     | hostgroup_id | hostname       | port |
+-----------------+--------------+----------------+------+
| 139868968567744 | 12           | 100.119.144.14 | 3307 |
| 139869023093376 | 10           | 100.119.144.14 | 3307 |
+-----------------+--------------+----------------+------+
2019-04-04 01:56:18 MySQL_HostGroups_Manager.cpp:1220:commit(): [WARNING] Removed server at address 139868968567744, hostgroup 12, address 100.119.144.14 port 3307. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2019-04-04 01:56:18 MySQL_HostGroups_Manager.cpp:1220:commit(): [WARNING] Removed server at address 139869023093376, hostgroup 10, address 100.119.144.14 port 3307. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2019-04-04 01:56:18 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 13           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 0           | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2019-04-04 01:56:18 [INFO] Creating new server in HG 13 : 100.119.144.14:3307 , gtid_port=0, weight=10, status=0
2019-04-04 01:56:18 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 10 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 11 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 12 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 12 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 13 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 13 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2019-04-04 01:56:18 [INFO] Dumping mysql_servers
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| hostgroup_id | hostname       | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
| 10           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868953927840 |
| 13           | 100.119.144.14 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869018940032 |
| 13           | 100.119.148.33 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093536 |
| 12           | 100.119.144.14 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139868968567744 |
| 12           | 100.119.148.33 | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869018939552 |
| 11           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139868910989952 |
| 10           | 100.119.144.14 | 3307 | 0         | 10     | 3      | 0           | 1000            | 0                   | 0       | 0              |         | 139869023093376 |
| 10           | 100.119.150.4  | 3307 | 0         | 10     | 0      | 0           | 1000            | 0                   | 0       | 0              |         | 139869046418720 |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+
2019-04-04 01:56:23 MySQL_Monitor.cpp:1243:monitor_galera_thread(): [ERROR] Incorrect number of fields, please report a bug
2019-04-04 01:56:28 MySQL_HostGroups_Manager.cpp:4166:update_galera_set_writer(): [WARNING] Galera: setting host 100.119.144.14:3307 as writer
2019-04-04 01:56:28 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 10 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 11 , address: 100.119.150.4 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 12 , address: 100.119.148.33 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 13 , address: 100.119.144.14 , port: 3307 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2019-04-04 01:56:28 [INFO] Dumping mysql_servers
@renecannao
Copy link
Contributor

Closing, this seems a duplicate of #1978

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@renecannao @sherlock04