Open
Description
This is the place to report a reproducible bug, documentation error or feature request for ProxySQL.
Support questions will not be answered here. For community support, use the Google forum: https://groups.google.com/forum/#!forum/proxysql
- Note that due to time constraints ProxySQL engineers may be slow to respond to forum posts
- For information on paid support contracts: https://proxysql.com/services/support/
If you are submitting a reproducible bug report, please provide:
- [x ] A clear description of the issue
See at the end - [ x] ProxySQL version
ProxySQL version 2.1.0-495-gb712c8f3, codename Truls - [ x] OS version
Not relevant is a SQL issue - [ x] The steps to reproduce the issue
See below - [ x] The full ProxySQL error log (default location:
/var/lib/proxysql/proxysql.log)
No need see below.
If this is a crashing bug, please also include:
- The package used to install ProxySQL
- The compressed proxysql binary
- The compressed core dump (Note: if you're worried it may contain sensitive data, please contact us for information on sharing it securely: https://proxysql.com/contact-us/)
If the above information is not provided, this issue is likely to be closed.
Please use markdown to format any code or SQL: https://guides.github.com/features/mastering-markdown/
Thank you!
Issue description
Values for the READERS is modified overwriting the settings with the values for the writers.
When using writer_is_also_reader = 2 ProxySQL enter in the IF condition at line 5562 (MySQL_HostGroupManager.ccp)
if (num_backup_writers) { // there are backup writers, only these will be used as readers
q=(char *)"DELETE FROM mysql_servers_incoming WHERE hostgroup_id=%d";
query=(char *)malloc(strlen(q) + 128);
sprintf(query,q, info->reader_hostgroup);
mydb->execute(query);
free(query);
q=(char *)"INSERT OR IGNORE INTO mysql_servers_incoming (hostgroup_id,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment) SELECT %d,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment FROM mysql_servers_incoming WHERE hostgroup_id=%d";
query=(char *)malloc(strlen(q) + 128);
sprintf(query,q, info->reader_hostgroup, info->backup_writer_hostgroup);
mydb->execute(query);
free(query);
}
Now before executing this part of code we have in the mysql_server_incoming and in the mysql_servers
mysql> select * from mysql_servers_incoming order by hostgroup_id, weight desc;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| 100 | 192.168.4.22 | 3306 | 0 | 10000 | 0 | 0 | 2000 | 0 | 0 | 0 | Preferred writer |
| 100 | 192.168.4.23 | 3306 | 0 | 1000 | 1 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 100 | 192.168.4.233 | 3306 | 0 | 100 | 1 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 102 | 192.168.4.23 | 3306 | 0 | 1000 | 0 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 102 | 192.168.4.233 | 3306 | 0 | 100 | 0 | 0 | 2000 | 0 | 0 | 0 | Las chance |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
5 rows in set (0.00 sec)
mysql> select * from mysql_servers order by hostgroup_id, weight desc;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| 100 | 192.168.4.22 | 3306 | 0 | ONLINE | 10000 | 0 | 2000 | 0 | 0 | 0 | Preferred writer |
| 100 | 192.168.4.23 | 3306 | 0 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 100 | 192.168.4.233 | 3306 | 0 | ONLINE | 100 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 101 | 192.168.4.23 | 3306 | 0 | ONLINE | 10000 | 0 | 2000 | 0 | 0 | 0 | reader1 |
| 101 | 192.168.4.233 | 3306 | 0 | ONLINE | 10000 | 0 | 2000 | 0 | 0 | 0 | reader2 |
| 101 | 192.168.4.22 | 3306 | 0 | ONLINE | 100 | 0 | 2000 | 0 | 0 | 0 | last reader |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
As it is right now the INSERT action will incorrectly overwrite the WEIGHT value of the READERS with the value of the Backup_writer:
mysql> select * from mysql_servers_incoming order by hostgroup_id, weight desc;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| 100 | 192.168.4.22 | 3306 | 0 | 10000 | 0 | 0 | 2000 | 0 | 0 | 0 | Preferred writer |
| 100 | 192.168.4.23 | 3306 | 0 | 1000 | 1 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 100 | 192.168.4.233 | 3306 | 0 | 100 | 1 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 101 | 192.168.4.23 | 3306 | 0 | 1000 | 0 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 101 | 192.168.4.233 | 3306 | 0 | 100 | 0 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 102 | 192.168.4.23 | 3306 | 0 | 1000 | 0 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 102 | 192.168.4.233 | 3306 | 0 | 100 | 0 | 0 | 2000 | 0 | 0 | 0 | Las chance |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
7 rows in set (0.00 sec)
To preserve the right values but still use the conditions of writer_is_also_reader =2 just modify the above INSERT as follows:
INSERT OR IGNORE INTO mysql_servers_incoming (hostgroup_id,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment)SELECT %d,m1.hostname,m1.port,m1.gtid_port,m1.status,m1.weight,m1.compression,m1.max_connections,m1.max_replication_lag,m1.use_ssl,m1.max_latency_ms,m1.comment FROM mysql_servers m1 join mysql_servers_incoming m2 on m1.hostname=m2.hostname and m1.port=m2.port WHERE m2.hostgroup_id=%d and m1.hostgroup_id not in (select distinct hostgroup_id from mysql_servers_incoming );
This will gives you:
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
| 100 | 192.168.4.22 | 3306 | 0 | 10000 | 0 | 0 | 2000 | 0 | 0 | 0 | Preferred writer |
| 102 | 192.168.4.23 | 3306 | 0 | 1000 | 0 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 100 | 192.168.4.233 | 3306 | 0 | 100 | 1 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 100 | 192.168.4.23 | 3306 | 0 | 1000 | 1 | 0 | 2000 | 0 | 0 | 0 | Second preferred |
| 102 | 192.168.4.233 | 3306 | 0 | 100 | 0 | 0 | 2000 | 0 | 0 | 0 | Las chance |
| 101 | 192.168.4.23 | 3306 | 0 | 10000 | 0 | 0 | 2000 | 0 | 0 | 0 | reader1 |
| 101 | 192.168.4.233 | 3306 | 0 | 10000 | 0 | 0 | 2000 | 0 | 0 | 0 | reader2 |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------------+
preserving logic and settings from the source mysql_servers table;
I am testing the code and it seems working fine:
if (num_backup_writers) { // there are backup writers, only these will be used as readers
q=(char *)"DELETE FROM mysql_servers_incoming WHERE hostgroup_id=%d";
query=(char *)malloc(strlen(q) + 128);
sprintf(query,q, info->reader_hostgroup);
mydb->execute(query);
free(query);
//q=(char *)"INSERT OR IGNORE INTO mysql_servers_incoming (hostgroup_id,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment) SELECT %d,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment FROM mysql_servers_incoming WHERE hostgroup_id=%d";
q=(char *)"INSERT OR IGNORE INTO mysql_servers_incoming (hostgroup_id,hostname,port,gtid_port,status,weight,compression,max_connections,max_replication_lag,use_ssl,max_latency_ms,comment)SELECT %d,m1.hostname,m1.port,m1.gtid_port,m1.status,m1.weight,m1.compression,m1.max_connections,m1.max_replication_lag,m1.use_ssl,m1.max_latency_ms,m1.comment FROM mysql_servers m1 join mysql_servers_incoming m2 on m1.hostname=m2.hostname and m1.port=m2.port WHERE m2.hostgroup_id=%d and m1.hostgroup_id not in (select distinct hostgroup_id from mysql_servers_incoming );";
query=(char *)malloc(strlen(q) + 128);
sprintf(query,q, info->reader_hostgroup, info->backup_writer_hostgroup);
mydb->execute(query);
free(query);
}
thanks