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.