Getting wrong session timezone through ProxySQL

Andras Gera

unread,

Nov 9, 2017, 7:29:39 AM11/9/17

to proxysql

Hi There,

We have faced to an issue with ProxySQL getting the correct @@session.time_zone.

When we deployed the application server and MariaDB server with ProxySQL (installed on application server) we had no issue like this. The problem came out when we started to use it in production on a high load website.

See attachment for the result test page when the problem occurs. The top info shows connection settings through ProxySQL and the bottom shows direct connection to MariaDB server. MariaDB session timezone is always correct and never changes and gives SYSTEM as result.

Through ProxySQL it shows SYSTEM as well but after a little time it starts to show +00:00 few times then a little while later it shows +00:00 and few times SYSTEM. Then finally it shows only +00:00. Like you fill a glass with water :).

Also when we switch the website from ProxySQL to direrectly connect to MariaDB server the it happens opposite. Like you empty that glass :)

So this issue seems ProxySQL related because when we connect directly to MariaDB sever than that glass will only emptying when I start to refresh that test page I attached.

So any help to solve this issue will be appreciated.

Thank you,

Andras

timezone_issue.png

René Cannaò

unread,

Nov 9, 2017, 8:02:36 AM11/9/17

to Andras Gera, proxysql

Hi Andras,

Can we have the output of:

SELECT * FROM stats_mysql_query_digest WHERE digest_text LIKE '%time%zone%'\G

SHOW MYSQL STATUS;

Thanks

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+2

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Andras Gera

unread,

Nov 9, 2017, 8:16:42 AM11/9/17

to proxysql

Dear Rene,

Thank you for you quick response. Here's the result you asked:

MySQL [(none)]> SELECT * FROM stats_mysql_query_digest WHERE digest_text LIKE '%time%zone%'\G

*************************** 1. row ***************************

hostgroup: 0

schemaname: schema

username: dbuser

digest: 0xC7CC2EA40E54B940

digest_text: SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone

count_star: 333

first_seen: 1510232854

last_seen: 1510232992

sum_time: 141765

min_time: 243

max_time: 2878

*************************** 2. row ***************************

hostgroup: 0

schemaname: schema

username: dbuser

digest: 0xC32A4EA4BA8FC9D1

digest_text: SET time_zone = ?

count_star: 595

first_seen: 1510232843

last_seen: 1510232952

sum_time: 0

min_time: 0

max_time: 0

2 rows in set (0.01 sec)

MySQL [(none)]> SHOW MYSQL STATUS;

+------------------------------+------------+

| Variable_name | Value |

+------------------------------+------------+

| Active_Transactions | 0 |

| Backend_query_time_nsec | 7159066112 |

| Client_Connections_aborted | 0 |

| Client_Connections_connected | 0 |

| Client_Connections_created | 5017 |

| Client_Connections_non_idle | 0 |

| Com_autocommit | 0 |

| Com_autocommit_filtered | 0 |

| Com_backend_change_user | 326 |

| Com_backend_init_db | 0 |

| Com_backend_set_names | 0 |

| Com_backend_stmt_close | 0 |

| Com_backend_stmt_execute | 0 |

| Com_backend_stmt_prepare | 0 |

| Com_commit | 0 |

| Com_commit_filtered | 0 |

| Com_frontend_init_db | 0 |

| Com_frontend_set_names | 0 |

| Com_frontend_stmt_close | 0 |

| Com_frontend_stmt_execute | 0 |

| Com_frontend_stmt_prepare | 0 |

| Com_frontend_use_db | 4684 |

| Com_rollback | 0 |

| Com_rollback_filtered | 0 |

| ConnPool_get_conn_failure | 0 |

| ConnPool_get_conn_immediate | 389 |

| ConnPool_get_conn_success | 124150 |

| ConnPool_memory_bytes | 617584 |

| Mirror_concurrency | 0 |

| Mirror_queue_length | 0 |

| MySQL_Monitor_Workers | 16 |

| MySQL_Thread_Workers | 8 |

| ProxySQL_Uptime | 522 |

| Queries_backends_bytes_recv | 974640610 |

| Queries_backends_bytes_sent | 11938310 |

| Query_Cache_Entries | 0 |

| Query_Cache_Memory_bytes | 0 |

| Query_Cache_Purged | 0 |

| Query_Cache_bytes_IN | 0 |

| Query_Cache_bytes_OUT | 0 |

| Query_Cache_count_GET | 0 |

| Query_Cache_count_GET_OK | 0 |

| Query_Cache_count_SET | 0 |

| Query_Processor_time_nsec | 154559318 |

| Questions | 129853 |

| SQLite3_memory_bytes | 1882840 |

| Server_Connections_aborted | 0 |

| Server_Connections_connected | 7 |

| Server_Connections_created | 7 |

| Servers_table_version | 1 |

| Slow_queries | 0 |

| Stmt_Cached | 0 |

| Stmt_Client_Active_Total | 0 |

| Stmt_Client_Active_Unique | 0 |

| Stmt_Max_Stmt_id | 0 |

| Stmt_Server_Active_Total | 0 |

| Stmt_Server_Active_Unique | 0 |

| mysql_backend_buffers_bytes | 0 |

| mysql_frontend_buffers_bytes | 0 |

| mysql_session_internal_bytes | 21376 |

+------------------------------+------------+

60 rows in set (0.00 sec)

Andras Gera

unread,

Nov 20, 2017, 5:16:49 AM11/20/17

to proxysql

Dear Rene,

Did you have time to investigate this issue?

If you need access to the server, please contact with me on private channel.

Thank you,

Andras

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

René Cannaò

unread,

Nov 20, 2017, 7:59:48 PM11/20/17

to Andras Gera, proxysql

Hi Andras,

I managed to reproduce it and I opened a bug:
https://github.com/sysown/proxysql/issues/1253

It will be fixed soon.

Thanks

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.

René Cannaò

unread,

Nov 23, 2017, 6:19:38 AM11/23/17

to Andras Gera, proxysql

This is now fixed.

Thanks

Andras Gera

unread,

Nov 23, 2017, 7:32:55 AM11/23/17

to proxysql

This is great René!

Can you tell me when is the release of the new version expected?

Thanks

Reply all

Reply to author

Forward