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!

Improve this question

1 Answer 1

Reset to default
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.

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.