CREATE TABLE with dbDelta does not create table

Question

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');

Answer 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' );