Mysql Reading Final Connect Information System Error 2

"MySQL server has gone away" fault – Solution(s)

The MySQL server has gone abroad fault, means that MySQL server (mysqld) timed out and closed the connexion. By default, MySQL volition close connections after 8 hours (28800 seconds) if nothing happens. Notwithstanding, in some cases, your spider web host, DBA, or app developer may accept decreased this timeout setting, discussed below.

MySQL server has gone away, can exist a frustrating error to solve. This is partly because to solve this error; sometimes the solution involves multiple layers, awarding, or service config changes. This article includes solutions I've seen for this MySQL server full general fault. If you lot've found a solution not listed or linked to on this page, please send me a note or leave a comment.

MySQL server has gone abroad error log examples.

Continue in mind that this error can be logged in a few ways, every bit listed below. In addition, at times, the error is only an indication of a deeper underlying event. Significant the fault could be due to a problem or bug in your connecting application or remote service. In which case, yous need to check ALL related error logs with the aforementioned timestamp to determine whether another issue may be to blame. Application Performance Monitoring solutions and PHP Stack trace tools can be of help. With this in mind, hither are error log examples of the MySQL server has gone away error:

Full general fault: 2006 MySQL server has gone abroad

Error Lawmaking: 2013. Lost connection to MySQL server during query

Warning: Error while sending QUERY packet

PDOException: SQLSTATE[HY000]: General fault: 2006 MySQL server has gone away

MySQL wait_timeout

The reason for MySQL server has gone away error is oft because MySQL's wait_timeout was exceeded. MySQL wait_timeout is the number of seconds the server waits for activity on a non-interactive connectedness before endmost it. You should brand certain thewait_timeout is not set too low. The default for MySQL wait_timeout is 28800 seconds. Ofttimes, it gets lowered arbitrarily. That said, the lower you can set wait_timeout without affecting database connections, can exist a good sign of MySQL database efficiency. Also, bank check the variables:net_read_timeout,net_write_timeout andinteractive_timeout. Adjust or add together the following lines in my.cnf to run into your requirements:

wait_timeout=90 net_read_timeout=xc net_write_timeout=xc interactive_timeout=300 connect_timeout=xc

MySQL connect timeout in PHP config

Take a look at your php.ini config file. You'll find MySQL configuration options. Make sure themysql.connect_timeout setting isn't fix lower than MySQL wait_timeout, discussed in a higher place. The PHP optionmysql.connect_timeout is not only used for connect timeout. Information technology's also when waiting for the first response from the MySQL server. Try increasingmysql.connect_timeout to match or exceed your MySQL wait_timeout and make certain that mysql.allow_persistent is on (default = enabled).

mysql.connect_timeout=90 mysql.allow_persistent=1

Important: Read showtime almost PHP Persistent Database Connections to understand the benefits and caveats.

Also, conform PHP'southwarddefault_socket_timeout. For example, a PHP script could be running a wearisome query. Creating a wait that utilizes the default_socket_timeout. Eventually, information technology quits with the "MySQL server has gone away" error. Before you send hate mail, please read here first. Here'due south an excerpt:

"PHP, past default, sets a read timeout of 60s for streams. This is fix viaphp.ini, default_socket_timeout. This default applies to all streams that set no other timeout value. mysqlnd does not set any other value and therefore connections of long running queries tin can be asunder after default_socket_timeout seconds resulting in an mistake bulletin2006 – MySQL Server has gone away ."

default_socket_timeout=90

To be throughout, besides suitmax_execution_time andmax_input_time nevertheless in php.ini, if necessary. If PHP'southward execution fourth dimension is longer than max_execution_time, then MySQL server might disconnect.

max_execution_time = xc max_input_time = 90

MySQL max_allowed_packet

max_allowed_packet is the maximum size of one packet. The default size of 4MB helps the MySQL server take hold of large (possibly incorrect) packets. As of MySQL eight, the default has been increased to 16MB. If mysqld receives a packet that is as well large, information technology assumes that something is wrong and closes the connection. To prepare this, you should increment the max_allowed_packet in my.cnf, then restart MySQL. The max for this setting is 1GB. For case:

max_allowed_packet = 512M

MySQL innodb_log_file_size

Yous may need to increase theinnodb_log_file_size MySQL variable in your my.cnf configuration. MySQL'sinnodb_log_file_size should be 25% ofinnodb_buffer_pool_size (if possible, no less than 20%). Remember that the larger this value, the longer information technology volition take to recover from a database crash. (Source: Phpmyadmin Counselor)

This ways for case: if your buffer puddle size is ready to innodb_buffer_pool_size=16G  and yourinnodb_log_files_in_group setting is still set to the recommended default of 2 files (innodb_log_files_in_group=2), then yourinnodb_log_file_size should be prepare to 2G. This will create two (2) log files at 2GB each, which equals 25% ofinnodb_buffer_pool_size=16G .

WARNING:Yous must stop MySQL server in guild to alterinnodb_log_file_size orinnodb_log_files_in_group. If y'all don't, you lot run a risk ending! (Read: MySQL Log Redo instructions.)

Other causes of MySQL server has gone away

Remote MySQL connections

Remember earlier I mentioned that the error, at times, is only an indication of a deeper underlying issue. For example, remote MySQL connections to 3rd political party services. Using a third party payment processing plugin for osCommerce, Magento, etc.

MySQL database charset and collation

Changing default database charset to latin1 and default collation to latin1_general_ci seemed to have solved MySQL server has gone away for some.

Exceeding MySQL max_connections setting

Max_connections ready the maximum permitted number of simultaneous customer connections. Be careful with this setting!! Burnout of memory and other resource can occur when set too big and scheduling overhead too increases. Equally a guide, gear up max_connections to approximately double the previous number of maximum simultaneous client connections. E.k., if after a month of uptime, the maximum simultaneous customer connections were 114, then set to max_connections=250. Before you go crazy with this setting, please read: How MySQL Handles Client Connections.

Still unresolved? See MySQL's help page.

Oracle has put together a nice self-help page for MySQL server has gone away errors. On that page, they also suggest that you make sure MySQL didn't stop/restart during the query. Excerpt:

"You can bank check whether the MySQL server died and restarted by executing mysqladmin version  and examining the server's uptime. If the customer connection was cleaved because mysqld  crashed and restarted, you should concentrate on finding the reason for the crash."

# mysqladmin version mysqladmin Ver 9.i Distrib 10.i.40-MariaDB, for Linux on x86_64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.  Server version x.ane.40-MariaDB Protocol version ten Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 20 days 11 hours 49 min 40 sec  Threads: v Questions: 1030744326 Slow queries: 3343 Opens: 3585 Flush tables: 1 Open tables: 2564 Queries per second avg: 582.150

# mysqladmin status Uptime: 1770590 Threads: 4 Questions: 1030752268 Tedious queries: 3343 Opens: 3585 Flush tables: 1 Open up tables: 2564 Queries per 2nd avg: 582.151

I hope this helps!

Related articles:

• MySQL Operation Tuning: Tips, Scripts and Tools
• Tuning MySQL: my.cnf, avert this mutual pitfall!
• MySQL Performance: Stop hoarding. Drib unused MySQL databases

Published: June seventh, 2019 | Last updated: Baronial 10th, 2021

Tags: apm, linux, mariadb, mysql, performance, server, sysadmins

friedmanmiltured.blogspot.com

Source: https://haydenjames.io/mysql-server-has-gone-away-error-solutions/

Share this post