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

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.