Foreign key references a column in the parent table that has no index. InnoDB requires an index on the referenced column — a PRIMARY KEY or plain INDEX both work, but the index must exist before the FK is created.. Data type mismatch between the FK column and the referenced column. INT vs BIGINT, or SIGNED vs UNSIGNED, or varchar(50) vs varchar(100) — InnoDB requires an exact match including signedness and length.. Character set or collation mismatch between child and parent columns. A utf8mb4_unicode_ci FK referencing a utf8mb4_general_ci parent column triggers errno 150.. The .ibd tablespace file already exists on disk from a previously dropped table that was not fully cleaned up. This happens after a crash during DROP TABLE or when innodb_file_per_table is toggled.. Disk is full or the InnoDB system tablespace (ibdata1) has hit its max size. InnoDB cannot allocate pages for the new table's metadata.. The referenced parent table uses a different storage engine (MyISAM) than the child table (InnoDB). Cross-engine foreign keys are not supported.. Table name or column name conflicts with a MySQL reserved word and is not backtick-quoted in the DDL statement.

Step 1: Run `SHOW ENGINE INNODB STATUS\G` within seconds of the error. Look for the section labeled LATEST FOREIGN KEY ERROR — it contains the exact column, table, and reason. This diagnostic is overwritten by the next InnoDB error, so capture it immediately.. Step 2: Identify the errno from the original error message. `errno: 150` = FK constraint issue. `errno: 13` = OS permission denied. `errno: 28` = disk full. Each errno points to a different fix path.. Step 3: For errno 150, verify index existence on the parent column: `SHOW INDEX FROM parent_table WHERE Column_name = 'referenced_col';` — if empty, add one: `ALTER TABLE parent_table ADD INDEX idx_ref_col (referenced_col);`. Step 4: Compare column definitions exactly: `SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME IN ('parent_table', 'child_table') AND COLUMN_NAME IN ('fk_col', 'referenced_col');` — types, signedness, charset, and collation must all match.. Step 5: If a stale .ibd file exists, check the datadir: `ls -la /var/lib/mysql/your_db/table_name.ibd` — if present after the table was dropped, remove it manually and retry. Back up first.. Step 6: Check disk space and tablespace size: `df -h /var/lib/mysql && SELECT FILE_NAME, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS size_mb FROM information_schema.FILES WHERE TABLESPACE_NAME = 'innodb_system';`. Step 7: In dbt, if the error occurs during `dbt run --full-refresh`, check the generated DDL in `target/run/` for the failing model. Add `{{ config(on_schema_change='sync_all_columns') }}` or drop the FK constraint in a pre-hook before the refresh.

High severityschema

MySQL Error:
1005

Impact

Any dbt full-refresh, Flyway migration, or ADF pipeline that creates or recreates tables with foreign keys will hard-fail. Downstream tables go stale, incremental models that depend on the failed table skip rows or error out, and SLA clocks start ticking on every report that reads from those tables.

Error 1005 is a hard failure — the table is not created, so all subsequent INSERT, SELECT, or COPY operations against that table fail immediately. In dbt, downstream models that ref() the failed model are skipped entirely. In ADF, the Copy Activity fails and the pipeline enters a failed state. There is no automatic retry that will resolve this. Every minute the table is missing, downstream reports and dashboards serve stale data or show load errors. If the failed table is on a critical path, SLA breaches start accumulating from the moment the DDL fails.

What does this error mean?

MySQL raises error 1005 when a CREATE TABLE statement fails at the InnoDB storage-engine level. The error message itself is generic — the real cause is hidden behind an errno value (most commonly errno 150 for foreign key problems). In data pipelines this typically surfaces during dbt full-refresh runs, schema migrations executed by Flyway or Liquibase, or when an ADF Copy Activity creates a staging table in MySQL. The symptom is immediate: the DDL statement fails, the transaction rolls back, and any downstream INSERT or COPY operations that depend on that table fail with 'table does not exist'. Always check the errno suffix and run SHOW ENGINE INNODB STATUS immediately — the diagnostic window is short because the next error overwrites it.

Common causes

1Foreign key references a column in the parent table that has no index. InnoDB requires an index on the referenced column — a PRIMARY KEY or plain INDEX both work, but the index must exist before the FK is created.
2Data type mismatch between the FK column and the referenced column. INT vs BIGINT, or SIGNED vs UNSIGNED, or varchar(50) vs varchar(100) — InnoDB requires an exact match including signedness and length.
3Character set or collation mismatch between child and parent columns. A utf8mb4_unicode_ci FK referencing a utf8mb4_general_ci parent column triggers errno 150.
4The .ibd tablespace file already exists on disk from a previously dropped table that was not fully cleaned up. This happens after a crash during DROP TABLE or when innodb_file_per_table is toggled.
5Disk is full or the InnoDB system tablespace (ibdata1) has hit its max size. InnoDB cannot allocate pages for the new table's metadata.
6The referenced parent table uses a different storage engine (MyISAM) than the child table (InnoDB). Cross-engine foreign keys are not supported.
7Table name or column name conflicts with a MySQL reserved word and is not backtick-quoted in the DDL statement.

How to fix it

1Step 1: Run `SHOW ENGINE INNODB STATUS\G` within seconds of the error. Look for the section labeled LATEST FOREIGN KEY ERROR — it contains the exact column, table, and reason. This diagnostic is overwritten by the next InnoDB error, so capture it immediately.
2Step 2: Identify the errno from the original error message. `errno: 150` = FK constraint issue. `errno: 13` = OS permission denied. `errno: 28` = disk full. Each errno points to a different fix path.
3Step 3: For errno 150, verify index existence on the parent column: `SHOW INDEX FROM parent_table WHERE Column_name = 'referenced_col';` — if empty, add one: `ALTER TABLE parent_table ADD INDEX idx_ref_col (referenced_col);`
4Step 4: Compare column definitions exactly: `SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME IN ('parent_table', 'child_table') AND COLUMN_NAME IN ('fk_col', 'referenced_col');` — types, signedness, charset, and collation must all match.
5Step 5: If a stale .ibd file exists, check the datadir: `ls -la /var/lib/mysql/your_db/table_name.ibd` — if present after the table was dropped, remove it manually and retry. Back up first.
6Step 6: Check disk space and tablespace size: `df -h /var/lib/mysql && SELECT FILE_NAME, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS size_mb FROM information_schema.FILES WHERE TABLESPACE_NAME = 'innodb_system';`
7Step 7: In dbt, if the error occurs during `dbt run --full-refresh`, check the generated DDL in `target/run/` for the failing model. Add `{{ config(on_schema_change='sync_all_columns') }}` or drop the FK constraint in a pre-hook before the refresh.

Example log output

ERROR 1005 (HY000) at line 42: Can't create table `analytics`.`fact_orders` (errno: 150 "Foreign key constraint is incorrectly formed")
2026-05-11T08:14:03.412Z LATEST FOREIGN KEY ERROR: Cannot find an index in the referenced table `analytics`.`dim_customers` where the referenced columns appear as the first columns. Column `customer_id` in child table is INT UNSIGNED, in parent table is INT SIGNED.

Frequently asked questions

What does errno 150 mean in MySQL error 1005?

Errno 150 specifically means a foreign key constraint failed during CREATE TABLE. The three most common causes are: (1) no index on the parent column, (2) data type mismatch between FK and referenced column, (3) charset or collation mismatch. Run `SHOW ENGINE INNODB STATUS;` and look for the LATEST FOREIGN KEY ERROR section — it names the exact column and reason.

How do I fix MySQL error 1005 in a dbt full-refresh?

dbt full-refresh drops and recreates the table, which re-runs the DDL including any FK constraints. Check the generated SQL in `target/run/` for the failing model. Common fixes: ensure the parent table is built first (use `ref()` correctly), match column types exactly in your schema.yml, or add a pre-hook that drops the FK before the refresh and a post-hook that recreates it.

MySQL error 1005 but I am not using foreign keys — what else causes it?

Without FKs, error 1005 is typically caused by a leftover .ibd file on disk (errno 1), a full disk (errno 28), or an OS permission problem on the MySQL data directory (errno 13). Check `ls -la /var/lib/mysql/your_db/` for stale files, `df -h` for disk space, and `ls -ld /var/lib/mysql/your_db/` for directory permissions.

Does MySQL error 1005 retry automatically in ADF or dbt?

No. Error 1005 is a DDL failure, not a transient connection error — retrying the same CREATE TABLE statement will produce the same result. ADF retry policies and dbt retries will not help. You must fix the underlying schema definition, FK reference, or disk issue before re-running the pipeline.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html