<p>Here's a completed answer, while leaving the information from the initial foray, as it could give useful leads in other cases.</p>

<p>The problem is indeed a Craft bug, reported there as issue <a href="https://github.com/craftcms/cms/issues/2044" rel="nofollow noreferrer">#2044</a>.</p>

<p>The code in DbHelper isn't quite reckoning correctly if you use a string definition for a column $type. The specific problem in this case is the small code block starting at line 223. </p>

<p>This improperly adds an extra 'null' at the end of the original string, which in turn gets complicated by flonkey default behaviors of MySql, in the way of some mentioned in the earlier post, for a thoroughly confusing improper result. </p>

<p>Until corrected, you can bypass the problems by instead specifying the $type definition as an array: </p>

<pre><code>        $type = array(
            'column' =&gt; 'tinyint',
            'length' =&gt; '1',
            'unsigned' =&gt; 'true',
            'required' =&gt; 'true',
            'default' =&gt; '1',
        );

        $this-&gt;addColumn('craft_grnplatform_subscriptions ', 'failedRenew', $type);
</code></pre>

<p>Resulting DDL read back from the database:</p>

<pre><code>        `failedRenew` tinyint(1) unsigned NOT NULL DEFAULT '1',
</code></pre>

<p>The same means can be used for other types which may run into problems with DbHelper.</p>

<p>A look through Craft 3's handling suggests this problem won't be present there.</p>

<p>N.B. Another problem was later noticed, so you'll want use only the SQL string method if you are defining a private key (pk), as an array would likely fail there.  This should be fixed also when the posted issue is resolved.</p>

CC BY-SA 3.0
2017-10-24T16:33:53.340Z
You rock dude!!
Rick Kukiela
CC BY-SA 3.0
2017-10-25T02:12:21.880Z
turtles get there :) cheers, Rick
narration_sd
CC BY-SA 3.0
2017-10-22T03:06:26.253Z
2017-10-27T00:46:24.457Z
2017-10-27T00:46:24.457Z
narration_sd
<p>This looks like it could be a Yii issue, if a rather occluded one.</p>

<p>Here's how it is in another framework: <a href="https://github.com/metabase/metabase/issues/3506" rel="nofollow noreferrer">MySQL TINYINT(1) columns are always interpreted as booleans #3506</a></p>

<p>And here is a pointedly uncertain link with link, on a quite confused Yii discussion circling around this: <a href="https://github.com/yiisoft/yii2/issues/1099#issuecomment-27363543" rel="nofollow noreferrer">It is already documented that tinyint(1) would be treated as boolean</a></p>

<p>I don't think it is actually doc'd so -- actually it's the other way around. But doc may not tell all.</p>

<p>I would suggest making the def tinyint(2) and see if it cures...good fortune...</p>

CC BY-SA 3.0
2017-10-20T21:39:42.263Z
`failedRenew` tinyint(2) unsigned DEFAULT '0', so no, it didnt help the not null appear... You know I can totally live with the column being nullable, I just want to know why its not working for my own curiosity.
Rick Kukiela
CC BY-SA 3.0
2017-10-21T09:01:48.033Z
As noted on the Slack, I now suspect this to be a reportable Craft bug. I was able to bypass successfully on a test db by using $this->execute("alter table craft_grnplatform_subscriptions add column failedRenew tinyint(1) unsigned not null default 0"); a straight sql equivalent, which seems to say the problem's not in the PDO.
narration_sd
CC BY-SA 3.0
2017-10-20T21:28:36.113Z
2017-10-20T21:35:12.873Z
2017-10-20T21:35:12.873Z
narration_sd
<p>I set up a quick and dirty migration to add two columns to two tables. These are to be basically boolean 1/0 switch fields. I'm setting up my migration file as follows, note the 'not null' in both column types:</p>

<pre><code>&lt;?php
namespace Craft;

class m171020_153541_grnplatform_AutoRenewUpdate extends BaseMigration
{
    public function safeUp()
    {

        $this-&gt;addColumn('grnplatform_payment_cards', 'expireNotified', "tinyint(1) unsigned NOT NULL DEFAULT '0'");
        $this-&gt;addColumn('grnplatform_subscriptions', 'failedRenew', "tinyint(1) unsigned NOT NULL DEFAULT '0'");

        return true;
    }

    public function safeDown() {

        $this-&gt;dropColumn('grnplatform_payment_cards', 'expireNotified');
        $this-&gt;dropColumn('grnplatform_subscriptions', 'failedRenew');

        return true;
    }
}
</code></pre>

<p>However after the migration runs, my <code>show create table</code> command yields this:</p>

<pre><code>CREATE TABLE `craft_grnplatform_subscriptions` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `renewalPeriod` enum('monthly','quarterly','yearly') COLLATE utf8_unicode_ci DEFAULT NULL,
  `autoRenew` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `lastRenewal` date NOT NULL,
  `nextRenewal` date NOT NULL,
  `failedRenew` tinyint(1) unsigned DEFAULT '0',
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `craft_grnplatform_subscriptions_userId_unq_idx` (`userId`),
  KEY `craft_grnplatform_subscriptions_nextRenewal_idx` (`nextRenewal`),
  CONSTRAINT `FK_craft_grnplatform_subscriptions_craft_grnplatform_vendor_data` FOREIGN KEY (`userId`) REFERENCES `craft_grnplatform_vendor_data` (`userId`) ON DELETE CASCADE,
  CONSTRAINT `craft_grnplatform_subscriptions_id_fk` FOREIGN KEY (`id`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `craft_grnplatform_subscriptions_userId_fk` FOREIGN KEY (`userId`) REFERENCES `craft_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
</code></pre>

<p>Notice how the "not null" is missing from the column declaration for 'failedRenew`...</p>

<p>How can get set the NOT NULL value on a column I'm adding from a migration?</p>

<plugin-development><migration>
Plugin DB migration add "not null" column
# Plugin DB migration add "not null" column

(Asked by: username_0 on 2017-10-20)

I set up a quick and dirty migration to add two columns to two tables. These are to be basically boolean 1/0 switch fields. I'm setting up my migration file as follows, note the 'not null' in both column types:
addColumn('grnplatform_payment_cards', 'expireNotified', "tinyint(1) unsigned NOT NULL DEFAULT '0'");
        $this->addColumn('grnplatform_subscriptions', 'failedRenew', "tinyint(1) unsigned NOT NULL DEFAULT '0'");

        return true;
    }

    public function safeDown() {

        $this->dropColumn('grnplatform_payment_cards', 'expireNotified');
        $this->dropColumn('grnplatform_subscriptions', 'failedRenew');

        return true;
    }
}

However after the migration runs, my show create table command yields this:
CREATE TABLE `craft_grnplatform_subscriptions` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `renewalPeriod` enum('monthly','quarterly','yearly') COLLATE utf8_unicode_ci DEFAULT NULL,
  `autoRenew` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `lastRenewal` date NOT NULL,
  `nextRenewal` date NOT NULL,
  `failedRenew` tinyint(1) unsigned DEFAULT '0',
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `craft_grnplatform_subscriptions_userId_unq_idx` (`userId`),
  KEY `craft_grnplatform_subscriptions_nextRenewal_idx` (`nextRenewal`),
  CONSTRAINT `FK_craft_grnplatform_subscriptions_craft_grnplatform_vendor_data` FOREIGN KEY (`userId`) REFERENCES `craft_grnplatform_vendor_data` (`userId`) ON DELETE CASCADE,
  CONSTRAINT `craft_grnplatform_subscriptions_id_fk` FOREIGN KEY (`id`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `craft_grnplatform_subscriptions_userId_fk` FOREIGN KEY (`userId`) REFERENCES `craft_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Notice how the "not null" is missing from the column declaration for 'failedRenew`...
How can get set the NOT NULL value on a column I'm adding from a migration?

**Tags:** plugin-development, migration


---

## 2 answers

### Answer by username_1 on 2017-10-22. Score: 2 | Accepted answer

Here's a completed answer, while leaving the information from the initial foray, as it could give useful leads in other cases.
The problem is indeed a Craft bug, reported there as issue #2044.
The code in DbHelper isn't quite reckoning correctly if you use a string definition for a column $type. The specific problem in this case is the small code block starting at line 223. 
This improperly adds an extra 'null' at the end of the original string, which in turn gets complicated by flonkey default behaviors of MySql, in the way of some mentioned in the earlier post, for a thoroughly confusing improper result. 
Until corrected, you can bypass the problems by instead specifying the $type definition as an array: 
        $type = array(
            'column' => 'tinyint',
            'length' => '1',
            'unsigned' => 'true',
            'required' => 'true',
            'default' => '1',
        );

        $this->addColumn('craft_grnplatform_subscriptions ', 'failedRenew', $type);

Resulting DDL read back from the database:
        `failedRenew` tinyint(1) unsigned NOT NULL DEFAULT '1',

The same means can be used for other types which may run into problems with DbHelper.
A look through Craft 3's handling suggests this problem won't be present there.
N.B. Another problem was later noticed, so you'll want use only the SQL string method if you are defining a private key (pk), as an array would likely fail there.  This should be fixed also when the posted issue is resolved.

> **Comments:**
> **username_0**: You rock dude!!  
> **username_1**: turtles get there :) cheers, Rick  

---

### Answer by username_1 on 2017-10-20. Score: 2

This looks like it could be a Yii issue, if a rather occluded one.

Here's how it is in another framework: [MySQL TINYINT(1) columns are always interpreted as booleans #3506](https://github.com/metabase/metabase/issues/3506)

And here is a pointedly uncertain link with link, on a quite confused Yii discussion circling around this: [It is already documented that tinyint(1) would be treated as boolean](https://github.com/yiisoft/yii2/issues/1099#issuecomment-27363543)

I don't think it is actually doc'd so -- actually it's the other way around. But doc may not tell all.

I would suggest making the def tinyint(2) and see if it cures...good fortune...

> **Comments:**
> **username_0**: `failedRenew` tinyint(2) unsigned DEFAULT '0', so no, it didnt help the not null appear... You know I can totally live with the column being nullable, I just want to know why its not working for my own curiosity.  
> **username_1**: As noted on the Slack, I now suspect this to be a reportable Craft bug. I was able to bypass successfully on a test db by using $this->execute("alter table craft_grnplatform_subscriptions add column failedRenew tinyint(1) unsigned not null default 0"); a straight sql equivalent, which seems to say the problem's not in the PDO.  

---

