Magento2: M2.1.3 : SQL Error on `customer_grid_flat` when running `setup:upgrade`

[kanduvisla]

I've ran setup:upgrade countless times before, but this morning after I upgraded from Magento 2.1.2 to Magento 2.1.3 I get the following SQL error when running setup:upgrade:

[Zend_Db_Statement_Exception]
  SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'created_in' used in key specification without a key length, query was: CREATE T
  ABLE IF NOT EXISTS `customer_grid_flat` (
    `entity_id` int UNSIGNED NOT NULL COMMENT 'Entity ID' ,
    `name` text NULL COMMENT 'Name' ,
    `email` varchar(255) NULL COMMENT 'Email' ,
    `group_id` int NULL COMMENT 'Group_id' ,
    `created_at` timestamp NULL default NULL COMMENT 'Created_at' ,
    `website_id` int NULL COMMENT 'Website_id' ,
    `confirmation` varchar(255) NULL COMMENT 'Confirmation' ,
    `created_in` text NULL COMMENT 'Created_in' ,
    `dob` date NULL COMMENT 'Dob' ,
    `gender` int NULL COMMENT 'Gender' ,
    `taxvat` varchar(255) NULL COMMENT 'Taxvat' ,
    `lock_expires` timestamp NULL default NULL COMMENT 'Lock_expires' ,
    `shipping_full` text NULL COMMENT 'Shipping_full' ,
    `billing_full` text NULL COMMENT 'Billing_full' ,
    `billing_firstname` varchar(255) NULL COMMENT 'Billing_firstname' ,
    `billing_lastname` varchar(255) NULL COMMENT 'Billing_lastname' ,
    `billing_telephone` varchar(255) NULL COMMENT 'Billing_telephone' ,
    `billing_postcode` varchar(255) NULL COMMENT 'Billing_postcode' ,
    `billing_country_id` varchar(255) NULL COMMENT 'Billing_country_id' ,
    `billing_region` varchar(255) NULL COMMENT 'Billing_region' ,
    `billing_street` varchar(255) NULL COMMENT 'Billing_street' ,
    `billing_city` varchar(255) NULL COMMENT 'Billing_city' ,
    `billing_fax` varchar(255) NULL COMMENT 'Billing_fax' ,
    `billing_vat_id` varchar(255) NULL COMMENT 'Billing_vat_id' ,
    `billing_company` varchar(255) NULL COMMENT 'Billing_company' ,
    PRIMARY KEY (`entity_id`),
    INDEX `CUSTOMER_GRID_FLAT_GROUP_ID` (`group_id`),
    INDEX `CUSTOMER_GRID_FLAT_CREATED_AT` (`created_at`),
    INDEX `CUSTOMER_GRID_FLAT_WEBSITE_ID` (`website_id`),
    INDEX `CUSTOMER_GRID_FLAT_CONFIRMATION` (`confirmation`),
    INDEX `CUSTOMER_GRID_FLAT_CREATED_IN` (`created_in`),
    INDEX `CUSTOMER_GRID_FLAT_DOB` (`dob`),
    INDEX `CUSTOMER_GRID_FLAT_GENDER` (`gender`),
    INDEX `CUSTOMER_GRID_FLAT_LOCK_EXPIRES` (`lock_expires`),
    INDEX `CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID` (`billing_country_id`),
    FULLTEXT `FTI_AC9FBFAE9FBD3A0B02DBA986349612A6` (`name`, `email`, `taxvat`, `shipping_full`, `billing_full`, `billing_firstname`, `billing_lastname`, `
  billing_telephone`, `billing_postcode`, `billing_region`, `billing_street`, `billing_city`, `billing_fax`, `billing_vat_id`, `billing_company`)
  ) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

Anyone knows what might be causing this issue? I've never seen this SQL error before. I'm going to dive into it somewhat deeper, but the CREATE TABLE-line gives me the impression that there is something wrong in the setup script.

edit:

This is the stack trace:

Exception trace:
 () at /data/web/ci/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228
 PDOStatement->execute() at /data/web/ci/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228
 Zend_Db_Statement_Pdo->_execute() at /data/web/ci/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:95
 Magento\Framework\DB\Statement\Pdo\Mysql->_execute() at /data/web/ci/vendor/magento/zendframework1/library/Zend/Db/Statement.php:303
 Zend_Db_Statement->execute() at /data/web/ci/vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php:480
 Zend_Db_Adapter_Abstract->query() at /data/web/ci/vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php:238
 Zend_Db_Adapter_Pdo_Abstract->query() at /data/web/ci/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:465
 Magento\Framework\DB\Adapter\Pdo\Mysql->_query() at /data/web/ci/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:520
 Magento\Framework\DB\Adapter\Pdo\Mysql->query() at /data/web/ci/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:2008
 Magento\Framework\DB\Adapter\Pdo\Mysql->createTable() at /data/web/ci/vendor/magento/framework/Indexer/GridStructure.php:124
 Magento\Framework\Indexer\GridStructure->createFlatTable() at /data/web/ci/vendor/magento/framework/Indexer/GridStructure.php:75
 Magento\Framework\Indexer\GridStructure->create() at /data/web/ci/vendor/magento/framework/Indexer/SaveHandler/IndexerHandler.php:126
 Magento\Framework\Indexer\SaveHandler\IndexerHandler->cleanIndex() at /data/web/ci/vendor/magento/framework/Indexer/Action/Base.php:167
 Magento\Framework\Indexer\Action\Base->execute() at /data/web/ci/vendor/magento/framework/Indexer/Action/Base.php:180
 Magento\Framework\Indexer\Action\Base->executeFull() at /data/web/ci/vendor/magento/module-indexer/Model/Indexer.php:412
 Magento\Indexer\Model\Indexer->reindexAll() at /data/web/ci/vendor/magento/module-customer/Setup/UpgradeData.php:129
 Magento\Customer\Setup\UpgradeData->upgrade() at /data/web/ci/setup/src/Magento/Setup/Model/Installer.php:832
 Magento\Setup\Model\Installer->handleDBSchemaData() at /data/web/ci/setup/src/Magento/Setup/Model/Installer.php:796
 Magento\Setup\Model\Installer->installDataFixtures() at /data/web/ci/setup/src/Magento/Setup/Console/Command/UpgradeCommand.php:74
 Magento\Setup\Console\Command\UpgradeCommand->execute() at /data/web/ci/vendor/symfony/console/Symfony/Component/Console/Command/Command.php:257
 Symfony\Component\Console\Command\Command->run() at /data/web/ci/vendor/symfony/console/Symfony/Component/Console/Application.php:874
 Symfony\Component\Console\Application->doRunCommand() at /data/web/ci/vendor/symfony/console/Symfony/Component/Console/Application.php:195
 Symfony\Component\Console\Application->doRun() at /data/web/ci/vendor/magento/framework/Console/Cli.php:96
 Magento\Framework\Console\Cli->doRun() at /data/web/ci/vendor/symfony/console/Symfony/Component/Console/Application.php:126
 Symfony\Component\Console\Application->run() at /data/web/ci/bin/magento:23

My best guess is that Magento\Customer\Setup\UpgradeData->upgrade() is the place to start looking.

Another interesting detail is that this only happens on my production / staging server and not on my local development server.

edit 2: There error seems to be indexer-related. When I look at the upgrade()-method, I see that the line in question invokes the indexer. And when I run indexer:reindex I get the same error.

[kanduvisla]

Running the raw query throws the same error. Could it be MySQL-configuration-related?

[goralgen]

The same problem after upgrade to 2.1.3 in production mode.
MySQL 5.6.27-0

[kanduvisla]

Locally, the query throws no error. But on remote I get this error.

• Remote database: MySQL 5.6.33-79.0-log / InnoDB: 5.6.33
• Local database: 10.2.2-MariaDB-1~trusty / InnoDB: 5.7.14

[kanduvisla]

So I downgraded my local development database to MySQL 5.6.35 / InnoDB 5.6.35

No error. I think I'm going to try to figure this out with my hosting company. Isn't there anyone else who encountered this problem besides @goralgen ?

[kanduvisla]

Ok, so this is interesting...

I'm logging the queries that are executed by the indexer, and here's what the query looks like locally:

CREATE TABLE IF NOT EXISTS `customer_grid_flat` (
  `entity_id` int UNSIGNED NOT NULL COMMENT 'Entity ID' ,
  `name` text NULL COMMENT 'Name' ,
  `email` varchar(255) NULL COMMENT 'Email' ,
  `group_id` int NULL COMMENT 'Group_id' ,
  `created_at` timestamp NULL default NULL COMMENT 'Created_at' ,
  `website_id` int NULL COMMENT 'Website_id' ,
  `confirmation` varchar(255) NULL COMMENT 'Confirmation' ,
  `created_in` text NULL COMMENT 'Created_in' ,
  `dob` date NULL COMMENT 'Dob' ,
  `gender` int NULL COMMENT 'Gender' ,
  `taxvat` varchar(255) NULL COMMENT 'Taxvat' ,
  `lock_expires` timestamp NULL default NULL COMMENT 'Lock_expires' ,
  `shipping_full` text NULL COMMENT 'Shipping_full' ,
  `billing_full` text NULL COMMENT 'Billing_full' ,
  `billing_firstname` varchar(255) NULL COMMENT 'Billing_firstname' ,
  `billing_lastname` varchar(255) NULL COMMENT 'Billing_lastname' ,
  `billing_telephone` varchar(255) NULL COMMENT 'Billing_telephone' ,
  `billing_postcode` varchar(255) NULL COMMENT 'Billing_postcode' ,
  `billing_country_id` varchar(255) NULL COMMENT 'Billing_country_id' ,
  `billing_region` varchar(255) NULL COMMENT 'Billing_region' ,
  `billing_street` varchar(255) NULL COMMENT 'Billing_street' ,
  `billing_city` varchar(255) NULL COMMENT 'Billing_city' ,
  `billing_fax` varchar(255) NULL COMMENT 'Billing_fax' ,
  `billing_vat_id` varchar(255) NULL COMMENT 'Billing_vat_id' ,
  `billing_company` varchar(255) NULL COMMENT 'Billing_company' ,
  PRIMARY KEY (`entity_id`),
  INDEX `CUSTOMER_GRID_FLAT_GROUP_ID` (`group_id`),
  INDEX `CUSTOMER_GRID_FLAT_CREATED_AT` (`created_at`),
  INDEX `CUSTOMER_GRID_FLAT_WEBSITE_ID` (`website_id`),
  INDEX `CUSTOMER_GRID_FLAT_CONFIRMATION` (`confirmation`),
  INDEX `CUSTOMER_GRID_FLAT_DOB` (`dob`),
  INDEX `CUSTOMER_GRID_FLAT_GENDER` (`gender`),
  INDEX `CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID` (`billing_country_id`),
  FULLTEXT `FTI_8746F705702DD5F6D45B8C7CE7FE9F2F` (`name`, `email`, `created_in`, `taxvat`, `shipping_full`, `billing_full`, `billing_firstname`, `billing_lastname`, `billing_telephone`, `billing_postcode`, `billing_region`, `billing_city`, `billing_fax`, `billing_company`)
) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

If you look closely, there is one minor difference: in the query that is build locally there is no index created from the created_in-attribute. What could cause this minor difference in query building?

edit: I don't get it. The same code, the same database, but for some reason Magento's Indexer determines that my production environment needs an extra index. Can anyone point me to the right direction where this index is determined?

[kanduvisla]

Ok, so I narrowed the creation of the flat table down to \Magento\Framework\Indexer\GridStructure::createFlatTable(). When I debug variables there I notice the following:

if ($field['type'] === 'filterable') {
    $table->addIndex(
        $this->resource->getIdxName($tableName, $name, AdapterInterface::INDEX_TYPE_INDEX),
        $name,
        ['type' => AdapterInterface::INDEX_TYPE_INDEX]
    );
}

So this code is responsible for adding the index. Now when I debug my data, I notice that created_in on local is of type searchable and on production it's filterable. So now I have to figure out where this difference is coming from. The raw configuration of the attributes in the database is the same. I even completely copied the database from production to my local environment, and still, locally I cannot reproduce the error.

[kanduvisla]

Ok, so now I'm totally stumped. I synced the last version of the database and now it no longer creates INDEX local, but KEY.

[goralgen]

@kanduvisla
Could you try create this table manually by SQL?
(request was gotten by command 'show create table' in Magento EE 2.1.2 in production mode)

CREATE TABLE `customer_grid_flat` (
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  `name` text COMMENT 'Name',
  `email` varchar(255) DEFAULT NULL COMMENT 'Email',
  `group_id` int(11) DEFAULT NULL COMMENT 'Group_id',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Created_at',
  `website_id` int(11) DEFAULT NULL COMMENT 'Website_id',
  `confirmation` varchar(255) DEFAULT NULL COMMENT 'Confirmation',
  `created_in` text COMMENT 'Created_in',
  `dob` date DEFAULT NULL COMMENT 'Dob',
  `gender` int(11) DEFAULT NULL COMMENT 'Gender',
  `taxvat` varchar(255) DEFAULT NULL COMMENT 'Taxvat',
  `lock_expires` timestamp NULL DEFAULT NULL COMMENT 'Lock_expires',
  `shipping_full` text COMMENT 'Shipping_full',
  `billing_full` text COMMENT 'Billing_full',
  `billing_firstname` varchar(255) DEFAULT NULL COMMENT 'Billing_firstname',
  `billing_lastname` varchar(255) DEFAULT NULL COMMENT 'Billing_lastname',
  `billing_telephone` varchar(255) DEFAULT NULL COMMENT 'Billing_telephone',
  `billing_postcode` varchar(255) DEFAULT NULL COMMENT 'Billing_postcode',
  `billing_country_id` varchar(255) DEFAULT NULL COMMENT 'Billing_country_id',
  `billing_region` varchar(255) DEFAULT NULL COMMENT 'Billing_region',
  `billing_street` varchar(255) DEFAULT NULL COMMENT 'Billing_street',
  `billing_city` varchar(255) DEFAULT NULL COMMENT 'Billing_city',
  `billing_fax` varchar(255) DEFAULT NULL COMMENT 'Billing_fax',
  `billing_vat_id` varchar(255) DEFAULT NULL COMMENT 'Billing_vat_id',
  `billing_company` varchar(255) DEFAULT NULL COMMENT 'Billing_company',
  PRIMARY KEY (`entity_id`),
  KEY `CUSTOMER_GRID_FLAT_GROUP_ID` (`group_id`),
  KEY `CUSTOMER_GRID_FLAT_CREATED_AT` (`created_at`),
  KEY `CUSTOMER_GRID_FLAT_WEBSITE_ID` (`website_id`),
  KEY `CUSTOMER_GRID_FLAT_CONFIRMATION` (`confirmation`),
  KEY `CUSTOMER_GRID_FLAT_DOB` (`dob`),
  KEY `CUSTOMER_GRID_FLAT_GENDER` (`gender`),
  KEY `CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID` (`billing_country_id`),
  FULLTEXT KEY `FTI_8746F705702DD5F6D45B8C7CE7FE9F2F` (`name`,`email`,`created_in`,`taxvat`,`shipping_full`,`billing_full`,`billing_firstname`,`billing_lastname`,`billing_telephone`,`billing_postcode`,`billing_region`,`billing_city`,`billing_fax`,`billing_company`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='customer_grid_flat'

[kanduvisla]

Solved it. The problem was in our way of deploying. To build/compile Magento you need the database. But to speed up our build-process we create a shallow copy of the production database to build against. This is pure an empty database with the same tables / schemas, and only the necessary tables needed for compiling are populated. (since you need attributes, configuration, design settings etc. to properly compile your site).

This worked for M2.1.2, but in 2.1.3 I guess some optimisations have been done to the indexer, where it also needed the content of customer_eav_attribute, etc. Adding these tables to our deployment solved the problem for us.

So I'm closing this ticket since it's not Magento-related, but our implementation of deployment related.