🔧 Troubleshooting Guide

🔹 Package Not Found During Installation

Symptom: E: Unable to locate package mysql-server

Fix: Update your package list first:

sudo apt update
sudo apt install mysql-server
                    

🔹 MySQL Root Password Never Set

Symptom: You installed MySQL but were never prompted for a root password. Now you can't log in.

Fix: On Ubuntu, MySQL often uses auth_socket authentication for root. Log in with sudo:

# Log in as root using sudo (no password needed)
sudo mysql

# Once inside, set a password for root
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword!';
FLUSH PRIVILEGES;
EXIT;

# Now you can log in with: mysql -u root -p
                    

🔹 mysql_secure_installation Fails

Symptom: Running sudo mysql_secure_installation gives errors or loops.

Fix: Make sure MySQL is running first, then try:

# Ensure MySQL is running
sudo service mysql start

# If it still fails, set root password manually first (see above)
# Then run the secure installation
sudo mysql_secure_installation
                    

🔹 Apache Won't Start (Port 80 in Use)

Symptom: sudo service apache2 start fails because another process is using port 80.

Fix:

# Find what's using port 80
sudo lsof -i :80

# If it's another web server (nginx, IIS), stop it
sudo service nginx stop

# Or change Apache's port in /etc/apache2/ports.conf
# Listen 8080 (instead of 80)

# Restart Apache
sudo service apache2 restart
                    

🔹 MySQL Won't Start

Symptom: sudo service mysql start fails or exits immediately.

Fix: Check the error log:

# Check the status
sudo service mysql status

# Read the error log
sudo tail -50 /var/log/mysql/error.log

# Common causes:
# - Port 3306 already in use → another MySQL instance running
# - Corrupted data files → check the error log for specifics
# - Insufficient disk space → check with: df -h
# - Permission issues → check ownership of /var/lib/mysql

# Try restarting
sudo service mysql restart
                    

🔹 MySQL Stops Unexpectedly

Symptom: MySQL was running but crashed or stopped.

Fix:

# Check the error log for crash details
sudo tail -100 /var/log/mysql/error.log

# Common causes:
# - Out of memory (OOM killer) → check: dmesg | grep -i mysql
# - Disk full → check: df -h
# - Configuration error after editing my.cnf

# Restart the service
sudo service mysql start
                    

🔹 Checking MySQL Status

# Is MySQL running?
sudo service mysql status

# Which version?
mysql --version

# Is it listening on port 3306?
sudo netstat -tlnp | grep 3306
# or
sudo ss -tlnp | grep 3306
                    

🔹 "Access Denied" with Correct Password

Symptom: You're sure the password is right, but you still get ERROR 1045.

Possible Causes:

• The user exists for a different host (e.g., 'user'@'%' but not 'user'@'localhost')
• Root uses auth_socket — try sudo mysql instead
• Password has special characters that the shell is interpreting — wrap in single quotes

# Try with sudo (for auth_socket users)
sudo mysql

# Check which authentication method root uses
sudo mysql -e "SELECT User, Host, plugin FROM mysql.user WHERE User='root';"

# If it shows 'auth_socket', switch to password authentication:
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword!';
FLUSH PRIVILEGES;
                    

🔹 Can't Connect via Socket

Symptom: Can't connect through socket '/var/run/mysqld/mysqld.sock'

Fix:

# Check if MySQL is running
sudo service mysql status

# If not running, start it
sudo service mysql start

# If the socket file is missing, check the config
cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep socket

# Verify the socket file exists
ls -la /var/run/mysqld/mysqld.sock
                    

🔹 Connection Times Out (Remote)

Fix: MySQL defaults to localhost-only. For remote connections:

# 1. Edit MySQL config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change: bind-address = 127.0.0.1
# To:     bind-address = 0.0.0.0

# 2. Restart MySQL
sudo service mysql restart

# 3. Create a user that can connect remotely
mysql -u root -p
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'SecurePass!';
GRANT ALL PRIVILEGES ON bookstore.* TO 'remote_user'@'%';

# 4. Check firewall (if applicable)
sudo ufw allow 3306
                    

🔹 phpMyAdmin Page Not Found (404)

Symptom: Navigating to http://localhost/phpmyadmin gives a 404 error.

Fix:

# Check if phpMyAdmin is installed
dpkg -l | grep phpmyadmin

# If not installed:
sudo apt install phpmyadmin

# During installation, select "apache2" when asked for the web server

# If installed but not linked to Apache:
sudo ln -s /usr/share/phpmyadmin /var/www/html/phpmyadmin

# Restart Apache
sudo service apache2 restart
                    

🔹 Can't Log In to phpMyAdmin

Symptom: The login page appears but your credentials are rejected.

Fix: phpMyAdmin uses MySQL's authentication. Make sure your MySQL user uses mysql_native_password (not auth_socket):

sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword!';
FLUSH PRIVILEGES;
EXIT;
                    

🔹 Upload Size Too Small in phpMyAdmin

Symptom: Can't import large .sql files — "file exceeds maximum upload size."

Fix: Edit PHP settings:

# Find the php.ini file
php -i | grep "Loaded Configuration File"

# Edit it (path may vary)
sudo nano /etc/php/8.x/apache2/php.ini

# Change these values:
# upload_max_filesize = 64M
# post_max_size = 64M
# max_execution_time = 300

# Restart Apache
sudo service apache2 restart
                    

🔹 Query Returns No Results (But Should)

Possible Causes:

• Wrong database selected — check with SELECT DATABASE();
• Table is empty — check with SELECT COUNT(*) FROM table_name;
• WHERE condition too restrictive
• Case-sensitive comparison on a case-insensitive column (or vice versa)
• Comparing to NULL with = instead of IS NULL

-- Bad: This never works for NULL values
SELECT * FROM books WHERE author_id = NULL;

-- Fix: Use IS NULL
SELECT * FROM books WHERE author_id IS NULL;
                    

🔹 JOIN Returns Unexpected Duplicates

Cause: A many-to-many relationship or missing DISTINCT.

-- If you see duplicate rows, check the relationship
-- Use DISTINCT to remove duplicates
SELECT DISTINCT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.id;

-- Or check if you're joining on the wrong column
                    

🔹 Query Hangs or Is Very Slow

Quick Fixes:

-- 1. Check if there's a long-running query
SHOW PROCESSLIST;

-- 2. Kill a stuck query (use the Id from PROCESSLIST)
KILL 12345;

-- 3. Analyze the slow query
EXPLAIN SELECT * FROM books WHERE author_id = 3;
-- Look for "type: ALL" (full table scan) — add an index

-- 4. Add an index
CREATE INDEX idx_author_id ON books(author_id);
                    

🔹 "You Can't Specify Target Table for Update in FROM Clause"

Cause: You're trying to UPDATE/DELETE a table while reading from it in a subquery.

-- Bad:
DELETE FROM books WHERE price = (SELECT MIN(price) FROM books);

-- Fix: Wrap in a derived table
DELETE FROM books WHERE price = (SELECT min_price FROM (SELECT MIN(price) AS min_price FROM books) AS tmp);
                    

🔹 Quick Performance Checklist

1. Use EXPLAIN — look for type: ALL (full table scan). Add indexes on columns in WHERE, JOIN, and ORDER BY.
2. Avoid SELECT * — only request the columns you need.
3. Add indexes — on foreign keys, frequently filtered columns, and columns used in ORDER BY.
4. Use LIMIT — don't fetch thousands of rows when you need 10.
5. Check for missing foreign key indexes — foreign key columns should always be indexed.

🔹 Enable the Slow Query Log

-- Check if slow query log is enabled
SHOW VARIABLES LIKE 'slow_query_log';

-- Enable it
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- Log queries slower than 1 second

-- Check the log file location
SHOW VARIABLES LIKE 'slow_query_log_file';
                    

# View the slow query log
sudo tail -50 /var/lib/mysql/hostname-slow.log
                    

🔹 Garbled Characters (Mojibake)

Symptom: Characters like é appear instead of é, or emojis show as ????.

Fix: Ensure everything uses utf8mb4:

-- Check database encoding
SELECT DEFAULT_CHARACTER_SET_NAME FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'bookstore';

-- Check table encoding
SHOW CREATE TABLE books;

-- Fix database encoding
ALTER DATABASE bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Fix table encoding
ALTER TABLE books CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Set connection encoding
SET NAMES utf8mb4;
                    

🔹 Emojis Show as "????"

Cause: The column uses utf8 (3-byte) instead of utf8mb4 (4-byte). Emojis require 4 bytes.

Fix: Convert the column or table to utf8mb4:

ALTER TABLE posts MODIFY COLUMN content TEXT CHARACTER SET utf8mb4;
                    

🔹 Services Don't Start Automatically in WSL

Symptom: MySQL and Apache aren't running when you open WSL.

Cause: WSL doesn't use systemd by default, so services don't auto-start.

Fix: Start services manually each session, or create an alias:

# Start services manually
sudo service mysql start
sudo service apache2 start

# Or add to ~/.bashrc for convenience
echo 'alias lamp-start="sudo service mysql start && sudo service apache2 start"' >> ~/.bashrc
source ~/.bashrc

# Now just type: lamp-start
                    

🔹 Can't Access localhost from Windows Browser

Symptom: Apache is running in WSL but http://localhost doesn't work in your Windows browser.

Fix:

# Check if Apache is running
sudo service apache2 status

# Try the WSL IP address instead
hostname -I
# Use that IP in your browser: http://172.x.x.x/phpmyadmin

# In newer WSL2, localhost forwarding should work.
# If not, restart WSL: (in PowerShell)
# wsl --shutdown
# Then reopen your Ubuntu terminal
                    

🔹 MySQL Data Directory Permissions in WSL

Symptom: MySQL won't start with permission errors on /var/lib/mysql.

Fix:

# Fix ownership
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

# Restart MySQL
sudo service mysql start