📋

Description

MySQL Error 2004 indicates that the system failed to create a necessary TCP/IP socket, preventing the MySQL client or server from establishing a network connection. This typically occurs when network resources are unavailable, ports are in use, or system limits are reached.

💬

Error Message

Can't create TCP/IP socket (%d)

🔍

Known Causes

3 known causes

⚠️

Port Already in Use

Another application or an orphaned process is already utilizing the required TCP/IP port (e.g., 3306 for MySQL).

⚠️

System Resource Exhaustion

The operating system has run out of available file descriptors, network buffers, or other resources needed to create a new socket.

⚠️

Network Configuration Issues

Incorrect network settings, firewall restrictions, or routing problems prevent the system from creating a valid TCP/IP socket.

🛠️

Solutions

3 solutions available

1. Verify Network Connectivity and Firewall Rules easy

Ensure the client can reach the MySQL server's IP address and port, and that firewalls are not blocking the connection.

1

From the client machine attempting to connect, ping the MySQL server's IP address to confirm basic network reachability.

ping <mysql_server_ip>

2

Use `telnet` or `nc` (netcat) to test if the MySQL port (default 3306) is open and accessible on the server from the client machine.

telnet <mysql_server_ip> 3306

3

If `telnet` or `nc` fails, check firewall rules on both the client and server. On Linux, use `ufw` or `firewalld`. On Windows, check Windows Defender Firewall. Ensure that inbound connections to port 3306 are allowed for the client's IP or network.

# Example for ufw on Ubuntu:
sudo ufw allow from <client_ip> to any port 3306

# Example for firewalld on CentOS/RHEL:
sudo firewall-cmd --zone=public --add-rich-rule='rule family="ipv4" source address="<client_ip>" port protocol="tcp" port="3306" accept'

4

On the MySQL server, ensure the `bind-address` directive in the MySQL configuration file (`my.cnf` or `my.ini`) is set to allow connections from the client's IP or is commented out to listen on all interfaces (use with caution and proper firewalling).

# In my.cnf or my.ini
# bind-address = 127.0.0.1  <-- comment this out or set to server's IP
bind-address = 0.0.0.0

5

Restart the MySQL server after modifying the configuration file.

# On systemd-based systems (most modern Linux):
sudo systemctl restart mysql

# On older init.d systems:
sudo service mysql restart

2. Check MySQL Server Status and Listening Address medium

Confirm that the MySQL server process is running and listening on the expected network interface and port.

1

On the MySQL server, check if the MySQL process is running.

ps aux | grep mysqld

2

Verify that MySQL is listening on the correct IP address and port. The default port is 3306.

sudo netstat -tulnp | grep 3306

3

If `netstat` shows MySQL listening on `127.0.0.1` (localhost) and you're trying to connect from a remote machine, this is the cause. Edit the MySQL configuration file (`my.cnf` or `my.ini`) to change the `bind-address`.

# In my.cnf or my.ini
# bind-address = 127.0.0.1
bind-address = 0.0.0.0  # Or the specific IP address of the server

4

Restart the MySQL service after making configuration changes.

# On systemd-based systems:
sudo systemctl restart mysql

# On older init.d systems:
sudo service mysql restart

3. Resource Exhaustion on the Server advanced

Investigate if the server has run out of available network sockets or system resources.

1

Check the system's open file descriptor limits. MySQL, like other processes, uses file descriptors for network connections.

ulimit -n

2

If the limit is low and the server is under heavy load with many connections, you might need to increase it. This is typically done by editing `/etc/security/limits.conf` (on Linux).

# Example to increase limits for the mysql user:
mysql    soft    nofile    65535
mysql    hard    nofile    65535

3

You can also check the system's overall socket usage.

grep -c "^" /proc/net/sockstat

4

Monitor server resources (CPU, RAM, disk I/O) to rule out general system instability or overload that might indirectly affect socket creation.

top
htop
vmstat

5

If resource exhaustion is suspected, consider optimizing MySQL queries, increasing server hardware resources, or distributing the load across multiple servers.

🔗

Related Errors

5 related errors

MySQL Error 2004: TCP/IP Socket Creation Failure