DB Query not working in Plugin

I am working on a plugin. And I want to create a table via the plugin. So I am trying execute following SQL Query.

global $wpdb;
$createSQL              =   "
CREATE TABLE `". $wpdb->prefix ."_book_ratings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `book_id` bigint(20) UNSIGNED NOT NULL,
  `rating` float(3.1) UNSIGNED NOT NULL,
  `user_ip` varchar(32) NOT NULL
) ENGINE=InnoDB" . $wpdb->get_charset_collate() . " AUTO_INCREMENT=1;
";

require( ABSPATH . '/wp-admin/includes/upgrade.php' );
dbDelta( $createSQL );

When I deactivate the plugin and when re-activating the plugin I am getting following error.

The plugin generated 464 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.

Please let me know where I am wrong.

I am working on a plugin. And I want to create a table via the plugin. So I am trying execute following SQL Query.

global $wpdb;
$createSQL              =   "
CREATE TABLE `". $wpdb->prefix ."_book_ratings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `book_id` bigint(20) UNSIGNED NOT NULL,
  `rating` float(3.1) UNSIGNED NOT NULL,
  `user_ip` varchar(32) NOT NULL
) ENGINE=InnoDB" . $wpdb->get_charset_collate() . " AUTO_INCREMENT=1;
";

require( ABSPATH . '/wp-admin/includes/upgrade.php' );
dbDelta( $createSQL );

When I deactivate the plugin and when re-activating the plugin I am getting following error.

The plugin generated 464 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin.

Please let me know where I am wrong.

• plugins
• plugin-development
• database
• mysql

Share Improve this question asked Oct 22, 2018 at 10:53 user2584538user2584538 12711 silver badge99 bronze badges 3

• “headers already sent” messages generally display when you not start or end PHP tag in file. also remove white space before start PHP tag or Ending PHP tag. – Mehul Commented Oct 22, 2018 at 11:03
• Do you have error logging enabled? What is this additional output you get? – kero Commented Oct 22, 2018 at 11:16
• Sorry I haven't enabled error log. – user2584538 Commented Oct 22, 2018 at 11:18

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Looking at the SQL query in $createSQL,

1. ENGINE=InnoDB" . $wpdb->get_charset_collate() . " AUTO_INCREMENT=1; yields to:

   Error in query (1286): Unknown storage engine 'InnoDBDEFAULT'

   To fix it, add a space between the ENGINE=InnoDB (the storage engine) and " . $wpdb->get_charset_collate() (the charset).

2. You have defined the column id as AUTO_INCREMENT, but the column is not a key, and that yields to the following error:

   Error in query (1075): Incorrect table definition; there can be only one auto column and it must be defined as a key

   To fix it, define the column id as a key using either PRIMARY KEY (id) or KEY(id).

So, here's the fixed code (or SQL query), tried and tested working on WordPress 4.9.8:

$createSQL              =   "
CREATE TABLE `". $wpdb->prefix ."_book_ratings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `book_id` bigint(20) UNSIGNED NOT NULL,
  `rating` float(3.1) UNSIGNED NOT NULL,
  `user_ip` varchar(32) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB " . $wpdb->get_charset_collate() . " AUTO_INCREMENT=1;
";

If that doesn't work for you, follow the guides here, such as use two spaces between the words PRIMARY KEY and the definition of your primary key.