There is an issue with using unique => true in defining an attribute as a primary key when using MYSQL 5.7.*.

protected function defineAttributes()
    return array(
        'eid' => array(AttributeType::Number, 'unique' => true, 'column' => ColumnType::PK) 

According to my setting this error should not occur. Here is the error.

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Here is the SQL executed.

system.db.CDbCommand.execute(CREATE TABLE `craft_venti_events` (
    `eventid` INT(10) NULL,
    `startDate` datetime NULL,
    `endDate` datetime NULL,
    `allDay` INT(10) NULL,
    `repeat` INT(10) NULL,
    `rRule` VARCHAR(255) NULL,
    `summary` VARCHAR(255) NULL,
    `isrepeat` INT(10) NULL,
    `locale` VARCHAR(255) NULL,
    `dateCreated` datetime NOT NULL,
    `dateUpdated` datetime NOT NULL,
    `uid` CHAR(36) NOT NULL DEFAULT 0,
    PRIMARY KEY (eid)
) ENGINE=InnoDb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci)
  • Can you enable devMode and look at the SQL that's being generated for that statement and share it?
    – Brad Bell
    Sep 19 '16 at 22:19
  • I have updated the question with the sql.
    – aran
    Sep 19 '16 at 22:55
  • If you remove AttributeType::Number, does that help?
    – Brad Bell
    Sep 19 '16 at 23:03
  • If I remove AttributeType::Number I receive the same error.
    – aran
    Sep 19 '16 at 23:07
  • Hrm... one last thing. Pretty sure unique is unnecessary here as primary keys are unique by definition. What happens if you remove it?
    – Brad Bell
    Sep 19 '16 at 23:23

Turns out this is a bug as of Craft 2.6.2931 with the ColumnType::PK enum in a record's attribute type definition.

Pretty sure it'd be a bug in < MySQL 5.7 because it's invalid SQL syntax as well unless they're just more forgiving of it there.

This is a non-issue in the upcoming Craft 3 release (the concept of attributes no longer exists and plugins explicitly define their table schema create statements), but the workaround for Craft 2 until the bug is fixed is to not use a record here and manually create the table schema that you need by adding an onBeforeInstall method to your plugin.

  • So by using onBeforeInstall and setting up the table the Record will detect that it is already installed?
    – aran
    Sep 20 '16 at 14:12
  • Probably not... you'd probably either have to not use a Record there or have that record override BasePlugin's createTable command and do some custom logic.
    – Brad Bell
    Sep 20 '16 at 18:15
  • is the issue in the base record's createTable or the DbCommand's createTable?
    – aran
    Sep 23 '16 at 4:31

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.