Table not updating with wordpress dbdelta

2 comments

1. You’ve used the dbDelta function incorrectly.

   • You have to put each field on its own line in your SQL statement.

   • You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.

   • You must use the keyword KEY rather than its synonym INDEX and you must include at least one KEY.

   http://hungred.com/how-to/wordpress-dbdelta-function/

2. The code itself of the dbDelta function is very complex. But with trial-and-error, I’ve noticed some behaviors of the dbDelta function update behavior:

   • It doesn’t rename the Primary Key column
   • It doesn’t remove a column
   • If you try to rename an old column, it will just create a new column and keep the old one.

   Long explanation

   dbDelta is a function that people usually tell to hook at register_activation_hook, but this doesn’t fire on plugin updates, so it wouldn’t create the tables for your users that are upgrading an existing install. So the first thing to get dbDelta updates to work, is to hook it on admin_init.

   However, this is a pretty heavy function, so it must only run when needed. For this, you can keep a my_plugin_db_version option in the database, and use it to check if you should run the dbDelta or not.

   If you already have an autoloaded option being loaded, it’s even better to use that, as the check will have zero performance impact, as autoloaded options are loaded in-memory at the beginning of WordPress life-cycle.

   This, in code, can be expressed as:

   add_action('admin_init', 'my_plugin_maybe_run_delta');
   
   function my_plugin_maybe_run_delta() {
     // Here we are not using an existing autoloaded option to keep the example short
     $dbVersion = get_option('my_plugin_db_version');
   
     // Early bail: No need to run dbDelta
     if ($dbVersion === '1.0.0') {
       return;
     }
   
     // dbDelta here...
   
     // Now set the db version so it can early bail next time...
     update_option('my_plugin_db_version', '1.0.0', false);
   }
   

   Now let’s suppose that I have this dbDelta on version 1.0 of my plugin:

   global $wpdb;
   require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
   
   $tableName = $wpdb->prefix . 'foo';
   $charset_collate = $wpdb->get_charset_collate();
   
   $sql = "CREATE TABLE $tableName (
     id bigint(20) NOT NULL AUTO_INCREMENT,
     event longtext NOT NULL,
     PRIMARY KEY  (id)
   ) $charset_collate;";
   
   dbDelta($sql);
   
   // Some error happened during dbDelta.
   if (!empty($lastErrorBefore) && $lastErrorBefore !== $wpdb->last_error) {
       // handle error
   }
   

   This produces the following DDL in the database:

   CREATE TABLE `wp_foo` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `event` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   

   Now I decide to rename id to event_id, so I change the dbDelta and release the version 1.1 of the plugin:

   $sql = "CREATE TABLE $tableName (
     event_id bigint(20) NOT NULL AUTO_INCREMENT,
     event longtext NOT NULL,
     PRIMARY KEY  (event_id)
   ) $charset_collate;";
   

   This delta won’t change the db structure for users that are upgrading the plugin, they will have the primary key as id, while new installs will have the primary key as event_id:

   CREATE TABLE `wp_foo` (
     `event_id` bigint(20) NOT NULL AUTO_INCREMENT,
     `event` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     PRIMARY KEY (`event_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   

   So rule #1 of dbDelta: It doesn’t renames the Primary Key column.

   Now, on version 1.2, I want to rename event to events, so I modify the dbDelta like this:

   $sql = "CREATE TABLE $tableName (
     event_id bigint(20) NOT NULL AUTO_INCREMENT,
     events longtext NOT NULL,
     PRIMARY KEY  (event_id)
   ) $charset_collate;";
   

   This will produce the following result for users that upgrade from 1.1 to 1.2:

   CREATE TABLE `wp_foo` (
     `event_id` bigint(20) NOT NULL AUTO_INCREMENT,
     `event` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     `events` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     PRIMARY KEY (`event_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   

   While producing the following result for fresh installs:

   CREATE TABLE `wp_wpstg_analytics` (
     `event_id` bigint(20) NOT NULL AUTO_INCREMENT,
     `events` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     PRIMARY KEY (`event_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   

   While producing the following result for users that use the plugin since 1.0:

   CREATE TABLE `wp_wpstg_analytics` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `event` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     `events` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
   

   Which is a pretty reasonable behavior when you come to think about it. If you rename the column event to events, you can still migrate the data from the old column to the new for previous installs. Just remember to never rename the column that is assigned as Primary Key.

Comments are closed.