search

sysown / proxysql

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

Empty runtime values for several mysql-default variables ≥ 2.0.11 #2883

Open jhriggs opened 4 years ago

jhriggs commented 4 years ago

Starting in v2.0.11, several mysql-default_* variables are empty in the runtime table, even after LOAD MYSQL VARIABLES TO RUNTIME. The corresponding values in memory are not written to runtime. Among the variables affected are all of the character set and collation variables, as well as mysql-default_sql_safe_updates. (These are just the ones I happened to try; I did not try all of them.)

Below are queries showing the issue. These were all run on fresh Docker images with the brand-new, out-of-the-box config with no changes or modifications. This capture happens to be from 2.0.12, but 2.0.11 seemed the same, whereas 2.0.10 (and several other previous versions I tried) appears to be OK.

Fresh out of the box, memory and runtime are different:

MySQL [(none)]> select * from global_variables where variable_name like 'mysql-default%' order by variable_name;
+----------------------------------------+--------------------+
| variable_name                          | variable_value     |
+----------------------------------------+--------------------+
| mysql-default_CHARSET                  | utf8               |
| mysql-default_action                   |                    |
| mysql-default_character_set_client     | utf8               |
| mysql-default_character_set_connection | utf8               |
| mysql-default_character_set_database   | utf8               |
| mysql-default_character_set_results    | utf8               |
| mysql-default_charset                  | utf8               |
| mysql-default_collation_connection     | utf8_general_ci    |
| mysql-default_group_concat_max_len     |                    |
| mysql-default_isolation_level          |                    |
| mysql-default_max_join_size            |                    |
| mysql-default_max_latency_ms           | 1000               |
| mysql-default_names                    |                    |
| mysql-default_net_write_timeout        |                    |
| mysql-default_query_delay              | 0                  |
| mysql-default_query_timeout            | 36000000           |
| mysql-default_reconnect                | true               |
| mysql-default_schema                   | information_schema |
| mysql-default_session_track_gtids      | OFF                |
| mysql-default_sql_auto_is_null         |                    |
| mysql-default_sql_log_bin              |                    |
| mysql-default_sql_mode                 |                    |
| mysql-default_sql_safe_updates         |                    |
| mysql-default_sql_select_limit         |                    |
| mysql-default_time_zone                |                    |
| mysql-default_transaction_read         |                    |
| mysql-default_tx_isolation             | READ-COMMITTED     |
| mysql-default_wsrep_sync_wait          |                    |
+----------------------------------------+--------------------+
28 rows in set (0.01 sec)

MySQL [(none)]> select * from runtime_global_variables where variable_name like 'mysql-default%' order by variable_name;
+----------------------------------------+--------------------+
| variable_name                          | variable_value     |
+----------------------------------------+--------------------+
| mysql-default_CHARSET                  |                    |
| mysql-default_action                   |                    |
| mysql-default_character_set_client     |                    |
| mysql-default_character_set_connection |                    |
| mysql-default_character_set_database   |                    |
| mysql-default_character_set_results    |                    |
| mysql-default_charset                  |                    |
| mysql-default_collation_connection     |                    |
| mysql-default_group_concat_max_len     |                    |
| mysql-default_isolation_level          |                    |
| mysql-default_max_join_size            |                    |
| mysql-default_max_latency_ms           | 1000               |
| mysql-default_names                    |                    |
| mysql-default_net_write_timeout        |                    |
| mysql-default_query_delay              | 0                  |
| mysql-default_query_timeout            | 36000000           |
| mysql-default_reconnect                | true               |
| mysql-default_schema                   | information_schema |
| mysql-default_session_track_gtids      | OFF                |
| mysql-default_sql_auto_is_null         |                    |
| mysql-default_sql_log_bin              |                    |
| mysql-default_sql_mode                 |                    |
| mysql-default_sql_safe_updates         |                    |
| mysql-default_sql_select_limit         |                    |
| mysql-default_time_zone                |                    |
| mysql-default_transaction_read         |                    |
| mysql-default_tx_isolation             | READ-COMMITTED     |
| mysql-default_wsrep_sync_wait          |                    |
+----------------------------------------+--------------------+
28 rows in set (0.00 sec)

Try loading the data from memory to runtime (still empty):

MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> select * from runtime_global_variables where variable_name like 'mysql-default%' order by variable_name;
+----------------------------------------+--------------------+
| variable_name                          | variable_value     |
+----------------------------------------+--------------------+
| mysql-default_CHARSET                  |                    |
| mysql-default_action                   |                    |
| mysql-default_character_set_client     |                    |
| mysql-default_character_set_connection |                    |
| mysql-default_character_set_database   |                    |
| mysql-default_character_set_results    |                    |
| mysql-default_charset                  |                    |
| mysql-default_collation_connection     |                    |
| mysql-default_group_concat_max_len     |                    |
| mysql-default_isolation_level          |                    |
| mysql-default_max_join_size            |                    |
| mysql-default_max_latency_ms           | 1000               |
| mysql-default_names                    |                    |
| mysql-default_net_write_timeout        |                    |
| mysql-default_query_delay              | 0                  |
| mysql-default_query_timeout            | 36000000           |
| mysql-default_reconnect                | true               |
| mysql-default_schema                   | information_schema |
| mysql-default_session_track_gtids      | OFF                |
| mysql-default_sql_auto_is_null         |                    |
| mysql-default_sql_log_bin              |                    |
| mysql-default_sql_mode                 |                    |
| mysql-default_sql_safe_updates         |                    |
| mysql-default_sql_select_limit         |                    |
| mysql-default_time_zone                |                    |
| mysql-default_transaction_read         |                    |
| mysql-default_tx_isolation             | READ-COMMITTED     |
| mysql-default_wsrep_sync_wait          |                    |
+----------------------------------------+--------------------+
28 rows in set (0.00 sec)

Save runtime to memory (now the memory values are empty too):

MySQL [(none)]> save mysql variables from runtime;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select * from global_variables where variable_name like 'mysql-default%' order by variable_name;
+----------------------------------------+--------------------+
| variable_name                          | variable_value     |
+----------------------------------------+--------------------+
| mysql-default_CHARSET                  |                    |
| mysql-default_action                   |                    |
| mysql-default_character_set_client     |                    |
| mysql-default_character_set_connection |                    |
| mysql-default_character_set_database   |                    |
| mysql-default_character_set_results    |                    |
| mysql-default_charset                  |                    |
| mysql-default_collation_connection     |                    |
| mysql-default_group_concat_max_len     |                    |
| mysql-default_isolation_level          |                    |
| mysql-default_max_join_size            |                    |
| mysql-default_max_latency_ms           | 1000               |
| mysql-default_names                    |                    |
| mysql-default_net_write_timeout        |                    |
| mysql-default_query_delay              | 0                  |
| mysql-default_query_timeout            | 36000000           |
| mysql-default_reconnect                | true               |
| mysql-default_schema                   | information_schema |
| mysql-default_session_track_gtids      | OFF                |
| mysql-default_sql_auto_is_null         |                    |
| mysql-default_sql_log_bin              |                    |
| mysql-default_sql_mode                 |                    |
| mysql-default_sql_safe_updates         |                    |
| mysql-default_sql_select_limit         |                    |
| mysql-default_time_zone                |                    |
| mysql-default_transaction_read         |                    |
| mysql-default_tx_isolation             | READ-COMMITTED     |
| mysql-default_wsrep_sync_wait          |                    |
+----------------------------------------+--------------------+
28 rows in set (0.00 sec)

Nothing of note appears in the default log/output other than these warnings:

2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_CHARSET set to invalid value in the configuration file. Changing to default utf8
2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_character_set_results set to invalid value in the configuration file. Changing to default utf8
2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_character_set_connection set to invalid value in the configuration file. Changing to default utf8
2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_character_set_client set to invalid value in the configuration file. Changing to default utf8
2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_character_set_database set to invalid value in the configuration file. Changing to default utf8
2020-06-12 17:16:43 ProxySQL_Admin.cpp:5458:flush_mysql_variables___database_to_runtime(): [WARNING] The default_charset set to invalid value in the configuration file. Changing to default utf8
...
2020-06-12 17:17:08 ProxySQL_Admin.cpp:5475:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set variable server_capabilities with value "569867". Resetting to current "569899".
renecannao commented 4 years ago

Hi @jhriggs . Thank you for the report. This is a know issue, fixed in https://github.com/sysown/proxysql/pull/2866 . A lot of these variables are deprecated. I will merge it to 2.0.13 after adding further testing.