MySQL server has gone away
1,481 views
Skip to first unread message
Benjamin Argoud
Mar 29, 2018, 12:22:28 PM3/29/18
to proxysql
Hi,
In my tests (a script with a loop which contains this simple update querie: UPDATE dns SET test=1 WhERE id=?), I have the "MySQL server has gone away" error after only 7-8 seconds. If I change my PHP script to use my MySQL Host directly, the script run all queries without problem (120 seconds).
I've got a problem with ProxySQL. Lot of MySQL queries returns "MySQL Server has gone away".
If I change the DB host to point directly to my MySQL host, there is no error.
My ProxySQL variables
mysql> show variables;+-----------------------------------------------------+---------------------------------------------------------------------------+| Variable_name | Value |+-----------------------------------------------------+---------------------------------------------------------------------------+| admin-admin_credentials | login:pass || 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:3305;/tmp/proxysql.sock || admin-read_only | false || admin-refresh_interval | 2000 || admin-stats_credentials | login:pass || 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-version | 1.4.7-23-g64e0eef || admin-web_enabled | true || admin-web_port | 8080 || mysql-autocommit_false_is_transaction | false || mysql-autocommit_false_not_reusable | false || mysql-client_found_rows | true || mysql-commands_stats | true || mysql-connect_retries_delay | 1 || mysql-connect_retries_on_failure | 10 || mysql-connect_timeout_server | 5000 || 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 | 2000 || 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_filename | queries.log || mysql-eventslog_filesize | 104857600 || mysql-forward_autocommit | false || mysql-free_connections_pct | 100 || mysql-have_compress | true || mysql-hostgroup_manager_verbose | 1 || mysql-init_connect | (null) || mysql-interfaces | 10.0.0.104:3306;10.0.0.105:3306;10.0.0.106:3306;/var/lib/mysql/mysql.sock || mysql-long_query_time | 1000 || mysql-max_allowed_packet | 536870912 || mysql-max_connections | 10000 || mysql-max_stmts_cache | 10000 || mysql-max_stmts_per_connection | 20 || mysql-max_transaction_time | 14400000 || 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_groupreplication_healthcheck_interval | 5000 || mysql-monitor_groupreplication_healthcheck_timeout | 800 || mysql-monitor_history | 600000 || mysql-monitor_password | password || mysql-monitor_ping_interval | 10000 || mysql-monitor_ping_max_failures | 3 || mysql-monitor_ping_timeout | 1000 || mysql-monitor_query_interval | 60000 || mysql-monitor_query_timeout | 100 || mysql-monitor_read_only_interval | 1500 || mysql-monitor_read_only_max_timeout_count | 3 || mysql-monitor_read_only_timeout | 500 || mysql-monitor_replication_lag_interval | 10000 || mysql-monitor_replication_lag_timeout | 1000 || mysql-monitor_replication_lag_use_percona_heartbeat | || mysql-monitor_slave_lag_when_null | 60 || mysql-monitor_username | proxysql || mysql-monitor_wait_timeout | true || mysql-monitor_writer_is_also_reader | true || mysql-multiplexing | true || mysql-ping_interval_server_msec | 10000 || mysql-ping_timeout_server | 500 || mysql-poll_timeout | 2000 || mysql-poll_timeout_on_failure | 100 || mysql-query_cache_size_MB | 256 || 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_processor_iterations | 0 || mysql-query_processor_regex | 1 || mysql-query_retries_on_failure | 1 || mysql-server_capabilities | 45578 || mysql-server_version | 5.7.21 || mysql-servers_stats | true || mysql-session_idle_ms | 1000 || mysql-session_idle_show_processlist | false || mysql-sessions_sort | true || mysql-shun_on_failures | 5 || mysql-shun_recovery_time_sec | 10 || mysql-ssl_p2s_ca | (null) || mysql-ssl_p2s_cert | (null) || mysql-ssl_p2s_cipher | (null) || mysql-ssl_p2s_key | (null) || mysql-stacksize | 1048576 || mysql-stats_time_backend_query | false || mysql-stats_time_query_processor | false || mysql-threads | 32 || 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 | 2147483647 || mysql-throttle_ratio_server_to_client | 0 || mysql-verbose_query_error | false || mysql-wait_timeout | 600 |+-----------------------------------------------------+---------------------------------------------------------------------------+
What can I check to fix this problem?
Thank you
René Cannaò
Mar 29, 2018, 12:36:13 PM3/29/18
to Benjamin Argoud, proxysql
Benjamin,
I would start from checking the error log.--
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.
Benjamin Argoud
Mar 29, 2018, 12:49:19 PM3/29/18
to proxysql
Yes sure, sorry I forgot to tell you I've checked the /var/lib/proxysql/proxysql.log file but no line is added when I have a "MySQL has gone away" error occured.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
Benjamin Argoud
Apr 3, 2018, 7:09:40 AM4/3/18
to proxysql
Hi, a little update for my problem.
I have a "MySQL has gone away" only when my PHP script is slow. I have tested to add a "sleep(1);" in my loop and I have the error to each run (after only 4-5 seconds) which is strange if we read variables values I have copied in my first post. If I remove this, it's ok.
I've also tried the same script on MySQL Router (I don't want to use it because ProxySQL has more interessant features) on the same machine and the same script (including the sleep(1); pause) is OK (script finished after 546 secs runtime, I've included a big loop with the 1 second sleep on each action for my tests).
To summary:
- If I configure my script to point directly to my MySQL Master-node : OK
- If I configure my script to point to ProxySQL which route all queries to my MySQL Master-node : errors
- If I configure my script to point to MySQL Router which route all queries to my MySQL Master-node : OK
I see two options:
- There is an error in my ProxySQL configuration
- There is an error in my ProxySQL configuration
- ProxySQL ignores one (or more) variable value
.
Anton Aleksandrov
Jun 5, 2019, 2:32:10 AM6/5/19
to proxysql
Hello Benjamin,
Were you able to fix this issue? I am quite new to ProxySQL, trying to implement on one project, that uses Percona Master-Master cluster. So far routing just one unimportant cronjob to it and we get the same error from time to time. Cron script seems to run some time, definitely more than 4-5 seconds. But the same script routed directly to the cluster or through HAProxy has never given any issues.
Anton
Benjamin Argoud
Jun 5, 2019, 2:51:14 AM6/5/19
to proxysql
Hello Anton,
I haven't found real solution :(
I've split my queries with joins and it seems to be better.
Shaun
Jul 10, 2019, 2:07:46 AM7/10/19
to proxysql
Came across this post troubleshooting a similar issue and at some point in testing made what I think is the same mistake you have.
As per your proxysql settings you have
As per your proxysql settings you have
mysql-wait_timeout | 600
Unlike MySQL which uses seconds for wait_timeout - ProxySQL uses milliseconds (https://github.com/sysown/proxysql/wiki/Global-variables#mysql-wait_timeout)
Unlike MySQL which uses seconds for wait_timeout - ProxySQL uses milliseconds (https://github.com/sysown/proxysql/wiki/Global-variables#mysql-wait_timeout)
so your setting is probably not the 1min I assume you intended but in fact 0.6 seconds..
hope that helps.
wolfe fu
Jul 12, 2019, 10:49:47 PM7/12/19
to proxysql
Admin> show variables like '%time%';
+----------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------+----------+
| mysql-shun_recovery_time_sec | 10 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-monitor_connect_timeout | 5000 |
| mysql-monitor_ping_timeout | 5000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_query_timeout | 1000 |
| mysql-monitor_wait_timeout | true |
| mysql-max_transaction_time | 14400000 |
| mysql-wait_timeout | 28800000 |
| mysql-long_query_time | 1000 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-default_time_zone | SYSTEM |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-default_query_timeout | 86400000 |
| mysql-poll_timeout | 2000 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_read_only_timeout | 5000 |
| mysql-ping_timeout_server | 5000 |
+----------------------------------------------------+----------+
23 rows in set (0.00 sec)
Hi, This is my configuration, but the latest version(proxysql 2.0.5) will still appear.
MySQL_Session.cpp:107:kill_query_thread(): [WARNING] KILL CONNECTION 3304618 on xxx
Hi, This is my configuration, but the latest version(proxysql 2.0.5) will still appear.
MySQL_Session.cpp:107:kill_query_thread(): [WARNING] KILL CONNECTION 3304618 on xxx
Reply all
Reply to author
Forward
0 new messages