5

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
Joseph at SwiftOtter
  • 4,276
  • 5
  • 37
  • 55
  • If this is known issue in magneto (as you did mention), you should post the question to your community site. – ajreal Sep 02 '11 at 21:26
  • Thanks - I'll do that. Sometimes people get better support here, so that is why I tried here first, and now off to the forums :). Thank you all for your help. – Joseph at SwiftOtter Sep 03 '11 at 19:06

3 Answers3

9

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.

ontek
  • 574
  • 6
  • 12
1

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

Community
  • 1
  • 1
Dmytro Zavalkin
  • 5,265
  • 1
  • 30
  • 34
  • Hi there. Yes, I understand what is going on here, and I did see that article already. My point is that Magento should have experienced this error when in development. Obviously they didn't. So, is it some different DB settings, and if it isn't did they test it? – Joseph at SwiftOtter Sep 03 '11 at 00:38
  • Magento multi db engine support is in active development state now, maybe you are right. Magento has public bugtracker, can you file a ticket there? – Dmytro Zavalkin Sep 03 '11 at 07:55
0
alter table table_name add  index index_name (column_name(767));

NOTE : 767 is the number of characters limit upto which MySQL will index columns while dealing with blob/text indexes

Ref : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

Abhishek Goel
  • 18,785
  • 11
  • 87
  • 65