High severityschema
MySQL Error:
1146
What does this error mean?
MySQL raises error 1146 when a query references a table that does not exist in the target database. In data-pipeline context this typically surfaces during an ADF copy activity, a dbt run, or a Power BI dataset refresh that executes a SQL query against a MySQL source. The engineer sees the pipeline fail with `Table 'schema.table_name' doesn't exist`. Root causes range from a migration that was never applied, a table dropped by a failed dbt run, schema drift between environments, or a simple typo in the fully-qualified table name. On Linux-based MySQL servers, filesystem case sensitivity adds another failure mode where `Orders` and `orders` resolve to different physical files.
Common causes
- 1A dbt model or migration that creates the table has not been run in this environment — common after promoting code from dev to prod without running `dbt run --select +model_name`.
- 2The table was dropped by a partially-failed migration or a `dbt run --full-refresh` that errored midway, leaving the old table gone and the new one not yet created.
- 3The query references the wrong database prefix — e.g. `analytics.orders` instead of `analytics_prod.orders` — because an environment variable or dbt profile target was misconfigured.
- 4Case sensitivity mismatch on Linux MySQL: the DDL created `Customer_Orders` but the query references `customer_orders`. On Linux with `lower_case_table_names=0`, these are distinct.
- 5A scheduled `DROP TABLE IF EXISTS` + `CREATE TABLE` pattern in an ETL job failed between the drop and recreate, leaving a window where the table does not exist and concurrent queries hit 1146.
- 6InnoDB tablespace file (.ibd) was manually deleted or corrupted on disk — MySQL's data dictionary still references the table but the storage engine cannot open it, producing a variant of 1146.
How to fix it
- 1Step 1: Confirm the table is genuinely missing: `SHOW TABLES FROM your_database LIKE 'your_table';` — if empty, the table does not exist in this database.
- 2Step 2: Search all databases for the table: `SELECT table_schema, table_name FROM information_schema.tables WHERE table_name = 'your_table';` — this reveals if it lives in a different schema.
- 3Step 3: Check case sensitivity settings: `SHOW VARIABLES LIKE 'lower_case_table_names';` — value 0 means case-sensitive on Linux. Compare the exact casing in your query vs the filesystem under `/var/lib/mysql/your_database/`.
- 4Step 4: Re-run the migration or dbt model that creates the table: `dbt run --select +your_model --target prod` (the `+` ensures upstream dependencies run first).
- 5Step 5: If the table was dropped accidentally, restore from the most recent backup: `mysql -u root -p your_database < /backups/your_table_ddl_and_data.sql` or use point-in-time recovery from binlogs.
- 6Step 6: For InnoDB tablespace issues, discard and re-import: `ALTER TABLE your_table DISCARD TABLESPACE;` then copy the .ibd file back and `ALTER TABLE your_table IMPORT TABLESPACE;`.
- 7Step 7: After restoring, verify downstream dependencies still work: re-trigger the ADF pipeline or Power BI refresh manually and confirm no further 1146 errors appear in the activity output.
Example log output
ERROR 1146 (42S02) at line 1: Table 'analytics_prod.stg_customer_orders' doesn't exist
[2026-05-11 08:14:22 UTC] ADF pipeline 'P_Load_CustomerOrders' activity 'Copy_MySQL_to_ADLS' failed: {"errorCode":"2200","message":"ErrorCode=MySqlOperationFailed, Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException, Message=Error 1146: Table 'analytics_prod.stg_customer_orders' doesn't exist"}
[2026-05-11 08:14:23 UTC] Downstream activity 'Refresh_PBI_Dataset' skipped due to upstream failure.