Problem with dbDelta

  • on activation my plugin is generating 751 bytes of unexpected output. The issue is caused by my activation hook which includes the following:

    $charset_collate = $wpdb->get_charset_collate();

$sql[] = "CREATE TABLE {$links_table_name} (
		'id' bigint unsigned NOT NULL auto_increment,
		'cloud_id' char(32) NOT NULL,
		'user_id' bigint unsigned NOT NULL,
		'category_id' tinyint NOT NULL,
		'url' varchar(255) NOT NULL default '',
		'url_hash' char(32) NOT NULL,
		'target' varchar(25) default NULL,
		'rel' varchar(25) default NULL,
		'slug' varchar(255) NOT NULL,
		'name' varchar(255) NOT NULL,
		'description' text,
		'status' tinyint(1) NOT NULL default '1',
		'vote_count' smallint NOT NULL default '0',
		'vote_total' smallint NOT NULL default '0',
		'popularity' mediumint UNSIGNED NOT NULL default '0',
		'embed_service' char(32) default null,
		'embed_status' tinyint(1) default '0',
		'embed_data' text,
		'date_created' datetime NOT NULL,
		'date_updated' timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
		PRIMARY KEY  ('id'),
		UNIQUE 'cloud_id' ('cloud_id'),
		KEY 'user_id' ('user_id'),
		KEY 'category_id' ('category_id'),
		KEY 'url_hash' ('url_hash'),
		KEY 'slug' ('slug'),
		KEY 'name' ('name'(20)),
		KEY 'status' ('status'),
		KEY 'vote_count' ('vote_count'),
		KEY 'vote_total' ('vote_total'),
		KEY 'popularity' ('popularity'),
		KEY 'date_created' ('date_created'),
		KEY 'date_updated' ('date_updated')
		) {$charset_collate};";
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql);

    Looking at my error log I’m seeing the following errors:

    [18-Mar-2019 08:54:25 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘()’ at line 1 for query ALTER TABLE EJcStfaZbp_links ADD() made by activate_plugin
    [18-Mar-2019 08:54:25 UTC] PHP Notice: Undefined index: column_name in /wp-admin/includes/upgrade.php on line 2713
    [18-Mar-2019 08:54:25 UTC] PHP Notice: Undefined index: index_columns in /wp-admin/includes/upgrade.php on line 2685
    [18-Mar-2019 08:54:25 UTC] PHP Notice: Undefined index: index_name in /wp-admin/includes/upgrade.php on line 2682
    [18-Mar-2019 08:54:25 UTC] PHP Notice: Undefined index: index_type in /wp-admin/includes/upgrade.php on line 2676

    I’m assuming there’s an error in my SQL syntax but I can’t see the error, am I missing something?

    Thanks in advance.

    • This topic was modified 5 years, 8 months ago by Venutius.
    • This topic was modified 5 years, 8 months ago by Venutius.
    • This topic was modified 5 years, 8 months ago by Venutius.
    • This topic was modified 5 years, 8 months ago by Venutius.
Viewing 5 replies - 1 through 5 (of 5 total)

  • I’m not sure if this is the only error, but this:

    $sql[] =

    should be:

    $sql =

    The $sql varibel should be a string, not an array.

    If you’re concerned about the SQL query itself, it’s best off running it in phpMyAdmin (or which ever other SQL manager you have access to) to see what errors are produced, if any.

    Thread Starter Venutius

    (@venutius)

    Thanks, I’ll give that a try

    Thread Starter Venutius

    (@venutius)

    I checked this and it should be an array, as I’m adding multiple queries, it’s only this one that’s causing the issue.

    OK, so what happens when you run that query in phpMyAdmin? What errors re reported? What goes wrong? Even if you can echo out the query and paste it into phpMyAdmin you’ll see what is going on.

    If you’re going to narrow it down like this, you need some sort of reporting to know what the actual error is, because even with the query, it could be just about anything.

    Thread Starter Venutius

    (@venutius)

    It’s a bit odd, if I drop the table and run the query it runs without an error, obviously running the query with the table still in place would simply throw an error as the table is there already, that’s why I’m trying to use dbDelta but it seems dbDelta is misinterpreting the data in some way and thinking a new table needs to be added even though it’s already there. I think as a workaround I’m going to check if the table is there and only add it to the bdDelta command if it’s missing.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Problem with dbDelta’ is closed to new replies.