DB Query not working in Plugin

Question

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.

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