📋

Description

This error indicates that your active connection to the MariaDB server was abruptly terminated by the server itself. This can occur due to various server-side actions, including explicit administrative commands, server restarts, or exceeding configured timeout limits.

💬

Error Message

Connection was killed

🔍

Known Causes

4 known causes

⚠️

Administrator Intervention

A database administrator manually issued a `KILL` command to terminate your specific connection on the MariaDB server.

⚠️

Server Restart or Shutdown

The MariaDB server process handling your connection was restarted or gracefully shut down while the connection was still active.

⚠️

Idle Connection Timeout

Your connection remained inactive for a duration exceeding the server's configured `wait_timeout` or `interactive_timeout` variables, leading to its termination.

⚠️

Server Resource Limits

The MariaDB server terminated the connection because it hit internal resource limits, such as maximum concurrent connections or memory allocation.

🛠️

Solutions

4 solutions available

1. Increase `wait_timeout` and `interactive_timeout` easy

Extend the time MySQL waits for activity on an idle connection.

1

Connect to your MariaDB server as a user with sufficient privileges (e.g., root).

2

Check the current values of `wait_timeout` and `interactive_timeout`.

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

3

If the values are low (e.g., 60 seconds), increase them. A common recommendation is to set them to a higher value like 28800 seconds (8 hours). This can be done temporarily for the current session or permanently.

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

4

To make these changes permanent, edit the MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/50-server.cnf`). Add or modify the following lines under the `[mysqld]` section:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800

5

Restart the MariaDB server for permanent changes to take effect.

sudo systemctl restart mariadb

2. Investigate Network Interruption or Firewall Rules medium

Rule out external factors that might be dropping the connection.

1

Check for network connectivity issues between the client and the MariaDB server. Ping the server from the client machine to ensure basic network reachability.

ping <mariadb_server_ip_or_hostname>

2

Review firewall rules on both the client and server machines, as well as any network firewalls in between. Ensure that the MariaDB port (default 3306) is open and not being blocked or aggressively timed out by network devices.

Example: On Ubuntu/Debian, check with:
ufw status

On CentOS/RHEL, check with:
systemctl status firewalld
firewall-cmd --list-all

3

If using cloud providers (AWS, Azure, GCP), check their security group or network access control list (NACL) settings to ensure the MariaDB port is accessible.

4

Examine server logs for any network-related errors or dropped connections that might coincide with the client experiencing the 'Connection Killed Unexpectedly' error.

Check MariaDB error logs (e.g., /var/log/mysql/error.log or /var/log/mariadb/mariadb.log) and system logs (e.g., /var/log/syslog or /var/log/messages).

3. Optimize Long-Running Queries medium

Address queries that are taking too long and potentially timing out.

1

Enable the slow query log in MariaDB. This will record queries that take longer than a specified time to execute.

In your MariaDB configuration file (`my.cnf` or equivalent):
[mysqld]
 slow_query_log = 1
 slow_query_log_file = /var/log/mysql/mariadb-slow.log
 long_query_time = 2  # Log queries longer than 2 seconds

2

Restart the MariaDB server after modifying the configuration.

sudo systemctl restart mariadb

3

Analyze the slow query log file to identify problematic queries. Use tools like `mysqldumpslow` to summarize the log.

mysqldumpslow /var/log/mysql/mariadb-slow.log

4

For identified slow queries, use `EXPLAIN` to understand their execution plan and identify areas for optimization (e.g., missing indexes, inefficient joins).

EXPLAIN SELECT ... FROM your_table WHERE ...;

5

Consider adding appropriate indexes to tables, rewriting queries, or optimizing table structures to improve query performance.

4. Review `max_connections` and Resource Usage medium

Ensure the server isn't overloaded with too many active connections.

1

Check the current `max_connections` setting in MariaDB.

SHOW VARIABLES LIKE 'max_connections';

2

Monitor the number of active connections to your MariaDB server. If it's consistently close to `max_connections`, this can lead to connection issues.

SHOW GLOBAL STATUS LIKE 'Threads_connected';

3

If `max_connections` is too low for your workload, you can increase it temporarily.

SET GLOBAL max_connections = 500; -- Adjust value as needed

4

To make this change permanent, add or modify the `max_connections` setting in your MariaDB configuration file (`my.cnf` or equivalent) under the `[mysqld]` section.

[mysqld]
max_connections = 500 -- Adjust value as needed

5