How to fix ERROR 2004: Can't create TCP/IP socket in MySQL

# When you see the error, capture the full message:
# "ERROR 2004: Can't create TCP/IP socket (24)"
# The errno is 24

# Common errno values:
# 24 = EMFILE (Too many open files)
# 97 = EAFNOSUPPORT (Address family not supported)
# 10106 = WSAENOBUFS (Windows - no buffer space)

# Check per-user file descriptor limit:
ulimit -n

# Check current open file descriptors for your user:
lsof -u $USER | wc -l

# Check for the mysql process specifically:
lsof -p $(pgrep mysqldump)
# or for a specific PID:
lsof -p 12345 | wc -l

# Check system-wide limits:
cat /proc/sys/fs/file-max
cat /proc/sys/fs/nr_open

# Count total open files across all processes:
lsof | wc -l

# Check both soft and hard limits:
ulimit -Sn  # soft limit
ulimit -Hn  # hard limit

# Increase soft limit for the current session:
ulimit -n 65536

# For permanent change, edit /etc/security/limits.conf:
# Add these lines (replace username or use *):
* soft nofile 65536
* hard nofile 65536

# For systemd services, edit the service file:
# /etc/systemd/system/mysql.service or similar
# In the [Service] section, add:
LimitNOFILE=65536

# Reload systemd and restart:
sudo systemctl daemon-reload
sudo systemctl restart mysql

# Verify the change:
cat /proc/$(pgrep mysql)/limits | grep files

# Find processes with excessive open files:
for pid in $(pgrep mysqldump); do echo "PID $pid:" $(lsof -p $pid | wc -l); done

# Kill connections from stalled clients:
mysql -u root -p -e "SHOW PROCESSLIST;" | grep Sleep
mysql -u root -p -e "KILL <process_id>;"

# On the client side, close unused connections in code:
# For example, in Python/PyMySQL:
db.close()  # Close connection when done

# Check for orphaned temporary files consuming descriptors:
ls -la /tmp | wc -l
df /tmp

# Clean up old backup or dump files:
rm -f /tmp/mysql_backup_*.sql

# Test network connectivity to the MySQL host:
ping mysql-server.example.com

# Test if the MySQL port is open:
nc -zv mysql-server.example.com 3306
# or:
telnet mysql-server.example.com 3306

# Verify the MySQL connection string is correct:
# Should be in format: mysql -h hostname -u user -p -P 3306

# Test with explicit protocol (if available):
mysql -h 127.0.0.1 -u root -p --protocol=TCP

# On Windows, verify TCP/IP is enabled in network settings:
ipconfig /all | findstr "TCP"

# Check if SELinux is enforcing:
getenforce

# Temporarily disable SELinux for testing (requires root):
sudo setenforce 0

# Check AppArmor status:
sudo systemctl status apparmor

# View AppArmor profile for MySQL:
grep mysql /etc/apparmor.d/usr.sbin.mysqld

# Check firewall rules:
sudo iptables -L -n
sudo firewall-cmd --list-all
sudo ufw status

# If a security tool is blocking, either:
# 1. Add an exception for the MySQL client
# 2. Temporarily disable for testing to confirm

# SELinux denials:
grep denied /var/log/audit/audit.log | grep mysql

# AppArmor denials:
grep apparmor /var/log/syslog | grep DENIED

# Reset Winsock (run as Administrator):
netsh winsock reset catalog
netsh int ip reset reset.log

# Flush DNS cache:
ipconfig /flushdns

# Restart Windows networking:
net stop "network connections"
net start "network connections"

# Check TCP/IP settings:
ipconfig /all

# Verify MySQL port is open and available:
netstat -ano | findstr ":3306"

# Connect via Unix socket instead:
mysql -u root -p --protocol=socket --socket=/var/run/mysqld/mysqld.sock

# Verify the socket exists and is readable:
ls -la /var/run/mysqld/mysqld.sock

# Use in connection string (Python example):
# mysql+pymysql://user:password@localhost/dbname?charset=utf8mb4&unix_socket=/var/run/mysqld/mysqld.sock