Let us go through the ‘connection to the MariaDB server lost’ and its solution in detail.

We can solve your MariaDB lost connection during query error at Bobcares with our Server Management Service.

Do you want to learn more? Continue reading and get in touch if you have any additional queries.

MariaDB lost connection

This MariaDB lost connection during query issue frequently occurs when performing a long or sophisticated MySQL query that takes more than a few seconds to complete. Change the timeout-related global settings in the MySQL database server to resolve the error.

Increase the connection timeout from the command

When connecting to MySQL from the command line, use the —connect-timeout option to raise the number of seconds MySQL waits for a connection response. Increasing the timeout from the command can resolve the MariaDB lost connection during query error.

MySQL will wait 10 seconds before returning a connection timeout error by default. To wait for two minutes, the user can increase the number to 120 seconds:

mysql -uroot -proot --connect-timeout 120

The user can change the number 120 above to the amount of time they want to wait for a connection response. Once inside the MySQL console, a user may rerun the query to determine if it was successful.

The —connect-timeout option modifies the timeout seconds temporarily. It only works for the currently running MySQL session. Therefore the user must use the option each time they want the connection timeout extension. If the user wishes to make a permanent modification to the connection timeout variable, they must do so using their MySQL database server or the GUI interface used to access the database server.

First, let’s look at how to modify the timeout global variables in your MySQL database server to manage the MariaDB lost connection during query error.

Adjust the timeout global variables in your MySQL database server

The MySQL database holds timeout-related global variables, which a user can access by the following query:

SHOW VARIABLES LIKE "%timeout";

Here is the outcome from the local database. Change the following variables for MySQL to conduct longer queries:

Variable_name | Value |
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800

Use the SET GLOBAL query to update the variable values, as illustrated below.

SET GLOBAL connect_timeout = 600;

The query above should set the connect timeout variable to 600 seconds. The user can make changes to the numbers as they see suitable.

Adjust the timeout variables in your MySQL configuration files

Alternatively, if users want to alter the settings of the connections using a MySQL configuration file, they may edit the my.cnf (Mac) or my.ini (Windows) file used by the MySQL connection. Open the configuration file using the user’s preferred text editor, and they must identify the following variables in mysqld:

[mysqld]
connect_timeout = 10
net_read_timeout = 30
wait_timeout = 28800
interactive_timeout = 28800

The wait timeout and interactive timeout variables should not create any issues because their default value is 28800 seconds (or 8 hours). To avoid the ‘MariaDB lost connection during query error’, raise the connect timeout and net read timeout variable settings. Set it to at least 600 seconds (10 minutes).

Adjust timeout-related variables in your MySQL GUI tools

If the user is using GUI MySQL tools such as MySQL Workbench, Sequel Ace, or PHPMyAdmin, timeout-related variables.  Then the user can note its presence in the settings or preferences menu of these products.

Firstly the user must add my.cnf / my.ini in the [mysqld] section max_allowed_packet=32M. Based on the existing database, increase this value:

mysql -u user --password=password database name file_to_import

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude, MariaDB lost connection during query indicates the loss of connection to the MariaDB server. And to fix the error modify the MySQL database server’s global timeout-related settings.