Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
That's very helpful! Thank you. I'll give it a shot. –  rpophessagr Feb 5 '12 at 21:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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