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:

namespace Craft;

class m171020_153541_grnplatform_AutoRenewUpdate extends BaseMigration
    public function safeUp()

        $this->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?


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.

| improve this answer | |
  • You rock dude!! – Rick Kukiela Oct 24 '17 at 16:33
  • turtles get there :) cheers, Rick – narration_sd Oct 25 '17 at 2:12

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

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

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...

| improve this answer | |
  • 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 Oct 20 '17 at 21:39
  • 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 Oct 21 '17 at 9:01

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.