Wordpress register_activation_hook table creation not working

Question

I've created a simple Class for my wordpress plugin. The main file looks like this:

<?php
/*
 * Plugin Name: Codes
 * Description: Generates a coupon code after checkout
 * Version: 0.1.0
 * Author: XXX
 */

require_once('inc/DatabaseUtils.php');

$codes = new Codes();

class Codes {

    private $databaseUtils;

    /**
     * Create a new instance 
     *
     * @param null
     * @return void
     */
    function __construct() {
        $this->regsiter_hooks();
        $this->databaseUtils = new DatabaseUtils();
    }

    /**
     * Called on plugin activation
     *
     * @param null
     * @return void
     */
    function activate() {
        $this->databaseUtils->setup_codes_table();
    }

    /**
     * Called on plugin deactivation
     *
     * @param null
     * @return void
     */
    function deactivate() {

    }

    /**
     * Register plugin hooks
     *
     * @param null
     * @return void
     */
    function regsiter_hooks() {
        register_activation_hook(__File__, array($this, 'activate'));
        register_deactivation_hook(__File__, array($this, 'deactivate'));
    }
}

In the folder ´inc´ I have a DatabaseUtils.php file. This is a class with a function to create a table in the wordpress database after activating the plugin. Here the content:

<?php

class DatabaseUtils {

    function setup_codes_table() {
        global $wpdb;
        global $charset_collate;
        $table_name = $wpdb->prefix . 'pin';
         $sql = "CREATE TABLE IF NOT EXISTS $table_name (
          `id` bigint(20) NOT NULL AUTO_INCREMENT,
          `pincode` bignit(128) DEFAULT NOT NULL,
           PRIMARY KEY (`id`)   
        )$charset_collate;";
         require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
         dbDelta( $sql );
    }

}

If you see, I call the function in the activation method in my main file. But when I activate the plugin there is no new table in my database.

Does anyone have a idea why? Is there a way to debug the register_activation_hook?

Thank's for your help!

Answer 1

The SQL for creating tables in dbDelta() has very specific requirements. From the codex:

• 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.
• KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
• You must not use any apostrophes or backticks around field names.
• Field types must be all lowercase.
• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
• You must specify the length of all fields that accept a length parameter. int(11), for example.

IF NOT EXISTS should also not be necessary when using dbDelta(), the purpose of which is to automatically only make changes to the database if they're necessary based on the SQL.

So your SQL should look like this:

$sql = "CREATE TABLE $table_name (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    pincode bigint(128) NOT NULL,
    PRIMARY KEY  (id)   
) $charset_collate;";

Your SQL lacked the 2 spaces after PRIMARY KEY. You also had a typo where it says bignit instead if bigint for pincode, and DEFAULT NOT NULL is not valid SQL, it should just be NOT NULL.

I also removed the backticks and added a space before $charset_collate; to be consistent with the style in the codex, but I'm not sure if they'd cause issues.