Debugging Why WordPress Won’t Create Plugin Tables On Activation

July 11th, 2013 - Posted by Steve Marks to MySQL, PHP, Web Development.

I’ve wrote small-ish WordPress plugins in the past, however at the moment I’m working on something a little more girthy (can a WordPress plugin be girthy?) where I’m getting to experiment with more of the advanced features.

One of these features I’m getting to play with is creating custom tables when the plugin is activated, and then ensuring they’re schema is kept up to date when the plugin is updated. This can be easily managed by using the dbDelta() function as explained in the WordPress Codex regarding creating tables.

What I have found however whilst trying this out is that it’s very hard to debug when something goes wrong. In my case the plugin was claiming that it had been activated successfully, yet the tables did not exist, nor were there any errors in sight, even with WP_DEBUG set to TRUE in my wp-config.php file.

After going through the ordeal of trying to debug this, I wanted to give a couple of tips should others find themselves in the same situation:

1. Check, Double-Check and Triple Check the Codex

There are four key bullet points in the codex that specify the exact criteria your query must match in order to be executed. To confirm these, they are as follows:

- You must put each field on its own line in your SQL statement.
- You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
- You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
- You must not use any apostrophes or backticks around field names.

If you can 100% say that your query passes all of the above criteria there is another thing you can try that may aid in your debugging.

2. Check for MySQL Errors

I’m going to add my own bullet point to the ones above:

- Your query must be valid.

Seems obvious right? Well… not really. If you look inside the dbDelta() function you’ll see that it’s doing lots of weird and wonderful things with your query; looking for certain words, exploding strings left, right, and centre, and lots more. Who know’s the final query that it’s actually executing.

So what we can do? To demonstrate I’ve included a snippet below containing a simple (and invalid) MySQL query where I attempt to create a table:

global $wpdb;

// Create tables for storing statistics
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

// Create table to record individual feeds being ran
$table_name = $wpdb->prefix . "people";

$sql = "CREATE TABLE $table_name (
		first_name VARCHAR(50) DEFAULT '' NOT NULL,
		last_name VARCHAR(50) DEFAULT '' NOT NUL,
dbDelta( $sql );

echo $wpdb->last_error;

The people paying attention will notice that there is an error in the query above. Can you spot it? I’ll leave you guessing for now, however note what we’ve got at the end of the query. By outputting $wpdb->last_error we will then be able to know what the error is that’s preventing the tables from being created.

Once you’ve followed the two steps above I hope that all doubt and confusion will be erased. Personally, I was especially surprised that no errors were output, even when WP_DEBUG was activated.

This entry was posted on Thursday, July 11th, 2013 at 6:18 pm by +Steve Marks and is filed under MySQL, PHP, Web Development. You can follow any responses to this entry through the RSS 2.0 feed.

Fear not, we won't publish this

Comments (0)

No comments have been left yet. Be the first