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?

Improve this question
1
  • 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. Commented May 30, 2014 at 10:12

2 Answers 2

Reset to default
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' );
Improve this answer
1
  • 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 Commented May 30, 2014 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, ),
Improve this answer
3
  • 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. Commented May 30, 2014 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. Commented Jul 25, 2016 at 15:16
  • This is also a word-for-word copy paste from a similar issue. drupal.stackexchange.com/questions/69737/… Commented Sep 21, 2016 at 13:26

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.