键规范中使用的BLOB/TEXT列“值"(不带键长度) [英] BLOB/TEXT column 'value' used in key specification without a key length

问题描述

我已经开发了一个扩展，该扩展在Magento上可以使用到1.6版(我正在尝试Enterprise ed，并且我认为Community具有相同的问题，因为它具有相同的代码).在安装脚本中，我正在调用$installer->createEntityTables($this->getTable('alphanum/info'));.直到涉及到_text实体表，安装过程都很好.它在那里崩溃！原来，当我登录sql并通过PHPmyadmin运行它时，这是错误:BLOB/TEXT column 'value' used in key specification without a key length.我看了看那里的代码，这就是试图在value列上生成索引的原因:

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

它没有任何if语句来确保它不是文本类型.有什么我想念的吗?我需要更改数据库配置吗?这可能是错误吗?

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?

我一直在围绕它放置一个if语句(将其从父链中断开)以获取扩展名.这样做应该很好.我看了以前的版本(1.5.something)，那里没有那个索引.我只是想不出为什么当他们添加它时并没有引起很多问题.让我想知道这是否是我的问题?

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?

不知道这是否有助于包含Magento创建的SQL:

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

推荐答案

看起来像这是问题(现在有同样的问题)，这两行:

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

需要这样列出数值:

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行中，您需要修改以下行:

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

如下:

            ->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.

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.

希望有帮助.

这篇关于键规范中使用的BLOB/TEXT列“值"(不带键长度)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！