BLOB/TEXT column 'value' used in key specification without a key length

Question

I have developed an extension that works great on Magento until 1.6 (I'm trying Enterprise ed, and I would assume that Community has the same problem, as it has the same code). In my install script, I'm calling $installer->createEntityTables($this->getTable('alphanum/info'));. The installation goes just fine until it comes to the _text entity table. It crashes there! It turns out when I log the sql and run it through PHPmyadmin, this is the error: BLOB/TEXT column 'value' used in key specification without a key length. I looked at there code, and this is what is trying to generate a index on the value column:

->addIndex($this->getIdxName($eavTableName, array('attribute_id', 'value')),
     array('attribute_id', 'value'))
->addIndex($this->getIdxName($eavTableName, array('entity_type_id', 'value')),
     array('entity_type_id', 'value'))

It doesn't have any if statements to make sure it is not of type text. Is there something that I am missing? Do I need to change my DB configuration? Could this be a bug?

I have been kicking around putting an if statement around it (breaking it out of the parent chain) to get the extension in. That should nicely do it. I looked at the previous rev (1.5.something), and it didn't have that index in there. I just can't figure out why it didn't cause a lot of problem when they added it. Makes me wonder if it's my problem somehow?

Don't know if this would help to include the SQL that Magento created:

CREATE TABLE `alphanum_info_text` (
  `value_id` int NOT NULL auto_increment COMMENT 'Value Id',
  `entity_type_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Entity Type Id',
  `attribute_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Attribute Id',
  `store_id` smallint UNSIGNED NOT NULL default '0' COMMENT 'Store Id',
  `entity_id` int UNSIGNED NOT NULL default '0' COMMENT 'Entity Id',
  `value` text NOT NULL COMMENT 'Attribute Value',
  PRIMARY KEY (`value_id`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID` (`entity_type_id`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID` (`attribute_id`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_STORE_ID` (`store_id`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_ID` (`entity_id`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID_VALUE` (`attribute_id`, `value`),
  INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID_VALUE` (`entity_type_id`, `value`),
  CONSTRAINT `FK_ALPHANUM_INFO_TEXT_ENTITY_ID_EAV_ENTITY_ENTITY_ID` FOREIGN KEY     (`entity_id`) REFERENCES `eav_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_ALPHA_NUM_TEXT_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_ALPHANUM_INFO_TEXT_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT='Eav Entity Value Table' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

Answer 1

Looks like this is the issue (having the same issue right now), these two lines:

INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID_VALUE` (`attribute_id`, `value`),
INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID_VALUE` (`entity_type_id`, `value`),

Need to have numerical values listed as such:

INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID_VALUE` (`attribute_id`, `value`(255)),
INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID_VALUE` (`entity_type_id`, `value`(255)),

Plug that in, and it will work. The trick is getting it to insert correctly. For brevity's sake I won't post the whole function, but in Mage_Eav_Model_Entity_Setup::createEntityTables at about line 1341, you need to modify these lines:

            ->addIndex($this->getIdxName($eavTableName, array('attribute_id', 'value')),
                array('attribute_id', 'value'))
            ->addIndex($this->getIdxName($eavTableName, array('entity_type_id', 'value')),
                array('entity_type_id', 'value'))

As follows:

            ->addIndex($this->getIdxName($eavTableName, array('attribute_id', 'value')),
                array('attribute_id', $type == 'text' ? array('name' => 'value', 'size' => 255) : 'value'))
            ->addIndex($this->getIdxName($eavTableName, array('entity_type_id', 'value')),
                array('entity_type_id', $type == 'text' ? array('name' => 'value', 'size' => 255) : 'value'))

I'm not quite sure what you should set the size value to, but I would think that setting it to the full 64k would defeat the purpose of indexing in the first place. Hopefully someone that knows a little bit more about sql than I will chime in.

Hope that helps.

Answer 2

It is not magento fault, rather mysql and your (because you don't know this mysql limitation). See this topic: MySQL error: key specification without a key length