ProxySQL changing charset variables

[garutilorenzo]

We have an unexpected behavior with ProxySQL.
It seems that ProxySQL in changing the charset on new connection/threads.

Some data about our environment:

OS: CentOS Linux release 7.3.1611 (Core)
Kernel: Linux blade10 3.10.0-693.11.1.el7.x86_64 SMP Mon Dec 4 23:52:40 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Python Version: Python 2.7.14
Python Mysql Library: MySQL-python (1.2.3rc1)
ProxySQL Version: ProxySQL version 2.0.6-73-gc746bf7, codename Truls
Mysql Version: 5.7.26: mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper

ProxySQL variables:

mysql> show variables;
+-----------------------------------------------------+--------------------------------------------------------+
| Variable_name                                       | Value                                                  |
+-----------------------------------------------------+--------------------------------------------------------+
| admin-admin_credentials                             | admin:proxysql                                         |
| admin-checksum_mysql_query_rules                    | true                                                   |
| admin-checksum_mysql_servers                        | true                                                   |
| admin-checksum_mysql_users                          | true                                                   |
| admin-cluster_check_interval_ms                     | 1000                                                   |
| admin-cluster_check_status_frequency                | 10                                                     |
| admin-cluster_mysql_query_rules_diffs_before_sync   | 3                                                      |
| admin-cluster_mysql_query_rules_save_to_disk        | true                                                   |
| admin-cluster_mysql_servers_diffs_before_sync       | 3                                                      |
| admin-cluster_mysql_servers_save_to_disk            | true                                                   |
| admin-cluster_mysql_users_diffs_before_sync         | 3                                                      |
| admin-cluster_mysql_users_save_to_disk              | true                                                   |
| admin-cluster_password                              |                                                        |
| admin-cluster_proxysql_servers_diffs_before_sync    | 3                                                      |
| admin-cluster_proxysql_servers_save_to_disk         | true                                                   |
| admin-cluster_username                              |                                                        |
| admin-hash_passwords                                | true                                                   |
| admin-mysql_ifaces                                  | 0.0.0.0:6032                                           |
| admin-read_only                                     | false                                                  |
| admin-refresh_interval                              | 2000                                                   |
| admin-stats_credentials                             | stats:stats                                            |
| admin-stats_mysql_connection_pool                   | 60                                                     |
| admin-stats_mysql_connections                       | 60                                                     |
| admin-stats_mysql_query_cache                       | 60                                                     |
| admin-stats_system_cpu                              | 60                                                     |
| admin-stats_system_memory                           | 60                                                     |
| admin-telnet_admin_ifaces                           | (null)                                                 |
| admin-telnet_stats_ifaces                           | (null)                                                 |
| admin-vacuum_stats                                  | true                                                   |
| admin-version                                       | 2.0.6-73-gc746bf7                                      |
| admin-web_enabled                                   | false                                                  |
| admin-web_port                                      | 6080                                                   |
| mysql-add_ldap_user_comment                         |                                                        |
| mysql-auditlog_filename                             |                                                        |
| mysql-auditlog_filesize                             | 104857600                                              |
| mysql-auto_increment_delay_multiplex                | 5                                                      |
| mysql-autocommit_false_is_transaction               | false                                                  |
| mysql-autocommit_false_not_reusable                 | false                                                  |
| mysql-binlog_reader_connect_retry_msec              | 3000                                                   |
| mysql-client_found_rows                             | true                                                   |
| mysql-client_multi_statements                       | true                                                   |
| mysql-client_session_track_gtid                     | true                                                   |
| mysql-commands_stats                                | true                                                   |
| mysql-connect_retries_delay                         | 1                                                      |
| mysql-connect_retries_on_failure                    | 10                                                     |
| mysql-connect_timeout_server                        | 1000                                                   |
| mysql-connect_timeout_server_max                    | 10000                                                  |
| mysql-connection_delay_multiplex_ms                 | 0                                                      |
| mysql-connection_max_age_ms                         | 0                                                      |
| mysql-connpoll_reset_queue_length                   | 50                                                     |
| mysql-default_charset                               | utf8                                                   |
| mysql-default_max_latency_ms                        | 1000                                                   |
| mysql-default_query_delay                           | 0                                                      |
| mysql-default_query_timeout                         | 36000000                                               |
| mysql-default_reconnect                             | true                                                   |
| mysql-default_schema                                | information_schema                                     |
| mysql-default_sql_mode                              |                                                        |
| mysql-default_time_zone                             | SYSTEM                                                 |
| mysql-enforce_autocommit_on_reads                   | false                                                  |
| mysql-eventslog_default_log                         | 0                                                      |
| mysql-eventslog_filename                            |                                                        |
| mysql-eventslog_filesize                            | 104857600                                              |
| mysql-eventslog_format                              | 1                                                      |
| mysql-forward_autocommit                            | false                                                  |
| mysql-free_connections_pct                          | 10                                                     |
| mysql-have_compress                                 | true                                                   |
| mysql-have_ssl                                      | false                                                  |
| mysql-hostgroup_manager_verbose                     | 1                                                      |
| mysql-init_connect                                  |                                                        |
| mysql-interfaces                                    | 0.0.0.0:3306;0.0.0.0:3307;0.0.0.0:3308;/tmp/mysql.sock |
| mysql-keep_multiplexing_variables                   | tx_isolation,version                                   |
| mysql-kill_backend_connection_when_disconnect       | true                                                   |
| mysql-ldap_user_variable                            |                                                        |
| mysql-long_query_time                               | 1000                                                   |
| mysql-max_allowed_packet                            | 33554432                                               |
| mysql-max_connections                               | 2048                                                   |
| mysql-max_stmts_cache                               | 10000                                                  |
| mysql-max_stmts_per_connection                      | 20                                                     |
| mysql-max_transaction_time                          | 14400000                                               |
| mysql-min_num_servers_lantency_awareness            | 1000                                                   |
| mysql-mirror_max_concurrency                        | 16                                                     |
| mysql-mirror_max_queue_length                       | 32000                                                  |
| mysql-monitor_connect_interval                      | 60000                                                  |
| mysql-monitor_connect_timeout                       | 200                                                    |
| mysql-monitor_enabled                               | true                                                   |
| mysql-monitor_galera_healthcheck_interval           | 5000                                                   |
| mysql-monitor_galera_healthcheck_max_timeout_count  | 3                                                      |
| mysql-monitor_galera_healthcheck_timeout            | 800                                                    |
| mysql-monitor_groupreplication_healthcheck_interval | 5000                                                   |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800                                                    |
| mysql-monitor_history                               | 600000                                                 |
| mysql-monitor_password                              | xxxxxxxx                                               |
| mysql-monitor_ping_interval                         | 2000                                                   |
| mysql-monitor_ping_max_failures                     | 1                                                      |
| mysql-monitor_ping_timeout                          | 750                                                    |
| mysql-monitor_query_interval                        | 60000                                                  |
| mysql-monitor_query_timeout                         | 100                                                    |
| mysql-monitor_read_only_interval                    | 1000                                                   |
| mysql-monitor_read_only_max_timeout_count           | 3                                                      |
| mysql-monitor_read_only_timeout                     | 800                                                    |
| mysql-monitor_replication_lag_interval              | 2000                                                   |
| mysql-monitor_replication_lag_timeout               | 1000                                                   |
| mysql-monitor_replication_lag_use_percona_heartbeat |                                                        |
| mysql-monitor_slave_lag_when_null                   | 60                                                     |
| mysql-monitor_threads_max                           | 128                                                    |
| mysql-monitor_threads_min                           | 8                                                      |
| mysql-monitor_threads_queue_maxsize                 | 128                                                    |
| mysql-monitor_username                              | xxxxxxxx                                               |
| mysql-monitor_wait_timeout                          | true                                                   |
| mysql-monitor_writer_is_also_reader                 | true                                                   |
| mysql-multiplexing                                  | true                                                   |
| mysql-ping_interval_server_msec                     | 2000                                                   |
| mysql-ping_timeout_server                           | 200                                                    |
| mysql-poll_timeout                                  | 2000                                                   |
| mysql-poll_timeout_on_failure                       | 100                                                    |
| mysql-query_cache_size_MB                           | 1024                                                   |
| mysql-query_cache_stores_empty_result               | true                                                   |
| mysql-query_digests                                 | true                                                   |
| mysql-query_digests_lowercase                       | false                                                  |
| mysql-query_digests_max_digest_length               | 2048                                                   |
| mysql-query_digests_max_query_length                | 65000                                                  |
| mysql-query_digests_normalize_digest_text           | false                                                  |
| mysql-query_digests_replace_null                    | false                                                  |
| mysql-query_digests_track_hostname                  | false                                                  |
| mysql-query_processor_iterations                    | 0                                                      |
| mysql-query_processor_regex                         | 1                                                      |
| mysql-query_retries_on_failure                      | 1                                                      |
| mysql-reset_connection_algorithm                    | 2                                                      |
| mysql-server_capabilities                           | 569867                                                 |
| mysql-server_version                                | 5.7.26                                                 |
| mysql-servers_stats                                 | true                                                   |
| mysql-session_idle_ms                               | 1000                                                   |
| mysql-session_idle_show_processlist                 | true                                                   |
| mysql-sessions_sort                                 | true                                                   |
| mysql-set_query_lock_on_hostgroup                   | 1                                                      |
| mysql-show_processlist_extended                     | 0                                                      |
| mysql-shun_on_failures                              | 1                                                      |
| mysql-shun_recovery_time_sec                        | 10                                                     |
| mysql-ssl_p2s_ca                                    |                                                        |
| mysql-ssl_p2s_cert                                  |                                                        |
| mysql-ssl_p2s_cipher                                |                                                        |
| mysql-ssl_p2s_key                                   |                                                        |
| mysql-stacksize                                     | 1048576                                                |
| mysql-stats_time_backend_query                      | false                                                  |
| mysql-stats_time_query_processor                    | false                                                  |
| mysql-threads                                       | 24                                                     |
| mysql-threshold_query_length                        | 524288                                                 |
| mysql-threshold_resultset_size                      | 4194304                                                |
| mysql-throttle_connections_per_sec_to_hostgroup     | 1000000                                                |
| mysql-throttle_max_bytes_per_second_to_client       | 0                                                      |
| mysql-throttle_ratio_server_to_client               | 0                                                      |
| mysql-verbose_query_error                           | false                                                  |
| mysql-wait_timeout                                  | 28800000                                               |
+-----------------------------------------------------+--------------------------------------------------------+

ProxySQL mysql servers:

+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname        | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment      |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 10           | master | 3306 | 0         | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |              |
| 20           | master | 3306 | 0         | ONLINE | 1      | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave1  | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave2 | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 20           | slave3  | 3306 | 0         | ONLINE | 50     | 0           | 100             | 1                   | 0       | 0              |              |
| 30           | slave1  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
| 30           | slave2 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
| 30           | slave3  | 3306 | 0         | ONLINE | 1      | 0           | 100             | 3600                | 0       | 0              | slow replica |
+--------------+-----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+

ProxySQL query rules:

mysql> select * from 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 |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 50      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | 3307       | NULL   | NULL         | NULL          | 0                    | CASELESS     | NULL    | NULL            | 30                    | 4000      | 1                  | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 100     | 1      | NULL     | NULL       | 0      | NULL        | NULL       | 3308       | NULL   | NULL         | NULL          | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | 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    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+

MySQL (master) charset variables:

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Our test results:

First run:

[user@host  ~]$ python /tmp/test.py 
({'Value': 'utf8', 'Variable_name': 'character_set_client'},
 {'Value': 'utf8', 'Variable_name': 'character_set_connection'},
 {'Value': 'latin1', 'Variable_name': 'character_set_database'},
 {'Value': 'binary', 'Variable_name': 'character_set_filesystem'},
 {'Value': 'utf8', 'Variable_name': 'character_set_results'},
 {'Value': 'latin1', 'Variable_name': 'character_set_server'},
 {'Value': 'utf8', 'Variable_name': 'character_set_system'},
 {'Value': '/usr/share/mysql/charsets/',
  'Variable_name': 'character_sets_dir'})

Second run:

[user@host ~]$ python /tmp/test.py
({'Value': 'latin1', 'Variable_name': 'character_set_client'},
 {'Value': 'latin1', 'Variable_name': 'character_set_connection'},
 {'Value': 'latin1', 'Variable_name': 'character_set_database'},
 {'Value': 'binary', 'Variable_name': 'character_set_filesystem'},
 {'Value': 'latin1', 'Variable_name': 'character_set_results'},
 {'Value': 'latin1', 'Variable_name': 'character_set_server'},
 {'Value': 'utf8', 'Variable_name': 'character_set_system'},
 {'Value': '/usr/share/mysql/charsets/',
  'Variable_name': 'character_sets_dir'})

Our test statement:

sql = """show variables like '%char%'"""

All queries we send are send to ProxySQL port 3306, host group id 10 (master)

[garutilorenzo]

Some additional information:

Database charset (MySQL mater):

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA 
    -> WHERE schema_name = "database";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1                     |
+----------------------------+
1 row in set (0.00 sec)

mysql> 

[ernstae]

Related to character sets.... i read that the default character set in ProxySQL was utf8mb4, however, in my review of the settings, I'm seeing utf8 in my installation of ProxySQL.

https://github.com/sysown/proxysql/wiki/Global-variables#mysql-default_charset

Is the documentation incorrect?

[garutilorenzo]

This is my mysql_variable section of proxysql.cnf

mysql_variables=
{
        threads=24
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
	poll_timeout=2000
        poll_timeout_on_failure=100
        interfaces="0.0.0.0:3306;0.0.0.0:3307;0.0.0.0:3308;/tmp/mysql.sock"
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.7.26"
        connect_timeout_server=1000
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_connect_timeout=200
        monitor_ping_interval=2000
        monitor_ping_timeout=750
        monitor_ping_max_failures=1
        monitor_replication_lag_interval=2000
        monitor_replication_lag_timeout=1000
        shun_on_failures=1
        ping_interval_server_msec=2000
        ping_timeout_server=200
        max_allowed_packet=33554432
        query_cache_size_MB=1024
        commands_stats=true
        sessions_sort=true
        monitor_username="xxxxxx"
        monitor_password="xxxxxx"
}

I didn't specify default_charset in proxysql.cnf and i didn't update default_charset by admin interface.

My question is: why ProxySQL is changing charset on new connection/threads?
The problem is that sometimes (probably when new ProxySQL thread is created) the data is in utf8, an this is a problem when we try to get special characters from db like chinese and cyrillic character.
In this cases we get data encoded in an unexpected way.
We expect that the charset is always in the format specified in default_charset.

As workaround we can try to specify SET NAMES on any new connection to ProxySQL (changing our DB api)

[garutilorenzo]

The problem seems to be solved upgrading to ProxySQL 2.0.7.

First run:

[user@host ~]$ python /tmp/test.py
({'Value': 'latin1', 'Variable_name': 'character_set_client'},
 {'Value': 'latin1', 'Variable_name': 'character_set_connection'},
 {'Value': 'latin1', 'Variable_name': 'character_set_database'},
 {'Value': 'binary', 'Variable_name': 'character_set_filesystem'},
 {'Value': 'latin1', 'Variable_name': 'character_set_results'},
 {'Value': 'latin1', 'Variable_name': 'character_set_server'},
 {'Value': 'utf8', 'Variable_name': 'character_set_system'},
 {'Value': '/usr/share/mysql/charsets/',
  'Variable_name': 'character_sets_dir'})

Second run:

[user@host ~]$ python /tmp/test.py
({'Value': 'latin1', 'Variable_name': 'character_set_client'},
 {'Value': 'latin1', 'Variable_name': 'character_set_connection'},
 {'Value': 'latin1', 'Variable_name': 'character_set_database'},
 {'Value': 'binary', 'Variable_name': 'character_set_filesystem'},
 {'Value': 'latin1', 'Variable_name': 'character_set_results'},
 {'Value': 'latin1', 'Variable_name': 'character_set_server'},
 {'Value': 'utf8', 'Variable_name': 'character_set_system'},
 {'Value': '/usr/share/mysql/charsets/',
  'Variable_name': 'character_sets_dir'})

changing variables:

• mysql-default_charset
• mysql-default_character_set_results (new on 2.0.7)

to "utf8" has no effect on the result (but i think this is ok).

[firelion-cis]

Same problem with ProxySQL version 2.0.5, resolved upgrading to 2.0.7

[dhyey-shah]

This seems to have re-occurred in 2.0.11 ? I am facing the same issue here.