0

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!

1 Answer 1

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.

1
  • That's very helpful! Thank you. I'll give it a shot. Feb 5, 2012 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.