<p>Error 1809 occurs when MySQL tries to create or alter a table that already resides in the shared InnoDB system tablespace while file-per-table is expected.</p>
<p>MySQL Error 1809 ER_TABLE_IN_SYSTEM_TABLESPACE is raised when a CREATE, ALTER, or IMPORT operation would place a table inside the shared system tablespace after it previously used its own .ibd file. Move or drop the orphan .ibd file, enable innodb_file_per_table, or re-import the table correctly to fix the issue.</p>
Table '%s' in system tablespace
Error 1809 with SQLSTATE HY000 prints: Table '%s' in system tablespace. MySQL blocks the operation because it detects a mismatch between the table's metadata and the physical tablespace where its data should reside.
The message appears most often during CREATE TABLE LIKE, ALTER TABLE, or DISCARD/IMPORT TABLESPACE steps when innodb_file_per_table is ON and the target .ibd file exists outside the system tablespace.
The error fires when MySQL sees a table definition that expects its own file-per-table tablespace but finds metadata indicating it lives inside the global ibdata system tablespace. Any DDL touching that table fails until the mismatch is resolved.
Migrations, server upgrades, physical file moves, or improper restores that leave orphan .ibd files or inconsistent metadata are common triggers.
Ignoring the condition prevents schema changes, blocks imports, and risks data corruption if you force MySQL to use mismatched storage formats. Resolving the inconsistency restores normal DDL operations and ensures InnoDB integrity.
- CREATE TABLE returns 1809 instantly.
- ALTER TABLE ... ENGINE=InnoDB fails with the same message.
- SHOW WARNINGS shows ER_TABLE_IN_SYSTEM_TABLESPACE.
These clues confirm the table metadata and physical files are out of sync.
Dropped tables with file-per-table may leave the .ibd file on disk if the server crashed before unlinking it. Re-creating the table hits 1809.
Using DISCARD TABLESPACE or IMPORT TABLESPACE steps out of order can register the table in the system tablespace while its .ibd sits externally.
Upgrading with the setting OFF, then re-enabling it later, leaves older tables mapped to ibdata while new DDL expects file-per-table.
Copying .ibd files between servers without matching the corresponding .frm or dictionary entries confuses InnoDB and triggers the error.
Raised when a tablespace belongs to a different storage engine than expected.
Occurs when the tablespace name in a CREATE/ALTER statement is invalid or already used.
Returned when MySQL cannot find the table definition; sometimes appears after improper tablespace imports.
Dropping and recreating works only if you also remove the orphan .ibd file. Otherwise, MySQL still sees the mismatch and raises the error.
Turning the setting OFF may let the table reside in the system tablespace, but it hides the root cause and is not recommended for production.
Yes. mysqldump exports logical SQL, so importing the dump recreates tables cleanly without touching .ibd files, preventing the mismatch.
Galaxy logs every DDL statement, making it easy to trace when a table was discarded or imported, and its AI copilot suggests the exact ALTER commands to resolve the issue.
.avif)
.avif){kind=logo}
