0

I am working on creating a plugin for a multisite wordpress framework that is enabled by default for all (by me, the super administrator)

When it activates it is required to create 2 tables where it will store some precedural information. Right now I am working on creating the first (most important) table.

The p_install function is called because of the options I set at the end of the function.

global $wpdb;
global $p_db_version;
$table_name = $wpdb->prefix . 'p_table';
$wpdb->p_table = $wpdb->prefix . 'p_table';
$charset_collate = '';
if (!empty($wpdb->charset)) {
    $charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset}";
}
if (!empty($wpdb->collate)) {
    $charset_collate .= " COLLATE {$wpdb->collate}";
}

$sql = "CREATE TABLE {$table_name} ( id mediumint(9) NOT NULL AUTO_INCREMENT, blog_id mediumint(9) NOT NULL, create_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, modify_date datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, created_by mediumint(9) NOT NULL, modified_by mediumint(9) NOT NULL, is_active tinyint(1) DEFAULT 0 NOT NULL, p tinytext DEFAULT '' NOT NULL, v mediumint(9) DEFAULT 0 NOT NULL, c mediumint(9) DEFAULT 0 NOT NULL, json_data text NOT NULL, type mediumint(9) DEFAULT 0 NOT NULL, UNIQUE KEY id (id) ) {$charset_collate};";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql);

delete_option('p_db_version'); delete_option('p_db_sql'); delete_option('p_db_timestamp');

add_option('p_db_version', $p_db_version); add_option('p_db_sql', $sql); add_option('p_db_timestamp', date('l jS \of F Y h:i:s A'));

But it fails to create the table. Please advise.

Edit 1

After reading PatJ's answer I feel the need to clean up some air:

  1. I got my table creation code from the dbDelta() documentation
  2. I have edited my code and used the two space policy

Answer

And the answer is that a datetime cant have the CURRENT_TIMESTAMP default value since MySQL 5.6.5 so I changed modify_date from datetime to timestamp and all is good.

Credits to: sebthebert

Thank you.

2
  • is "enabled by default" means that you place it in the mu-plugins directory? Sep 2, 2014 at 15:12
  • by "enabled by default" I mean that I enable it network-wise as administrator.
    – aifrim
    Sep 3, 2014 at 14:06

2 Answers 2

2

And the answer is that a datetime cant have the CURRENT_TIMESTAMP default value since MySQL 5.6.5 so I changed modify_date from datetime to timestamp and all is good.

1

From the dbDelta() documentation:

Note that the dbDelta function is rather picky, however. For instance:

  • You must put each field on its own line in your SQL statement.
  • You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  • You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
  • You must not use any apostrophes or backticks around field names.
  • Field types must be all lowercase.

(Emphasis mine.)

I don't see a PRIMARY KEY in your $sql code -- perhaps that's what's missing.

3
  • Does not work. I have modified my code and added only two spaces, I then modified adding a PRIMARY KEY definition in the $sql (but UNIQUE KEY is a primary key anyways) and still did not work. I created my code like the one in documentation, tested it with my servers PhpMyAdmin. With a hunch I just copied the example there and went with it and it worked. Perhaps the error lies within the fields I use in my query.
    – aifrim
    Sep 6, 2014 at 8:03
  • I did use the two-spaces between KEY and key name.
    – aifrim
    Sep 6, 2014 at 12:14
  • I found the answer. Edited the post again.
    – aifrim
    Sep 6, 2014 at 12:47

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.