Fixing WordPress database error “Table doesn’t exist” after site migration prefix mismatch

by

Root Cause Diagnosis

WordPress builds every table name from the $table_prefix variable in wp-config.php, so a migrated site throws Table 'dbname.wp_options' doesn't exist when that value does not match the prefix on the tables in the imported database. Common triggers are a wp-config.php copied from a different site or template, a prefix changed during migration, and MySQL case-sensitivity differences between source and target (WP_options vs wp_options on Linux). A related failure is a partial import that never created some tables. When a prefix is renamed, the prefix-dependent keys stored inside the tables (wp_user_roles, wp_capabilities) also have to be updated, or logins succeed but every user loses permissions.

Quick Fix

Find the prefix the database actually uses and set it in the config.

# List actual tables
mysql -u DBUSER -p DBNAME -e "SHOW TABLES LIKE '%options';"

# Set the matching prefix (example: tables are named wpx7_options)
wp config set table_prefix wpx7_ --type=variable --path=/var/www/html
wp db check --path=/var/www/html

Without WP-CLI, edit wp-config.php directly:

$table_prefix = 'wpx7_';   // must match the prefix in SHOW TABLES, including the trailing underscore

Step-by-Step Resolution

1. Read the exact table name WordPress is requesting

Enable logging in wp-config.php (place it above the “That’s all, stop editing” line):

define('WP_DEBUG', true);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);

tail -n 20 /var/www/html/wp-content/debug.log

The error string Table 'DBNAME.PREFIX_options' doesn't exist shows the prefix and the database WordPress is using.

2. Confirm the database credentials point to the right database

grep -E "DB_NAME|DB_HOST|table_prefix" /var/www/html/wp-config.php
mysql -u DBUSER -p -h DBHOST -e "SHOW DATABASES;"

If DB_NAME refers to an empty or wrong database, fix that first. No prefix change will help.

See also  Resolving WordPress REST API error 401 unauthorized during application password sync

3. List the real prefix(es) in the database

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'DBNAME'
  AND table_name LIKE '%\_options';

Compare the result with $table_prefix. Two or more results mean several installs share one database, so make sure you use the prefix that belongs to this site.

4. Choose a resolution

Option A: change the config to match the database (preferred). Set $table_prefix to the value from step 3. It requires no data changes and is the least risky.

Option B: rename the tables to match the config. Use this only if the prefix must stay as configured (for example wp_). Back up first:

mysqldump -u DBUSER -p --single-transaction DBNAME > /root/DBNAME-pre-rename.sql

Generate the rename statements. Replace OLD_ and NEW_ with the real prefixes, keeping the trailing underscore.

SELECT CONCAT('RENAME TABLE `', table_name, '` TO `NEW_', SUBSTRING(table_name, LENGTH('OLD_')+1), '`;')
FROM information_schema.tables
WHERE table_schema = 'DBNAME'
  AND table_name LIKE 'OLD\_%';

Run the generated RENAME TABLE statements. Then update the prefix-dependent data:

UPDATE NEW_options
SET option_name = REPLACE(option_name, 'OLD_user_roles', 'NEW_user_roles')
WHERE option_name = 'OLD_user_roles';

UPDATE NEW_usermeta
SET meta_key = CONCAT('NEW_', SUBSTRING(meta_key, LENGTH('OLD_')+1))
WHERE meta_key LIKE 'OLD\_%';

The second statement covers capabilities, user_level, user-settings, user-settings-time, dashboard_quick_press_last_post_id, and persisted_preferences. Multisite installs also have per-site tables named PREFIX2_posts and similar, plus PREFIX2_user_roles in each site’s options table. Adapt the statements accordingly.

5. Check for missing tables

A complete single-site install has 12 core tables: commentmeta, comments, links, options, postmeta, posts, term_relationships, term_taxonomy, termmeta, terms, usermeta, users.

wp db tables --path=/var/www/html
wp core verify-checksums --path=/var/www/html
wp db check --path=/var/www/html

If core tables are missing, the import was incomplete. Re-import from the original dump (see edge cases below) rather than creating tables by hand.

See also  How to Clean Orphaned wp_postmeta Rows Safely Using Direct SQL

6. Clear caches and confirm

wp cache flush --path=/var/www/html
wp option get siteurl --path=/var/www/html
wp user list --fields=ID,user_login,roles --path=/var/www/html

If roles is empty for all users, the user_roles and capabilities keys still carry the old prefix. Repeat the prefix-dependent updates from step 4.

Why This Fails for Some Users

  • Case sensitivity on Linux: With the default lower_case_table_names=0, table names are case-sensitive. A dump from Windows or macOS (where the value is 1 or 2) can create WP_Options or mixed-case names that WordPress does not find as wp_options. Rename the tables to lowercase, or lowercase the dump before importing. On MySQL 8.0 the setting can only be changed when the data directory is initialized, so changing it afterward stops the server from starting.
  • Prefix set outside wp-config.php:
    • Docker images that use WORDPRESS_TABLE_PREFIX regenerate the config from the environment variable.
    • Bedrock and Roots setups read DB_PREFIX from .env.
    • Some hosts inject the prefix from a control-panel template. Editing the file has no effect if it is regenerated on start.
  • Wrong wp-config.php loaded: WordPress also reads a wp-config.php one directory above the web root when none exists in the web root. Check with wp config path. Stale copies (wp-config-sample.php, backups in the parent directory) cause edits to appear ineffective.
  • Incomplete import:
    • Timeouts and a low max_allowed_packet silently stop large imports partway.
    • ERROR 1071 (key too long) and ERROR 1273 (unknown collation such as utf8mb4_0900_ai_ci or utf8mb4_unicode_520_ci on older MariaDB) abort the affected CREATE TABLE statements.
    • Re-import with mysql --force only after fixing the collation, or with sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' on the dump.
  • Tables exist under another schema: A dump created with CREATE DATABASE or USE olddb statements restores into the old database name. Check SHOW DATABASES; for a second database holding the tables.
  • Insufficient privileges: A DB user with SELECT on some tables but not others, or grants on a different database name, gives “doesn’t exist” errors on tables that are present. Verify with SHOW GRANTS FOR 'DBUSER'@'localhost';.
  • Plugins with hardcoded wp_: Custom queries that hardcode wp_ instead of $wpdb->prefix fail after any prefix change. The error names the plugin’s file in the debug log, and the fix is a plugin update or a code change.
  • Search-replace tools that rewrite the prefix: A blanket replace of wp_ across a dump also corrupts strings, serialized data lengths, and column names such as wp_postmeta references in content. Change the prefix only through table renames and the targeted updates above.
  • Persistent object cache: Redis or Memcached keyed to the old prefix or salt can return stale option data after the fix. Flush the cache, and check WP_CACHE_KEY_SALT if it is defined.
See also  WooCommerce Webhook Disabled Automatically Due to Delivery Failure: How to Reset

Leave a Comment