Invalid default value for 'record_date' timestamp

Question

I have created Schema in drupal7

 $schema['survey'] = array(
      'description' => 'TODO: please describe this table!',
      'fields' => array(
        'id' => array(
          'description' => 'TODO: please describe this field!',
          'type' => 'serial',
          'not null' => TRUE,
        ),
        'survey_id' => array(
          'description' => 'TODO: please describe this field!',
          'type' => 'int',
          'not null' => FALSE,
....
 'record_date' => array(
          'description' => 'TODO: please describe this field!',
          'mysql_type' => 'timestamp',
          'not null' => FALSE,
        ),
        'facebookid' => array(
          'description' => 'TODO: please describe this field!',
          'type' => 'varchar',
          'length' => '10',
          'not null' => FALSE,
)

/**
 * hook_enable()
 */
function itg_be_lucky_today_enable() {
  db_query('
    ALTER TABLE {survey} 
    MODIFY record_date TIMESTAMP NOT NULL 
    DEFAULT CURRENT_TIMESTAMP 
    ON UPDATE CURRENT_TIMESTAMP'
  );
}

When i try to enable module it's show error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'record_date': CREATE TABLE {survey} ( `id` INT NOT NULL auto_increment COMMENT 'TODO: please describe this field!', `survey_id` INT NULL DEFAULT NULL COMMENT 'TODO: please describe this field!', `name` VARCHAR(100) NULL

I have already search but did not find any soluntion for me.

This is working fine in my Local machine. I have also tested on mysql version: 5.7 and 5.5

Thanks

Answer 1

Try this:

'record_date' => array(
  'description' => 'TODO: please describe this field!',
  'type' => 'datetime',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),

For more detailed answer see this post