search

sysown / proxysql

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

Issues with causal reads in Galera #3333

Open krzysztof-ksiazek opened 3 years ago

krzysztof-ksiazek commented 3 years ago

SET SESSION wsrep_sync_wait=1; does not seem to create a sticky connection. Subsequent SELECTs can be routed to other hosts, making impossible to enforce causal reads.

Steps to reproduce:

CREATE SCHEMA test;
CREATE TABLE `mytable` (
  `col1` varchar(20) DEFAULT NULL,
  `col2` varchar(20) DEFAULT NULL,
  `col3` varchar(20) DEFAULT NULL,
  `col4` varchar(20) DEFAULT NULL,
  `col5` varchar(20) DEFAULT NULL,
  `col6` varchar(20) DEFAULT NULL,
  `col7` varchar(20) DEFAULT NULL,
  `col8` varchar(20) DEFAULT NULL,
  `col9` varchar(20) DEFAULT NULL,
  `col10` varchar(20) DEFAULT NULL,
  `col11` varchar(20) DEFAULT NULL,
  `col12` varchar(20) DEFAULT NULL,
  `col13` varchar(20) DEFAULT NULL,
  `col14` varchar(20) DEFAULT NULL,
  `col15` varchar(20) DEFAULT NULL,
  `col16` varchar(20) DEFAULT NULL,
  `col17` varchar(20) DEFAULT NULL,
  `col18` varchar(20) DEFAULT NULL,
  `col19` varchar(20) DEFAULT NULL,
  `col20` varchar(20) DEFAULT NULL,
  `col21` varchar(20) DEFAULT NULL,
  `col22` varchar(20) DEFAULT NULL,
  `col23` varchar(20) DEFAULT NULL,
  `col24` varchar(20) DEFAULT NULL,
  `col25` varchar(20) DEFAULT NULL,
  `col26` varchar(20) DEFAULT NULL,
  `col27` varchar(20) DEFAULT NULL,
  `col28` varchar(20) DEFAULT NULL,
  `col29` varchar(20) DEFAULT NULL,
  `col30` varchar(20) DEFAULT NULL,
  `col31` varchar(20) DEFAULT NULL,
  `col32` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Install ProxySQL:

root@vagrant:~# proxysql --version
ProxySQL version 2.0.17-16-gf6d9be19, codename Truls

Configure it to:

  1. Route SELECT.mytable. to HG20 (with two readers)
  2. Route SET SESSION wsrep_sync_wait.* to HG20
  3. Route everything else to HG10 (one writer)
mysql> SELECT * FROM runtime_mysql_servers;
+--------------+------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| hostgroup_id | hostname   | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment               |
+--------------+------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 666          | 10.0.0.182 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | test reader           |
| 10           | 10.0.0.183 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | SHUNNED | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.181 | 3306 | 0         | SHUNNED | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | ONLINE  | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM runtime_mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-----------------------------+---------------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest                | match_pattern                   | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-----------------------------+---------------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 97      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | .*SELECT.*mytable.* | NULL                            | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | 0       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 98      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                        | .*SET SESSION wsrep_sync_wait.* | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | 0       | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | 1           | 0         | NULL                | NULL | 1     | NULL    |
| 300     | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL                        | .*                              | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-----------------------------+---------------------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM runtime_mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 10               | 40                      | 20               | 50                | 1      | 1           | 2                     | 100                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

On the node install php, php-mysql

Execute following script:

<?php

$user = 'sbtest';
$pass = 'sbtest';

$pdo = new PDO('mysql:host=10.0.0.184;port=6033;dbname=test', $user, $pass, [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
]);

$errors = 0;
$success = 0;
for($i = 0; $i < 2500; $i++) {

#       $pdo->beginTransaction();

    $values = [
        ':col1'                 => 'xxx',
        ':col2'            => 'xxx',
        ':col3'                 => 'xxx',
        ':col4'          => 'xxx',
        ':col5'        => 'xxx',
        ':col6'                => 'xxx,
        ':col7'           => 'xxx',
        ':col8'       => 'xxx',
        ':col9'            => 'xxx',
        ':col10'               => 'xxx',
        ':col11'       => 'xxx',
        ':col12'            => 'xxx',
        ':col13'              => 'xxx',
        ':col14'        => 'xxx',
        ':col15'           => 'xxx',
        ':col16'           => 'xxx',
        ':col17'            => 'xxx',
        ':col18'        => 'xxx',
        ':col19'            => 'xxx',
        ':col20'         => 'xxx',
        ':col21'               => 'xxx',
        ':col22'            => 'xxx',
        ':col23'         => 'xxx',
        ':col24'         => 'xxx',
        ':col25'           => 'xxx',
        ':col26'        => 'xxx',
        ':col27' => 'xxx',
        ':col28'          => 'xxx',
        ':col29'        => 'xxx',
        ':col30'        => 'xxx',
        ':col31'    => 'xxx',
        ':col32'      => 'xxx',
    ];
    $query = sprintf(<<<SQL
INSERT INTO mytable (
 `col1`,
 `col2`,
 `col3`,
 `col4`,
 `col5`,
 `col6`,
 `col7`,
 `col8`,
 `col9`,
 `col10`,
 `col11`,
 `col12`,
 `col13`,
 `col14`,
 `col15`,
 `col16`,
 `col17`,
 `col18`,
 `col19`,
 `col20`,
 `col21`,
 `col22`,
 `col23`,
 `col24`,
 `col25`,
 `col26`,
 `col27`,
 `col28`,
 `col29`,
 `col30`,
 `col31`,
 `col32`
)
 VALUES (%s)
SQL
        , implode(', ', array_keys($values))
    );

    $pdoStatement = $pdo->prepare($query);
    foreach($values as $key => $val) {
        $pdoStatement->bindValue($key, $val);
    }

    $saved = $pdoStatement->execute();
    $lastInsertId = $pdo->lastInsertId();

#    $allid = $pdo->prepare('SELECT id FROM mytable');
    $causality = $pdo->prepare('SET SESSION wsrep_sync_wait=1');
    $refetch = $pdo->prepare('SELECT * FROM mytable WHERE id = :id');
    $refetch->bindValue(':id', $lastInsertId);
#    $allid->execute();
    $causality->execute();
    $refetch->execute();
    $results = $refetch->fetch();

    if(!$results || !isset($results['id']) || (int)$results['id'] !== (int)$lastInsertId) {
#       $output = $allid->fetchAll();
#       echo sprintf("############## list of IDs: %s \n", json_encode($output));
        echo sprintf("[!!] Could not properly refetch data, result: %s, called with ID: {%s}\n", json_encode($results), $lastInsertId);
        $errors++;
    } else {
        echo sprintf("OK: loop index %d for last insert ID %d was found!!\n", $i, $lastInsertId);
        $success++;
    }
# $pdo->commit();
}

$allid = $pdo->prepare('TRUNCATE TABLE test.mytable');
$allid->execute();

echo "Looped through {$i} occurences!\n";
echo "Results:\n";
echo str_pad($success, 6) . "success\n";
echo str_pad($errors, 6) . "errors\n";

The script does:

  1. Execute INSERT to HG10
  2. Execute SET SESSION wsrep_sync_wait=1 on HG20
  3. Execute SELECT on HG20 It fails randomly with missing rows:
OK: loop index 115 for last insert ID 348 was found!!
############## list of IDs: [{"id":"3"},{"id":"6"},{"id":"9"},{"id":"12"},{"id":"15"},{"id":"18"},{"id":"21"},{"id":"24"},{"id":"27"},{"id":"30"},{"id":"33"},{"id":"36"},{"id":"39"},{"id":"42"},{"id":"45"},{"id":"48"},{"id":"51"},{"id":"54"},{"id":"57"},{"id":"60"},{"id":"63"},{"id":"66"},{"id":"69"},{"id":"72"},{"id":"75"},{"id":"78"},{"id":"81"},{"id":"84"},{"id":"87"},{"id":"90"},{"id":"93"},{"id":"96"},{"id":"99"},{"id":"102"},{"id":"105"},{"id":"108"},{"id":"111"},{"id":"114"},{"id":"117"},{"id":"120"},{"id":"123"},{"id":"126"},{"id":"129"},{"id":"132"},{"id":"135"},{"id":"138"},{"id":"141"},{"id":"144"},{"id":"147"},{"id":"150"},{"id":"153"},{"id":"156"},{"id":"159"},{"id":"162"},{"id":"165"},{"id":"168"},{"id":"171"},{"id":"174"},{"id":"177"},{"id":"180"},{"id":"183"},{"id":"186"},{"id":"189"},{"id":"192"},{"id":"195"},{"id":"198"},{"id":"201"},{"id":"204"},{"id":"207"},{"id":"210"},{"id":"213"},{"id":"216"},{"id":"219"},{"id":"222"},{"id":"225"},{"id":"228"},{"id":"231"},{"id":"234"},{"id":"237"},{"id":"240"},{"id":"243"},{"id":"246"},{"id":"249"},{"id":"252"},{"id":"255"},{"id":"258"},{"id":"261"},{"id":"264"},{"id":"267"},{"id":"270"},{"id":"273"},{"id":"276"},{"id":"279"},{"id":"282"},{"id":"285"},{"id":"288"},{"id":"291"},{"id":"294"},{"id":"297"},{"id":"300"},{"id":"303"},{"id":"306"},{"id":"309"},{"id":"312"},{"id":"315"},{"id":"318"},{"id":"321"},{"id":"324"},{"id":"327"},{"id":"330"},{"id":"333"},{"id":"336"},{"id":"339"},{"id":"342"},{"id":"345"},{"id":"348"}]
[!!] Could not properly refetch data, result: false, called with ID: {351}
OK: loop index 117 for last insert ID 354 was found!!

If I change query rules and send both SET and SELECT to HG666 where I have one node != writer, everything works ok. My assumption is that ProxySQL does not create a sticky connection when it sees SET SESSION ... and as a result causality check is enabled on node A while SELECT is send to node B, all in the same HG.

ProxySQL log:

root@vagrant:~# cat /var/lib/proxysql/proxysql.log
2021-03-04 11:11:29 [INFO] ProxySQL version 2.0.17-16-gf6d9be19
2021-03-04 11:11:29 [INFO] Detected OS: Linux vagrant 5.4.0-58-generic #64-Ubuntu SMP Wed Dec 9 08:16:25 UTC 2020 x86_64
2021-03-04 11:11:29 [INFO] ProxySQL SHA1 checksum: ceb9a345822f2e6c3b6b799eb0b8a9e48ed06966
2021-03-04 11:11:29 [INFO] Starting ProxySQL
2021-03-04 11:11:29 [INFO] Sucessfully started
2021-03-04 11:11:29 [INFO] Angel process started ProxySQL process 448312
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:11:29 ProxySQL_Admin.cpp:5549:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set not existing variable ping_interval_server with value "10000". Deleting. If the variable name is correct, this version doesn't support it
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:11:29 [INFO] ProxySQL SHA1 checksum: ceb9a345822f2e6c3b6b799eb0b8a9e48ed06966
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Jan 20 14:30:00 2021
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:11:29 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:11:29 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2021-03-04 11:11:29 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:11:29 [INFO] Creating new server in HG 20 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 20 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 20 : 10.0.0.183:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 10 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 10 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 10 : 10.0.0.183:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 666 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] New mysql_group_replication_hostgroups table
2021-03-04 11:11:29 [INFO] New mysql_galera_hostgroups table
2021-03-04 11:11:29 [INFO] Loading Galera info for (10,40,20,50,on,1,2,100,"(null)")
2021-03-04 11:11:29 [INFO] New mysql_aws_aurora_hostgroups table
2021-03-04 11:11:29 [INFO] MySQL_HostGroups_Manager::commit() locked for 3ms
2021-03-04 11:11:29 [INFO] Created new Cluster Node Entry for host 10.0.0.184:6032
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Fri Jan 22 14:08:17 2021
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:11:29 [INFO] Cluster: starting thread for peer 10.0.0.184:6032
2021-03-04 11:11:29 ProxySQL_Cluster.cpp:217:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.0.0.184:6032 . Error: Can't connect to MySQL server on '10.0.0.184' (115)
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Mon Feb  8 19:23:06 2021
2021-03-04 11:11:29 MySQL_HostGroups_Manager.cpp:4763:update_galera_set_writer(): [WARNING] Galera: setting host 10.0.0.181:3306 as writer
2021-03-04 11:11:29 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
2021-03-04 11:11:29 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140132872075456 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140132872076256 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140132872075616 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140132872075136 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:29 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:11:29 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+------------+------+
| mem_pointer     | hostgroup_id | hostname   | port |
+-----------------+--------------+------------+------+
| 140132872075616 | 20           | 10.0.0.183 | 3306 |
+-----------------+--------------+------------+------+
2021-03-04 11:11:29 MySQL_HostGroups_Manager.cpp:1285:commit(): [WARNING] Removed server at address 140132872075616, hostgroup 20, address 10.0.0.183 port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2021-03-04 11:11:29 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 140132872075456 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 140132872075936 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 140132872075776 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 140132872075136 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0               | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0               | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:11:29 [INFO] Changing comment for server 20:10.0.0.181:3306 (10.0.0.181:3306) from 'read server' to 'read and write server'
2021-03-04 11:11:29 [INFO] Changing status for server 10:10.0.0.182:3306 (10.0.0.182:3306) from 0 (0) to 1
2021-03-04 11:11:29 [INFO] Changing status for server 10:10.0.0.181:3306 (10.0.0.181:3306) from 0 (0) to 1
2021-03-04 11:11:29 [INFO] Changing comment for server 20:10.0.0.182:3306 (10.0.0.182:3306) from 'read server' to 'read and write server'
2021-03-04 11:11:29 [INFO] Creating new server in HG 40 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Creating new server in HG 40 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:11:29 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:29 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075456 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833072928 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833073088 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140132872076256 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 3      | 0   | 100       | 10      | 0   | 0       | read server           | 140132872075616 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075136 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] MySQL_HostGroups_Manager::commit() locked for 11ms
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 10
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 10
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 40
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 40
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833072928 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833073088 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 20
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 20
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075456 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075136 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 3      | 0   | 100       | 10      | 0   | 0       | read server           | 140132872075616 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 50
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 50
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2021-03-04 11:11:30 MySQL_HostGroups_Manager.cpp:4763:update_galera_set_writer(): [WARNING] Galera: setting host 10.0.0.182:3306 as writer
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075456 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833072928 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833073088 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140132872076256 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075136 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:11:30 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2021-03-04 11:11:30 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075456 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833072928 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833073088 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140132872076256 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075136 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 10
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 10
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075776 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075936 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872076096 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 40
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 40
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833072928 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132833073088 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 20
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 20
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075456 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140132872075136 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:11:30 [INFO] Dumping current MySQL Servers structures for hostgroup 50
2021-03-04 11:11:30 [INFO] Dumping mysql_servers: HG 50
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2021-03-04 11:11:30 [INFO] Latest ProxySQL version available: 2.1.0-544-g17a4b4a7
2021-03-04 11:11:30 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 10.0.0.184:6032, version 1, epoch 1614856289, checksum 0x1161A2C479216A13 . Not syncing yet ...
2021-03-04 11:11:30 [INFO] Cluster: checksum for mysql_query_rules from peer 10.0.0.184:6032 matches with local checksum 0x1161A2C479216A13 , we won't sync.
2021-03-04 11:11:30 [INFO] Cluster: detected a new checksum for mysql_servers from peer 10.0.0.184:6032, version 3, epoch 1614856290, checksum 0x4C9EE178CC035094 . Not syncing yet ...
2021-03-04 11:11:30 [INFO] Cluster: checksum for mysql_servers from peer 10.0.0.184:6032 matches with local checksum 0x4C9EE178CC035094 , we won't sync.
2021-03-04 11:11:30 [INFO] Cluster: detected a new checksum for mysql_users from peer 10.0.0.184:6032, version 1, epoch 1614856289, checksum 0x215873DC2DEC0DBC . Not syncing yet ...
2021-03-04 11:11:30 [INFO] Cluster: checksum for mysql_users from peer 10.0.0.184:6032 matches with local checksum 0x215873DC2DEC0DBC , we won't sync.
2021-03-04 11:11:30 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 10.0.0.184:6032, version 1, epoch 1614856289, checksum 0x7BD5647855D5D400 . Not syncing yet ...
2021-03-04 11:11:30 [INFO] Cluster: checksum for proxysql_servers from peer 10.0.0.184:6032 matches with local checksum 0x7BD5647855D5D400 , we won't sync.
2021-03-04 11:12:52 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2021-03-04 11:12:52 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command
2021-03-04 11:12:52 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 10.0.0.184:6032, version 2, epoch 1614856372, checksum 0x130F3FE21D32C030 . Not syncing yet ...
2021-03-04 11:12:52 [INFO] Cluster: checksum for mysql_query_rules from peer 10.0.0.184:6032 matches with local checksum 0x130F3FE21D32C030 , we won't sync.
2021-03-04 11:12:57 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2021-03-04 11:12:57 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command
2021-03-04 11:12:57 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 10.0.0.184:6032, version 3, epoch 1614856377, checksum 0x63BC01BE58D73FC0 . Not syncing yet ...
2021-03-04 11:12:57 [INFO] Cluster: checksum for mysql_query_rules from peer 10.0.0.184:6032 matches with local checksum 0x63BC01BE58D73FC0 , we won't sync.
2021-03-04 11:13:40 MySQL_Thread.cpp:4439:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.0.184:50980
2021-03-04 11:13:40 MySQL_Session.cpp:135:kill_query_thread(): [WARNING] KILL CONNECTION 6764 on 10.0.0.182:3306
2021-03-04 11:13:43 MySQL_Thread.cpp:4439:process_all_sessions(): [WARNING] Closing unhealthy client connection 10.0.0.184:51044
2021-03-04 11:13:43 MySQL_Session.cpp:135:kill_query_thread(): [WARNING] KILL CONNECTION 6757 on 10.0.0.183:3306
2021-03-04 11:15:46 ProxySQL_GloVars.cpp:9:term_handler(): [WARNING] Received TERM signal: shutdown in progress...
2021-03-04 11:15:46 [INFO] Starting shutdown...
2021-03-04 11:15:47 [INFO] Shutdown completed!
2021-03-04 11:15:47 [INFO] Exiting...
2021-03-04 11:15:47 [INFO] Shutdown angel process
2021-03-04 11:15:47 [INFO] ProxySQL version 2.0.17-16-gf6d9be19
2021-03-04 11:15:47 [INFO] Detected OS: Linux vagrant 5.4.0-58-generic #64-Ubuntu SMP Wed Dec 9 08:16:25 UTC 2020 x86_64
2021-03-04 11:15:47 [INFO] ProxySQL SHA1 checksum: ceb9a345822f2e6c3b6b799eb0b8a9e48ed06966
2021-03-04 11:15:47 [INFO] Starting ProxySQL
2021-03-04 11:15:47 [INFO] Sucessfully started
2021-03-04 11:15:47 [INFO] Angel process started ProxySQL process 456248
Standard ProxySQL MySQL Logger rev. 2.0.0714 -- MySQL_Logger.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL Cluster rev. 0.4.0906 -- ProxySQL_Cluster.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Wed Jan 20 14:30:00 2021
Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:15:47 ProxySQL_Admin.cpp:5549:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set not existing variable ping_interval_server with value "10000". Deleting. If the variable name is correct, this version doesn't support it
Standard ProxySQL Admin rev. 2.0.6.0805 -- ProxySQL_Admin.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:15:47 [INFO] ProxySQL SHA1 checksum: ceb9a345822f2e6c3b6b799eb0b8a9e48ed06966
Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Wed Jan 20 14:30:00 2021
Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:15:47 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:15:47 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2021-03-04 11:15:47 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 20           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           | 0           | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:15:47 [INFO] Creating new server in HG 20 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 20 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 20 : 10.0.0.183:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 10 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 10 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 10 : 10.0.0.183:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 666 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] New mysql_group_replication_hostgroups table
2021-03-04 11:15:47 [INFO] New mysql_galera_hostgroups table
2021-03-04 11:15:47 [INFO] Loading Galera info for (10,40,20,50,on,1,2,100,"(null)")
2021-03-04 11:15:47 [INFO] New mysql_aws_aurora_hostgroups table
2021-03-04 11:15:47 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2021-03-04 11:15:47 [INFO] Created new Cluster Node Entry for host 10.0.0.184:6032
Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Fri Jan 22 14:08:17 2021
In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Wed Jan 20 14:30:00 2021
2021-03-04 11:15:47 [INFO] Cluster: starting thread for peer 10.0.0.184:6032
2021-03-04 11:15:47 ProxySQL_Cluster.cpp:217:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.0.0.184:6032 . Error: Can't connect to MySQL server on '10.0.0.184' (115)
Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Mon Feb  8 19:23:06 2021
2021-03-04 11:15:47 MySQL_HostGroups_Manager.cpp:4763:update_galera_set_writer(): [WARNING] Galera: setting host 10.0.0.181:3306 as writer
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140607625345216 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140607625346016 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140607625345376 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read server           | 140607625344896 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:15:47 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+------------+------+
| mem_pointer     | hostgroup_id | hostname   | port |
+-----------------+--------------+------------+------+
| 140607625345376 | 20           | 10.0.0.183 | 3306 |
+-----------------+--------------+------------+------+
2021-03-04 11:15:47 MySQL_HostGroups_Manager.cpp:1285:commit(): [WARNING] Removed server at address 140607625345376, hostgroup 20, address 10.0.0.183 port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2021-03-04 11:15:47 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 140607625345216 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 140607625345696 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 140607625345536 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read server           | 140607625344896 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0               | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server | 0               | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+-----------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:15:47 [INFO] Changing comment for server 20:10.0.0.181:3306 (10.0.0.181:3306) from 'read server' to 'read and write server'
2021-03-04 11:15:47 [INFO] Changing status for server 10:10.0.0.182:3306 (10.0.0.182:3306) from 0 (0) to 1
2021-03-04 11:15:47 [INFO] Changing status for server 10:10.0.0.181:3306 (10.0.0.181:3306) from 0 (0) to 1
2021-03-04 11:15:47 [INFO] Changing comment for server 20:10.0.0.182:3306 (10.0.0.182:3306) from 'read server' to 'read and write server'
2021-03-04 11:15:47 [INFO] Creating new server in HG 40 : 10.0.0.182:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Creating new server in HG 40 : 10.0.0.181:3306 , gtid_port=0, weight=1, status=0
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345216 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342688 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342848 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140607625346016 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 3      | 0   | 100       | 10      | 0   | 0       | read server           | 140607625345376 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625344896 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 10
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 10
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 40
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 40
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342688 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342848 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 20
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 20
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345216 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625344896 |
| 20  | 10.0.0.183 | 3306 | 0    | 1      | 3      | 0   | 100       | 10      | 0   | 0       | read server           | 140607625345376 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 50
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 50
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2021-03-04 11:15:47 MySQL_HostGroups_Manager.cpp:4763:update_galera_set_writer(): [WARNING] Galera: setting host 10.0.0.182:3306 as writer
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345216 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342688 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342848 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140607625346016 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625344896 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [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               |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 666          | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | test reader           |
| 10           | 10.0.0.181 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.183 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 10           | 10.0.0.182 | 3306 | 0         | 1      | 1      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 40           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.181 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
| 20           | 10.0.0.182 | 3306 | 0         | 1      | 0      | 0           | 100             | 10                  | 0       | 0              | read and write server |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
2021-03-04 11:15:47 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2021-03-04 11:15:47 [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 |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 666 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: test reader
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: ALL
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345216 |
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342688 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342848 |
| 666 | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | test reader           | 140607625346016 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625344896 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 10
HID: 10 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: SHUNNED , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 10 , address: 10.0.0.183 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 10
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 10  | 10.0.0.181 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345536 |
| 10  | 10.0.0.182 | 3306 | 0    | 1      | 1      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345696 |
| 10  | 10.0.0.183 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345856 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 40
HID: 40 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 40 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 40
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 40  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342688 |
| 40  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607586342848 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 20
HID: 20 , address: 10.0.0.181 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
HID: 20 , address: 10.0.0.182 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 100 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment: read and write server
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 20
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| hid | hostname   | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment               | mem_pointer     |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
| 20  | 10.0.0.181 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625345216 |
| 20  | 10.0.0.182 | 3306 | 0    | 1      | 0      | 0   | 100       | 10      | 0   | 0       | read and write server | 140607625344896 |
+-----+------------+------+------+--------+--------+-----+-----------+---------+-----+---------+-----------------------+-----------------+
2021-03-04 11:15:47 [INFO] Dumping current MySQL Servers structures for hostgroup 50
2021-03-04 11:15:47 [INFO] Dumping mysql_servers: HG 50
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2021-03-04 11:15:48 [INFO] Latest ProxySQL version available: 2.1.0-544-g17a4b4a7
2021-03-04 11:15:48 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer 10.0.0.184:6032, version 1, epoch 1614856547, checksum 0x63BC01BE58D73FC0 . Not syncing yet ...
2021-03-04 11:15:48 [INFO] Cluster: checksum for mysql_query_rules from peer 10.0.0.184:6032 matches with local checksum 0x63BC01BE58D73FC0 , we won't sync.
2021-03-04 11:15:48 [INFO] Cluster: detected a new checksum for mysql_servers from peer 10.0.0.184:6032, version 3, epoch 1614856547, checksum 0x4C9EE178CC035094 . Not syncing yet ...
2021-03-04 11:15:48 [INFO] Cluster: checksum for mysql_servers from peer 10.0.0.184:6032 matches with local checksum 0x4C9EE178CC035094 , we won't sync.
2021-03-04 11:15:48 [INFO] Cluster: detected a new checksum for mysql_users from peer 10.0.0.184:6032, version 1, epoch 1614856547, checksum 0x215873DC2DEC0DBC . Not syncing yet ...
2021-03-04 11:15:48 [INFO] Cluster: checksum for mysql_users from peer 10.0.0.184:6032 matches with local checksum 0x215873DC2DEC0DBC , we won't sync.
2021-03-04 11:15:48 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 10.0.0.184:6032, version 1, epoch 1614856547, checksum 0x7BD5647855D5D400 . Not syncing yet ...
2021-03-04 11:15:48 [INFO] Cluster: checksum for proxysql_servers from peer 10.0.0.184:6032 matches with local checksum 0x7BD5647855D5D400 , we won't sync.
krzysztof-ksiazek commented 3 years ago

Actually, it's even more messed up than I thought.

  1. Restart ProxySQL, use HG20 with two nodes.
  2. Run the script with SET SESSION wsrep_sync_wait=1
  3. All is good.
  4. Disable causality checks by commenting: $causality->execute();
  5. Run the script - errors show up, as expected
  6. Uncomment $causality->execute(); enabling causality checks again
  7. Run the script - errors still show up
  8. Restart ProxySQL
  9. Run the script with causality checks still enabled: now it is back to all green.
krzysztof-ksiazek commented 3 years ago

Not sure if it makes difference, I tested it on Ubuntu 20.04, 3 nodes of MariaDB 10.4.18 Galera Cluster.