Magento attribute change backend_type from varchar to text

Question

My product attribute exceeded the 255 character limit of varchar, so I came across the solution to change its backend_type to text.

Should this operation be considered safe (in the terms that it will not break anything, or leave garbage in the database)?

Answer 1

If you change the backend_type Magento will also change the EAV table.

It will use catalog_product_entity_text instead of catalog_product_entity_varchar.

You will have to move all the records for that attribute to the new table. If you do not do this operation you may create orphans records and db inconsistencies.

You may try with an insert from select. Best if you write down a small PHP script.

WARNING: Make a full DB backup before you try this.

Answer 2

Even though the shell script by vladPavlov may work, I preferred doing it via a setup script using models and no direct SQL. The following script will change the type from the attribute my_attribute from varchar to text, move all values from the varchar table to the text table and then delete all respective values from the varchar table.

<?php

/**
 * @var $installer Mage_Catalog_Model_Resource_Setup
 */
$installer = $this;

$installer->startSetup();


$varcharTable = $installer->getTable('catalog_product_entity_varchar');
$textTable    = $installer->getTable('catalog_product_entity_text');
// copy all fields but the value_id as this would lead to integrity constraint violations
$fieldsToCopy             = array_keys($installer->getConnection()->describeTable($varcharTable));
$fieldsToCopy             = array_combine($fieldsToCopy, $fieldsToCopy);
$fieldsToCopy['value_id'] = new Zend_Db_Expr ('NULL');

$attributesToMoveFromVarcharToInt = array('my_attribute');

foreach ($attributesToMoveFromVarcharToInt as $attributeCode) {
    $installer->updateAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode, 'backend_type',
        Varien_Db_Ddl_Table::TYPE_TEXT);
    $attributeId = $installer->getAttributeId(Mage_Catalog_Model_Product::ENTITY, $attributeCode);
    $select      = $installer->getConnection()->select()->from($varcharTable, $fieldsToCopy)
        ->where('attribute_id = ?', $attributeId);
    $query       = $installer->getConnection()->insertFromSelect($select, $textTable);
    $installer->run($query);
    $query = $installer->getConnection()->deleteFromSelect($select, $varcharTable);
    $installer->run($query);
}


$installer->endSetup();

Answer 3

I created shell script using method proposed by RicardoT, if anyone would need it:

<?php
include 'abstract.php';

class Mage_Shell_Extend_Attributes extends Mage_Shell_Abstract
{
    protected $_entityTypeId = 4; // Product

    public function __construct() {
        parent::__construct();
        Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
    }

    public function run() {
        $this->_alterAttribute('ATTRIBUTE_CODE');
    }

    protected function _alterAttribute($code) {
        $attribute = Mage::getModel('catalog/resource_eav_attribute')->load($code, 'attribute_code');
        $attribute->setData('backend_type', 'text')
            ->save();

        $write = Mage::getSingleton('core/resource')->getConnection('core_write');
        $query = '
                INSERT INTO catalog_product_entity_text
                    (entity_type_id, attribute_id, entity_id, store_id, value)
                SELECT
                    entity_type_id, attribute_id, entity_id, store_id, value from catalog_product_entity_varchar
                WHERE
                    attribute_id='.$attribute->getId().' AND entity_type_id='.$this->_entityTypeId.';

                DELETE FROM catalog_product_entity_varchar WHERE attribute_id='.$attribute->getId().' AND entity_type_id = '.$this->_entityTypeId.'
            ';
        $result = $write->query($query);
        echo "Processed attribute '".$code."' \n";
    }
}

$app = new Mage_Shell_Extend_Attributes();
$app->run();

Answer 4

This is what worked for me. Firstly, run the following code to change the attributes from varchar to text and move them into the right tables. Make sure you back up your website and database first!

START TRANSACTION;
SET @attribute_id = (SELECT attribute_id FROM rds_eav_attribute WHERE entity_type_id = 4 AND attribute_code = "meta_description");
UPDATE rds_eav_attribute SET frontend_class = 'validate-length maximum-length-XXX' WHERE attribute_code = 'meta_description';
UPDATE rds_eav_attribute SET backend_type = 'text' WHERE attribute_id = @attribute_id;
UPDATE rds_eav_attribute SET note = 'Maximum XXX chars' WHERE attribute_code = 'meta_description';
INSERT INTO rds_catalog_product_entity_text SELECT * FROM rds_catalog_product_entity_varchar WHERE attribute_id = @attribute_id;
DELETE FROM rds_catalog_product_entity_varchar where attribute_id = @attribute_id;
COMMIT;

Because it was the meta description I'm changing, I also had to change app\code\core\Mage\Adminhtml\Block\Catalog\Product\Edit\Tab\Attributes.php lines 117-119 from:

if ($form->getElement('meta_description')) {
    $form->getElement('meta_description')->setOnkeyup('checkMaxLength(this, 255);');
}

to:

if ($form->getElement('meta_description')) {
    $form->getElement('meta_description');
}

Instead of hacking the core file though, I'd copy the file to app\code\local\Mage\Adminhtml\Block\Catalog\Product\Edit\Tab\Attributes.php (although even this isn't recommended, but it's a workaround for the time being. Hopefully we'll see this changed in the Magento core at a later date.