How to fix ERROR 1177: Can't open table in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 23, 2025· 132 views

MySQL Error 1177 occurs when the database engine cannot find, access, or open a table referenced in a CHECK or FOREIGN KEY constraint. Common causes include non-existent tables, case sensitivity mismatches, corrupted files, or insufficient permissions.

Quick fix

Fixes 1177

USE your_database_name;
SHOW TABLES LIKE 'table_name';
  1. Verify the table exists
  2. Check table name case sensitivity
  3. Use fully qualified table names
  4. Check and repair table file permissions
mysql — error
USE your_database_name;
SHOW TABLES LIKE 'table_name';
ERROR 1177: Can't open table

What this error means

MySQL Error 1177 (ER_CHECK_NO_SUCH_TABLE, SQLSTATE 42000) is triggered when the database engine fails to locate or open a table during constraint validation operations. This error typically occurs during CREATE TABLE or ALTER TABLE statements when MySQL attempts to verify referential integrity by checking if referenced tables exist and are accessible. The error indicates a structural problem: either the table doesn't exist in the expected database, the table files are corrupted or inaccessible, there's a case-sensitivity mismatch between the table reference and actual files, or the MySQL process lacks sufficient permissions to access the table files on the file system.

Typical symptoms

  • CREATE TABLE or ALTER TABLE statements fail with "Can't open table" message
  • Foreign key constraints cannot be created because the referenced table cannot be opened
  • Application encounters "ERROR 1177" when executing DDL operations
  • Table appears in SHOW TABLES but still triggers the error when referenced
  • Different behavior between development and production environments with case-sensitive file systems

Common causes

  1. Table does not exist in the specified database or has been dropped
  2. Case sensitivity mismatch on Unix/Linux systems where table names in code do not match actual file names
  3. Table files (.ibd, .frm, .MYI, .MYD) are corrupted or damaged
  4. MySQL process lacks read/execute permissions on table files or data directory
  5. Referencing a table from another database without fully qualifying the name
  6. Storage engine mismatch or unsupported storage engine for the table
  7. Data dictionary corruption in the MySQL system tables

How to fix "ERROR 1177: Can't open table"

1Verify the table exists

First, confirm that the table you're trying to reference actually exists in the database:

sql
USE your_database_name;
SHOW TABLES LIKE 'table_name';

If the table doesn't appear in the results, you need to either create it or restore it from a backup. If the table exists but still produces the error, proceed to the next steps.

2Check table name case sensitivity

On Unix/Linux systems, table names are case-sensitive by default. Verify that the case of your table reference matches the actual table name exactly:

sql
-- Check current lower_case_table_names setting
SHOW VARIABLES LIKE 'lower_case_table_names';

If the value is 0 (case-sensitive), ensure all table references use the exact case. To make table names case-insensitive globally, set lower_case_table_names = 1 in your MySQL configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf), then restart MySQL:

ini
[mysqld]
lower_case_table_names = 1

Restart MySQL:

bash
sudo systemctl restart mysql
3Use fully qualified table names

When referencing tables in foreign key constraints or cross-database operations, always fully qualify the table name with the database prefix:

sql
-- Instead of this:
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Use this if tables are in different databases:
ALTER TABLE database_a.orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES database_b.customers(id);

-- Verify the table exists in the correct database:
SELECT * FROM database_name.table_name LIMIT 1;
4Check and repair table file permissions

File system permissions may prevent MySQL from accessing table files. Check the ownership and permissions of table files in the MySQL data directory:

bash
# Check current permissions
ls -la /var/lib/mysql/your_database/
ls -la /var/lib/mysql/your_database/table_name.*

# Fix ownership (should be mysql:mysql)
sudo chown -R mysql:mysql /var/lib/mysql/your_database
sudo chown mysql:mysql /var/lib/mysql/your_database/table_name.*

# Fix permissions (644 for data files, 755 for directories)
sudo chmod -R 755 /var/lib/mysql/your_database
sudo chmod 644 /var/lib/mysql/your_database/table_name.*

Then restart MySQL:

bash
sudo systemctl restart mysql
5Check and repair corrupted tables

If table files are corrupted, MySQL may fail to open them. Use CHECK TABLE to diagnose corruption:

sql
CHECK TABLE table_name;

For MyISAM tables, repair with REPAIR TABLE:

sql
REPAIR TABLE table_name;

For InnoDB tables (which don't support REPAIR TABLE), use ALTER TABLE to rebuild:

sql
ALTER TABLE table_name ENGINE=InnoDB;

Alternatively, use the command-line utility for MyISAM tables:

bash
sudo myisamchk -r /var/lib/mysql/database_name/table_name.MYI
6Verify and grant proper permissions

Ensure the user executing the DDL statement has appropriate privileges on the referenced table:

sql
-- Grant necessary privileges
GRANT SELECT, REFERENCES ON your_database.table_name TO 'your_user'@'localhost';

-- For CREATE/ALTER TABLE with foreign keys
GRANT SELECT, REFERENCES ON target_database.target_table TO 'your_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

Verify privileges are set:

sql
SHOW GRANTS FOR 'your_user'@'localhost';

Advanced notes

Temporary Workaround (Use with Caution): In rare cases during migration or repair operations, you can temporarily disable foreign key constraint checking with SET FOREIGN_KEY_CHECKS=0; before your DDL statement. However, this bypasses referential integrity checks and risks data consistency issues. Only use this approach when you fully understand the implications and plan to re-enable it immediately after: SET FOREIGN_KEY_CHECKS=1;

InnoDB vs MyISAM Considerations: InnoDB tables use .ibd files and have stricter constraint validation, while MyISAM uses .MYI and .MYD files. The repair process differs between engines. InnoDB's crash recovery may also resolve access issues automatically after restart.

Docker/Container Deployments: If running MySQL in Docker, ensure file volumes are properly mounted and have correct permissions. Check the container's MySQL process user (usually mysql:mysql) and verify the host's file permissions align with the container.

Hardware Issues: In rare cases, storage hardware problems (disk corruption, I/O errors) can trigger this error. Check system logs with journalctl -xe for disk-related errors and run file system checks if necessary.

Frequently asked questions

What does "1177" mean?
MySQL Error 1177 (ER_CHECK_NO_SUCH_TABLE, SQLSTATE 42000) is triggered when the database engine fails to locate or open a table during constraint validation operations. This error typically occurs during CREATE TABLE or ALTER TABLE statements when MySQL attempts to verify referential integrity by checking if referenced tables exist and are accessible. The error indicates a structural problem: either the table doesn't exist in the expected database, the table files are corrupted or inaccessible, there's a case-sensitivity mismatch between the table reference and actual files, or the MySQL process lacks sufficient permissions to access the table files on the file system.
How do I fix "1177"?
MySQL Error 1177 occurs when the database engine cannot find, access, or open a table referenced in a CHECK or FOREIGN KEY constraint. Common causes include non-existent tables, case sensitivity mismatches, corrupted files, or insufficient permissions.
What causes "1177"?
Table does not exist in the specified database or has been dropped Case sensitivity mismatch on Unix/Linux systems where table names in code do not match actual file names Table files (.ibd, .frm, .MYI, .MYD) are corrupted or damaged
Is the "1177" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading