Geek Answers Handbook

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

I developed an extension that works fine on Magento up to 1.6 (I'm trying Enterprise ed, and I would assume that the Community has the same problem as the same code). In my install script I call $installer->createEntityTables($this->getTable('alphanum/info'));. Installation is fine until it reaches the _text table. He is falling there! It turns out that when I file sql and run it through PHPmyadmin, this is a mistake: BLOB/TEXT column 'value' used in key specification without a key length. I looked at the code there, and this is what tries to generate the index in 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 does not have operators ifto make sure that it is not type text. Is there something I'm missing? Do I need to change the database configuration? Could this be a mistake?

I started feeding instructions around me if(breaking it from the parent chain) to get the extension. That should do it. I looked at the previous rev (1.5.something) and it did not have this index. I just can't understand why this did not cause a lot of problems when they added it. Makes me wonder if this is my problem?

I don't know if this will help enable the SQL created by Magento:

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
+5
source share
3 answers

This seems to be the problem (with the same problem 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`),

You 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)),

, . . , Mage_Eav_Model_Entity_Setup::createEntityTables 1341 :

            ->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'))

:

            ->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'))

, , , 64k . , - sql, .

, .

+9

magento, mysql ( mysql). . : MySQL:

0
alter table table_name add  index index_name (column_name(767));

NOTE : 767 is the number of characters to which MySQL will index columns when working with blob / text indexes

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

0
source

More articles:


All Articles