How to fix ERROR 1146: Table 'database.table' doesn't exist in MySQL

-- List all tables in the currently selected database:
SHOW TABLES;

-- Or get more details including table type (BASE TABLE vs. VIEW):
SHOW FULL TABLES;

-- Search for a specific table name (case-insensitive on most systems):
SHOW TABLES LIKE 'users';
SHOW TABLES LIKE '%user%';

-- Check which database is currently selected:
SELECT DATABASE();

-- Switch to the correct database:
USE your_database_name;

-- Verify you're in the right database and the table exists:
SHOW TABLES;
SHOW TABLES LIKE 'your_table';

-- Now query the table:
SELECT * FROM your_table LIMIT 1;

-- Check the current setting:
SHOW VARIABLES LIKE 'lower_case_table_names';

-- On Linux/Unix (case-sensitive), these are different tables:
SELECT * FROM Users;      -- Error if table is named users
SELECT * FROM users;      -- Works if table is named users

-- To find the exact table name:
SHOW TABLES WHERE TABLES_IN_database_name = 'users';

-- Or query the information schema:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME LIKE 'user%';

# Find the data directory:
mysql -u root -p -e "SELECT @@datadir;"

# Check permissions on the database directory:
ls -ld /var/lib/mysql/your_database/

# List table files:
ls -la /var/lib/mysql/your_database/ | grep your_table

# These should be owned by mysql:mysql with permissions 660 (files) or 770 (directories):
# Fix if needed:
sudo chown mysql:mysql /var/lib/mysql/your_database/your_table.*
sudo chmod 660 /var/lib/mysql/your_database/your_table.*
sudo chown mysql:mysql /var/lib/mysql/your_database/
sudo chmod 770 /var/lib/mysql/your_database/

sudo systemctl restart mysql

# Check and repair all tables in a database:
mysqlcheck -u root -p your_database

# Repair a specific table:
mysqlcheck -u root -p your_database your_table

# Repair with --repair flag for aggressive repair:
mysqlcheck -u root -p --repair your_database

# Check status of a specific table:
mysqlcheck -u root -p --check your_database your_table

# For MyISAM tables, use myisamchk (must be done offline):
mysql -u root -p -e "SET GLOBAL myisam_recover_options = 'BACKUP,FORCE';" 
sudo systemctl restart mysql

# List available backups:
ls -la /path/to/backups/

# Restore a full database backup (example with mysqldump):
mysql -u root -p your_database < /path/to/backup.sql

# Restore a specific table from a backup (extract relevant SQL):
grep -A 1000 "CREATE TABLE \`your_table\`" /path/to/backup.sql | grep -B 1000 "INSERT INTO \`other_table\`" | mysql -u root -p your_database

# Or use Percona XtraBackup for InnoDB backups:
xtrabackup --prepare --target-dir=/path/to/backup/
xtrabackup --copy-back --target-dir=/path/to/backup/

# Check for missing InnoDB table files:
ls -la /var/lib/mysql/your_database/ | grep -E "\.ibd$"

# If .ibd file is missing but table exists in SHOW TABLES:
# Option 1: Drop and recreate the table from backup
mysql -u root -p your_database -e "DROP TABLE your_table;"
# Then restore from backup (see previous step)

# Option 2: Force InnoDB recovery (advanced, requires downtime):
sudo systemctl stop mysql
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add: innodb_force_recovery=1
sudo systemctl start mysql
mysql -u root -p your_database -e "SHOW TABLES;"  # Check recovery
# After confirming tables are visible, dump the database
mysqldump -u root -p your_database > recovery.sql
# Remove the innodb_force_recovery setting and restart
sudo systemctl restart mysql