How to fix ERROR 1040: Too many connections in MySQL

-- Show current connection limit
SHOW VARIABLES LIKE 'max_connections';

-- Show maximum connections used since server startup
SHOW STATUS LIKE 'Max_used_connections';

-- Show current active connections
SHOW PROCESSLIST;

-- Get a count of connections by state
SHOW PROCESSLIST;
SELECT state, COUNT(*) FROM information_schema.processlist GROUP BY state;

-- Check current limit
SHOW VARIABLES LIKE 'max_connections';

-- Increase temporarily (no restart required)
SET GLOBAL max_connections = 500;

-- Verify the change
SHOW VARIABLES LIKE 'max_connections';

# On Linux/Unix, find the config file:
# /etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu)
# /etc/my.cnf (CentOS/RHEL, generic)
# /usr/local/mysql/etc/my.cnf (macOS with Homebrew)

# Edit the config file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Find the [mysqld] section and add or modify:
[mysqld]
max_connections = 500

# Save and exit (Ctrl+X, Y, Enter in nano)

# Restart MySQL to apply:
sudo systemctl restart mysql
# or on macOS:
brew services restart mysql

# Verify the change persisted:
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"

-- Check current timeouts (default is 28800 seconds = 8 hours)
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

-- Set shorter timeouts at runtime (300 seconds = 5 minutes)
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 300;

-- Verify:
SHOW VARIABLES LIKE '%timeout%';

[mysqld]
wait_timeout = 300
interactive_timeout = 300

const pool = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

engine = create_engine(
  'mysql+pymysql://root:password@localhost/mydb',
  pool_size=10,
  max_overflow=20,
  pool_pre_ping=True
)

HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
DataSource ds = new HikariDataSource(config);

-- Enable slow query logging at runtime
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking >2 seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Make permanent in config file:
[mysqld]
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
slower_log_file = /var/log/mysql/slow.log

# Install mysqldumpslow if needed
sudo apt-get install mysql-server

# Analyze slow queries
mysqldumpslow /var/log/mysql/slow.log | head -20

-- Show all connections
SHOW PROCESSLIST;

-- Kill a specific connection by ID
KILL CONNECTION 12345;

-- Kill all connections except the one executing this command
SELECT GROUP_CONCAT(CONCAT("KILL ", id)) FROM information_schema.processlist WHERE id != CONNECTION_ID() AND command != "Sleep";

-- Run the output of the above, or use a script:
KILL 101;
KILL 102;
KILL 103;