CREATE TABLE with dbDelta does not create table

I based this code from the codex on the docs. I placed it in my main plugin file but it’s not creating the database table. Have I missed something?

    // Database setup and hooks
    
    function core_createdb() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'core_logs';
        $wpdb_collate = $wpdb->collate;
        $sql = 
            "CREATE TABLE {$table_name} (
            timestamp DATE NOT NULL,
            logid INT NOT NULL AUTO_INCREMENT,
            userid INT DEFAULT NULL,
            actiontype TINYTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'undefined',
            userip VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
            actioncontent VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            botactioncomment VARCHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
            botactionmade BOOLEAN DEFAULT '0',
            flaglevel TINYINT DEFAULT '0',
            PRIMARY KEY  (logid),
            KEY useridkey (userid)
            )
            COLLATE {$wpdb_collate}";
    
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    
        $success = empty($wpdb->last_error);
    
        return $success;
    }
    
    // Create Database
    
    add_action('init', 'core_createdb');

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Found a couple of things and am including what I believe will work to correct your issue. (As an aside, you should try and simplify your initial attempts so you can isolate what works and what doesn’t. This is really complex for an initial attempt.)

One thing, you’ll struggle to have a field named timestamp, because timestamp is an SQL Field Type. So when you have timestamp DATE NOT NULL it’s actually confusing it by throwing two SQL field types at it instead of an field heading/name. I actually noticed this because of the colour formatting in SUBLIME TEXT 3 – both timestamp and DATE were the same colour.

Additionally, as per the codex:

Field types must be all lowercase (https://codex.wordpress.org/Creating_Tables_with_Plugins)

Here’s my re-write attempt of your code:

function core_createdb() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'core_logs';
    $wpdb_collate = $wpdb->get_charset_collate();
    $sql = 
        "CREATE TABLE $table_name (
        time_stamp date NOT NULL,
        logid int NOT NULL AUTO_INCREMENT,
        userid int DEFAULT NULL,
        actiontype tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'undefined',
        userip varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
        actioncontent varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        botactioncomment varchar CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        botactionmade boolean DEFAULT 0 NOT NULL,
        flaglevel tinyint(4) DEFAULT 0 NOT NULL,
        PRIMARY KEY  (logid),
        KEY useridkey (userid)
        ) $wpdb_collate;";   
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    $success = empty($wpdb->last_error);
    return $success;
}
// Create Database
add_action( 'init', 'core_createdb' );

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0