0

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.

  • “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 Oct 22 '18 at 11:03
  • Do you have error logging enabled? What is this additional output you get? – kero Oct 22 '18 at 11:16
  • Sorry I haven't enabled error log. – user2584538 Oct 22 '18 at 11:18
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.