Wordpress plugin table, cannot create with dbDelta although sql is correct

Question

I am trying to have my Wordpress plugin to create the table with data on activation.

The SQL seems to be executed and is by itself correct (in fact, it works if I copy it manually to the SQL server)

My PHP code is as follows

register_activation_hook( __FILE__, function () {
 global $wpdb;
 $table_name = $wpdb->prefix . "ajax_preview_galleries"; 
 $charset_collate = $wpdb->get_charset_collate();

 //Table definition
$sql =  "CREATE TABLE $table_name (
gallery_id int(10) unsigned NOT NULL AUTO_INCREMENT,
gallery_name varchar(100) COLLATE utf8_unicode_ci NOT NULL,
gallery_slug varchar(100) COLLATE utf8_unicode_ci NOT NULL,
gallery_selected_terms text COLLATE utf8_unicode_ci NOT NULL,
gallery_select_by tinyint(3) unsigned NOT NULL COMMENT '0: categories only; 1: tags only; 2: both',
gallery_post_count tinyint(3) unsigned NOT NULL,
gallery_custom_class_container varchar(200) COLLATE utf8_unicode_ci NOT NULL,
gallery_custom_class_buttons varchar(200) COLLATE utf8_unicode_ci NOT NULL,
gallery_transition_time int(10) unsigned NOT NULL DEFAULT '500',
gallery_loading_type tinyint(3) unsigned NOT NULL DEFAULT '1',
gallery_navigator_loop tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY  (gallery_id)
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

$res = dbDelta($sql);
});

If I output $res, I get this: Array ( [orhub_ajax_preview_galleries] => Created table orhub_ajax_preview_galleries )

Which would suggest everything is fine. If I check the database, though, the table is not there, and the plugin is unable indeed to store data.

As I said, I tried to output $sql and paste it directly in phpMyAdmin. That worked, so the problem does not seem to be in the query.

What else can be wrong then?

By the way, I tried also maybe_create_table, and that was not working either

Answer 1

You may want to try this function:

$table_name = "ratings";

$table_columns = "id INT(6) UNSIGNED AUTO_INCREMENT,
                    rate tinyint(1) NOT NULL,
                    ticket_id bigint(20) NOT NULL,
                    response_id bigint(20) NOT NULL,
                    created_at TIMESTAMP";

$table_keys = "PRIMARY KEY (id),
                    KEY ratings_rate (rate),
                    UNIQUE KEY ratings_response_id (response_id)";

create_table($table_name, $table_columns, $table_keys);

/**
 * Prevents unnecessary re-creating index and repetitive altering table operations when using WordPress dbDelta function
 *
 * Usage Example:
 *
 * $table_name      = "ratings";
 *
 * $table_columns   = "id INT(6) UNSIGNED AUTO_INCREMENT,
 *          rate tinyint(1) NOT NULL,
 *          ticket_id bigint(20) NOT NULL,
 *          response_id bigint(20) NOT NULL,
 *          created_at TIMESTAMP";
 *
 * $table_keys      = "PRIMARY KEY (id),
 *          KEY ratings_rate (rate),
 *          UNIQUE KEY ratings_response_id (response_id)";
 *
 * create_table($table_name, $table_columns, $table_keys);
 *
 * Things that need to be considered when using dbDelta function :
 *
 * 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.
 * 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.
 *
 * Further information can be found on here:
 *
 * http://codex.wordpress.org/Creating_Tables_with_Plugins
 *
 * @param $table_name
 * @param $table_columns
 * @param null $table_keys
 * @param null $charset_collate
 * @version 1.0.1
 * @author Ugur Mirza Zeyrek
 */
function create_table($table_name, $table_columns, $table_keys = null, $db_prefix = true, $charset_collate = null) {
    global $wpdb;

    if($charset_collate == null)
        $charset_collate = $wpdb->get_charset_collate();
    $table_name = ($db_prefix) ? $wpdb->prefix.$table_name : $table_name;
    $table_columns = strtolower($table_columns);

    if($table_keys)
        $table_keys =  ", $table_keys";

    $table_structure = "( $table_columns $table_keys )";

    $search_array = array();
    $replace_array = array();

    $search_array[] = "`";
    $replace_array[] = "";

    $table_structure = str_replace($search_array,$replace_array,$table_structure);

    $sql = "CREATE TABLE $table_name $table_structure $charset_collate;";

    // Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/includes/upgrade.php (we'll have to load this file, as it is not loaded by default)
    require_once (ABSPATH . 'wp-admin/includes/upgrade.php');

    // The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary
    return dbDelta($sql);
}

Answer 2

Got it to work! As it seems, the problems was the comment on one of the columns. In fact, I suspected as much, and I already tried to remove comments, although the one on the gallery_select_by column escaped my attention. Thanks to dingo_d for pointing my attention toward that line!

The WordPress Codex does specify that "the dbDelta function is rather picky, however. For instance:

• 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. 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.

As it seems, these were merely some examples, and dbDelta() has something to complain even about comments. I wasn't able to find a complete list of the "rules" of dbDelta, but at least I got my case to work.

By the way: as mentioned earlier, I used to get this result from dbDelta() when the table was NOT being created

Result: Array ( [orhub_ajax_preview_galleries] => Created table orhub_ajax_preview_galleries ) 

Now that the plugin is working, instead, I am getting an empty array. Go figure! This seems rather peculiar to me as it is totally counter-intuitive (as other aspects of dbDelta()), so it's probably good to know and I pointed it out for others who may battle with the same issues.

It seems that, when dealing with dbDelta(), it must be taken into account that "special rules" apply and a query that works elsewhere may not work here (in fact, as I mentioned, my original sql worked for instance when put directly in phpMyAdmin). Results from the function may not be that heplful either...

Answer 3

Ok, so you have illegal characters in your comment in the CREATE TABLE in your gallery_select_by column

Try with:

global $wpdb;
$table_name = $wpdb->prefix . "ajax_preview_galleries";
$charset_collate = $wpdb->get_charset_collate();

//Table definition
$sql =  "CREATE TABLE $table_name (
gallery_id int(10) unsigned NOT NULL AUTO_INCREMENT,
gallery_name varchar(100) COLLATE utf8_unicode_ci NOT NULL,
gallery_slug varchar(100) COLLATE utf8_unicode_ci NOT NULL,
gallery_selected_terms text COLLATE utf8_unicode_ci NOT NULL,
gallery_select_by tinyint(3) unsigned NOT NULL COMMENT '0 - categories only. 1 - tags only. 2 - both',
gallery_post_count tinyint(3) unsigned NOT NULL,
gallery_custom_class_container varchar(200) COLLATE utf8_unicode_ci NOT NULL,
gallery_custom_class_buttons varchar(200) COLLATE utf8_unicode_ci NOT NULL,
gallery_transition_time int(10) unsigned NOT NULL DEFAULT '500',
gallery_loading_type tinyint(3) unsigned NOT NULL DEFAULT '1',
gallery_navigator_loop tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY  (gallery_id)
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

$res = dbDelta($sql);

The thing is, you had : and ; in your comment, and ; was probably signaling to end the sql statement, so you got errors.

I tried searching escaping it, but only found this for string literals, nothing about colon and semicolon.

Hope this helps.

Answer 4

Add this just before your dbDelta:

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

You have to add that line in to explicitly load that portion of core you need to run dbDelta.