0

I'm building a module for Drupal 7, but I have troubles with my schema, and I don't understand why.

This is my schema.

$schema['table_name'] = array(
        'fields' => array(
            'id' => array(
                'type' => 'serial',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'nid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'oid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'start_time' => array(
                'type' => 'datetime',
                'not null' => false
            ),
            'end_time' => array(
                'type' => 'datetime',
                'not null' => false
            ),
            'duration' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            )
        ),
        'primary key' => array('id'),
        'unique keys' => array('id')
    );

I get this error.

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL DEFAULT NULL, end_time NULL DEFAULT NULL, duration INT unsigned NOT ' at line 5: CREATE TABLE {table_name} ( id INT unsigned NOT NULL auto_increment, nid INT unsigned NOT NULL, oid INT unsigned NOT NULL, start_time NULL DEFAULT NULL, end_time NULL DEFAULT NULL, duration INT unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY 0 () ) ENGINE = InnoDB DEFAULT CHARACTER SET utf8; Array ( ) in db_create_table() (line 2717 of /Applications/MAMP/htdocs/project/www/includes/database/database.inc).

How can I fix this error?

  • I've found out Drupal 7 no longer supports the datetime type and to use this type you must use the key 'mysql_type', however I'm still getting an error. – thebubbles May 30 '14 at 10:12
1

I've managed to fix it. I've altered 2 things from my original schema, the first issue was that Drupal 7 no longer supports 'type' => 'datetime', instead use 'mysql_type' => 'datetime'. The second was 'unique keys' => array('id'), I removed this and everything works correctly.

Solution...

$schema['table_name'] = array(
        'fields' => array(
            'id' => array(
                'type' => 'serial',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'nid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'oid' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            ),
            'start_time' => array(
                'type' => 'int',
                'mysql_type' => 'datetime',
                'size' => 'normal',
                'not null' => false
            ),
            'end_time' => array(
                'type' => 'int',
                'mysql_type' => 'datetime',
                'size' => 'normal',
                'not null' => false
            ),
            'duration' => array(
                'type' => 'int',
                'unsigned' => true,
                'not null' => true,
                'length' => 11
            )
        ),
        'primary key' => array('id'),
        'mysql_engine' => 'InnoDB'
    );
  • 1
    unique keys takes a nested array, so 'unique keys' => array('idx_id_uniq' => array('id'));. But it doesn't make sense to have the primary and unique keys set for the same single column, as primary keys already have a unique constraint – Clive May 30 '14 at 10:50
-1

Try this for Drupal7,

'pubblish_date' => array(
  'description' => t('The pubblish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),
'unpublish_date' => array(
  'description' => t('The unpublish date for the single news'),
  'type' => 'varchar',
  'mysql_type' => 'datetime',
  'not null' => FALSE,
),
  • Thanks @shrish, similar solution to what I've just posted. An issue here maybe that whilst using the Type Varchar you may also need to include a Length value. – thebubbles May 30 '14 at 10:23
  • Some elaboration on the problem and why this solution works rather than 'here is a fix for your specific code' would be much more appreciated. – Kiee Jul 25 '16 at 15:16
  • This is also a word-for-word copy paste from a similar issue. drupal.stackexchange.com/questions/69737/… – Remog Sep 21 '16 at 13:26

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.