1

I have created a custom module in drupal 8. But in the .install file there is a field for created_date which has a type of timestamp. When I try to enable that module it gives the below error.

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created_date'' in

.install file

<?php
function golf_reservation_schema()
{
  $schema['golf_reservation'] = array(
   'fields' => array(
       'id' => array(
           'type'     => 'serial',
           'not null' => TRUE,
       ),
       'property_tid' => array(
           'type' => 'int',
           'not null' => TRUE,
       ),
       'property_name' => array(
           'type'     => 'varchar',
           'length'   => 100,
           'not null' => TRUE,
       ),
       'title' => array(
           'type'     => 'varchar',
           'length'   => 20,
           'not null' => FALSE,
       ),
       'full_name' => array(
           'type'     => 'varchar',
           'length'   => 255,
           'not null' => TRUE,
       ),
       'tee_date' => array(
           'type'     => 'varchar',
           'length'   => 255,
           'not null' => TRUE,
       ),
       'tee_time' => array(
           'type'     => 'varchar',
           'length'   => 255,
           'not null' => TRUE,
       ),
       'golfers' => array(
           'type'     => 'int',
           'not null' => TRUE,
       ),
       'no_of_carts' => array(
           'type'     => 'int',
           'not null' => TRUE,
       ),
       'special_request' => array(
           'type'     => 'text',
           'not null' => FALSE,
       ),
       'created_date' => array(
           'mysql_type' => 'timestamp',
           'not null' => true,
       ),
     ),
     'primary key' => array('id'),
   );

   return $schema;
}

But when I execute this query in database it does not give an error

CREATE TABLE eme_golf_reservation (
  `id` INT NOT NULL auto_increment, 
  `property_tid` INT NOT NULL, 
  `property_name` VARCHAR(100) NOT NULL, 
  `title` VARCHAR(20) NULL DEFAULT NULL, 
  `full_name` VARCHAR(255) NOT NULL, 
  `tee_date` VARCHAR(255) NOT NULL, 
  `tee_time` VARCHAR(255) NOT NULL, 
  `golfers` INT NOT NULL, 
  `no_of_carts` INT NOT NULL, 
  `special_request` TEXT NULL DEFAULT NULL, 
  `created_date` TIMESTAMP NOT NULL, 
  PRIMARY KEY (`id`)
)

I am running the latest Drupal version with MySQL 5.6.30.

1

I believe you need to explicitly define the default value for those in the schema:

'created_date' => [
  'mysql_type' => 'timestamp',
  'not null' => FALSE,
  'default' => NULL,
],

For maximum portability, you can also define the types for other databases:

'created_date' => [
  'mysql_type' => 'timestamp',
  'pgsql_type' => 'timestamp',
  'sqlite_type' => 'datetime',
  'not null' => FALSE,
  'default' => NULL,
],

Look at \Drupal\KernelTests\Core\Database\SchemaTest for an example. FWIW, code doesn't use that for timestamp/created/updated. Those are field types and handled by the Field API.

  • But when I copy this module to another computer which has the same mySQL version as me, it works without any issue. (without explicitly defining the default value ) – i am batman Dec 16 '17 at 4:38
  • 1
    This solution works for me. What should I do if I want timestamp field to add CURRENT_TIMESTAMP on each record addition? something like this... created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP I am using 'default' => CURRENT_TIMESTAMP, but this doesn't work. Thanks – webdevfreak Nov 23 '18 at 14:54

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.