Magento 2.1.3 : Indexer creates an extra index on production, not on local development
TL;DR.
I think there is a problem with your created_in
column from the customer_entity
table in one of the environments. For some reason the size of the column is not taken into consideration.
May be the mysql version but this is just a speculation.
[EDIT]
The above assumption was wrong.
The problem appears because the attribute created_in
is marked as is_filterable_in_grid = 1
and is_searchable_in_grid = 0
in the customer_eav_attribute
table.
Make is_searchable_in_grid = 1
(as it should be by default) and everything should be OK.
Make it
[/EDIT]
Long version: Here are some pieces of the puzzle I found. You can try to put them all together. I hope there are enough pieces to realize the big picture.
Here is the code that builds the flat tables. It's an abstract method but the way the indexes are created is clear Magento\Framework\Indexer\GridStructure::createFlatTable()
.
This part is important.
if ($field['type'] === 'filterable') {
$table->addIndex(
$this->resource->getIdxName($tableName, $name, AdapterInterface::INDEX_TYPE_INDEX),
$name,
['type' => AdapterInterface::INDEX_TYPE_INDEX]
);
}
This means that if the field is declared as filtrerable
it will be added as an index in the flat table.
Now let's look at the indexer.xml
file from the customer module.. The created_in
attribute is marked as filterable and it has the type text
.
Now lets go back to the Indexer class before and see what type text means..
protected $columnTypesMap = [
'varchar' => ['type' => Table::TYPE_TEXT, 'size' => 255],
'mediumtext' => ['type' => Table::TYPE_TEXT, 'size' => 16777216],
'text' => ['type' => Table::TYPE_TEXT, 'size' => 65536],
'int' => ['type' => Table::TYPE_INTEGER, 'size' => null],
'date' => ['type' => Table::TYPE_DATE, 'size' => null],
'datetime' => ['type' => Table::TYPE_DATETIME, 'size' => null],
'timestamp' => ['type' => Table::TYPE_TIMESTAMP, 'size' => null],
];
This means that the the column type will be text and the size will be 65536.
But for some reason the size is ignored when creating the table. And you get the error when you try to add the index because there is no size on the column (as the error states).
i tried to reindex on my instance and indeed the column created_in
does not have an index. That's what made me speculate that there is mysql version issue, or maybe a configuration issue. For the record I have mysql 5.7.16-0ubuntu0.16.04.1
.
Other info that might prove useful, the actual query for creating the table is executed in Magento\Framework\DB\Adapter\Pdo\Mysql::createTable
. Good place to start debugging and then go back in the stack trace.
Also, before trasnforming the index configuration to the actual sql text, there is an other validation done my the method _getIndexesDefinition
in the same Magento\Framework\DB\Adapter\Pdo\Mysql
class.
That's all I have for now. Happy debugging.
[EDIT]
This is bugging me so I decided to invest a bit in it.
I debugged the createFlatTable
method mentioned above and I see that for the created_in
attribute comes up with type searchable
even if in the indexer.xml
it appears as filterable
. I don't know yet why that happens. will post back when I find out why.
[Second degree edit] - this is actually the solution, the rest is just debugging steps I took.
Continuing the idea from the edit above, the type of the flat column filterable
or searchable
is determined for the customer attributes by the method Magento\Customer\Model\Indexer\AttributeProvider::getType
.
protected function getType(Attribute $attribute)
{
if ($attribute->canBeSearchableInGrid()) {
$type = 'searchable';
} elseif ($attribute->canBeFilterableInGrid()) {
$type = 'filterable';
} else {
$type = 'virtual';
}
return $type;
}
In your case, you get the attribute type filterable
and because this is a text attribute mysql cannot create the index on it.
Make sure your attribute is marked as is_searchable_in_grid
and it will get the type searchable
when the flat table is built and no index will be added to it.