dbdelta failing with error: "WordPress database error Table 'wp_2_myPlugin' already exists

Question

I'm upgrading a plugin and having issues with dbdelta.

Here's the original db creation function:

global $jal_db_version;
$jal_db_version = "0.1";

function jal_install() {

   global $jal_db_version;

   global $wpdb;
   $table_name = $wpdb->prefix . "jalPlugin";      
   $sql = "CREATE TABLE " . $table_name . " (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
      hash text NOT NULL,
      uname tinytext NOT NULL,
      UNIQUE KEY id (id)
    );";

   require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
   dbDelta($sql);

   update_option("jal_db_version", $jal_db_version);
}

// Installs db on plugin activation
register_activation_hook(__FILE__,'jal_install');

You'll probably recognize it from the WordPress Codex on Using Tables -- it works beautifully. (I obviously modified the function name so as not to conflict with other plugins).

I'm trying to upgrade my db used for the plugin and having some issues. I thought dbdelta was supposed to be able to see the differences between what you have and what you want and modify a table accordingly. So here's my upgrade script:

global $jal_db_version;
$jal_db_version = "0.2";
function jal_install() {

   global $wpdb;
   $table_name = $wpdb->prefix . "jalPlugin";      
   $sql = "CREATE TABLE " . $table_name . " (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        hash text NOT NULL,
        uIP VARCHAR(55) DEFAULT '' NOT NULL,
        uname tinytext NOT NULL,
        UNIQUE KEY id (id)
    );";

   require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
   dbDelta($sql);

   update_option("jal_db_version", $jal_db_version);

}

function jal_install_update_check() { 
    global $jal_db_version;
    if (get_site_option('jal_db_version') != $jal_db_version) {
        jal_install();
    }
}
add_action('plugins_loaded', 'jal_install_update_check');

I got this code from the same codex page.

The db is not updating and I'm getting an error in the error log:

[05-Feb-2012 20:33:18] WordPress database error Table 'wp_2_jalPlugin' already exists for query CREATE TABLE wp_2_jalPlugin (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        timestamp datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
        hash text NOT NULL,
        uIP VARCHAR(55) DEFAULT '' NOT NULL,
        uname tinytext NOT NULL,
        UNIQUE KEY id (id)
    ) made by require, require_once, require_once, require_once, do_action, call_user_func_array, qrLogin_update_db_check, qrLoginDB_install, dbDelta

It would appear to me that dbdelta is not understanding that it has a job to do...

Any ideas?

...and thanks for your time!

Answer 1

I've never had any luck with dbdelta, it has worked spottily at best, and when I code, that's just not good enough. My method for handling DB changes is to use database versions. So when I create a plugin, I also set the database version, then if I want to update the database, I do a check against the current dbversion (almost always stored in the wp_options table), if it's less than the new DB version, I'll run a series of updates. I'll repeat the process if I release another update that requires database structure changes. This allows users to update the plugin, even from a very old version, and still be assured that the database format will be correct. This method also holds up if the data needs to be transformed and re-entered into the database, as it will update through every structural change as though the plugin were being updated incrementally.