<p>Turns out this is a bug as of <a href="https://craftcms.com/changelog#build2931" rel="nofollow">Craft 2.6.2931</a> with the <code>ColumnType::PK</code> enum 
in a record's attribute type definition.</p>

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

<p>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 <code>onBeforeInstall</code> method to your plugin.</p>

CC BY-SA 3.0
2016-09-20T14:12:55.317Z
So by using onBeforeInstall and setting up the table the Record will detect that it is already installed?
a-am
CC BY-SA 3.0
2016-09-20T18:15:36.550Z
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
CC BY-SA 3.0
2016-09-23T04:31:32.457Z
is the issue in the base record's createTable or the DbCommand's createTable?
a-am
CC BY-SA 3.0
2016-09-20T04:37:10.060Z
2016-09-20T04:37:10.060Z
Brad Bell
<p>There is an issue with using <code>unique =&gt; true</code> in defining an attribute as a primary key when using MYSQL 5.7.*. </p>

<pre><code>protected function defineAttributes()
{
    return array(
        'eid' =&gt; array(AttributeType::Number, 'unique' =&gt; true, 'column' =&gt; ColumnType::PK) 
    );
}
</code></pre>

<p>According to my setting this error should not occur. Here is the error. </p>

<pre><code>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
</code></pre>

<p>Here is the SQL executed.</p>

<pre><code>system.db.CDbCommand.execute(CREATE TABLE `craft_venti_events` (
    `eid` int(11) NOT NULL AUTO_INCREMENT NULL,
    `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)
</code></pre>

CC BY-SA 3.0
2016-09-19T22:19:46.497Z
Can you enable devMode and look at the SQL that's being generated for that statement and share it?
Brad Bell
CC BY-SA 3.0
2016-09-19T22:55:36.223Z
I have updated the question with the sql.
a-am
CC BY-SA 3.0
2016-09-19T23:03:58.083Z
If you remove `AttributeType::Number`, does that help?
Brad Bell
CC BY-SA 3.0
2016-09-19T23:07:30.823Z
If I remove AttributeType::Number I receive the same error.
a-am
CC BY-SA 3.0
2016-09-19T23:23:16.210Z
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
CC BY-SA 3.0
2016-09-19T23:27:58.397Z
I still run into the same error.
a-am
<plugin-development><mysql>
How would I write this differently to make it compatible with MYSQL 5.7
# How would I write this differently to make it compatible with MYSQL 5.7

(Asked by: username_0 on 2016-09-19)

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` (
    `eid` int(11) NOT NULL AUTO_INCREMENT NULL,
    `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)

**Tags:** plugin-development, mysql
> **Comments:**
> **username_1**: Can you enable devMode and look at the SQL that's being generated for that statement and share it?  
> **username_0**: I have updated the question with the sql.  
> **username_1**: If you remove `AttributeType::Number`, does that help?  
> **username_0**: If I remove AttributeType::Number I receive the same error.  
> **username_1**: Hrm... one last thing. Pretty sure `unique` is unnecessary here as primary keys are unique by definition. What happens if you remove it?  
> **username_0**: I still run into the same error.  

---

## 1 answers

### Answer by username_1 on 2016-09-20. Score: 3 | Accepted answer

Turns out this is a bug as of [Craft 2.6.2931](https://craftcms.com/changelog#build2931) 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.

> **Comments:**
> **username_0**: So by using onBeforeInstall and setting up the table the Record will detect that it is already installed?  
> **username_1**: 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.  
> **username_0**: is the issue in the base record's createTable or the DbCommand's createTable?  

---

