1

What is the correct way to completely remove category attributes from the database?

Attributes were added this way using setup script:

$setup->addAttribute('catalog_category', 'seo_text', array(
    'group'             => 'General',
    'label'             => 'Additional Text',
    'type'              => 'text',
    'input'             => 'textarea',

    'backend'           => '',
    'frontend'          => '',
    'visible'           => true,
    'required'          => false,
    'searchable'        => false,
    'user_defined'      => true,
    'visible_on_front'  => true,
    'wysiwyg_enabled'   => false,
    'global'            => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_STORE
));
$setup->addAttribute('catalog_category', 'cat_icon', array(
    'group'             => 'General',
    'label'             => 'Category icon',
    'type'              => 'varchar',
    'input'             => 'select',
    'source'            => 'categoryseo/source_ico',
    'backend'           => '',
    'frontend'          => '',
    'visible'           => true,
    'required'          => false,
    'searchable'        => false,
    'user_defined'      => true,
    'visible_on_front'  => true,
    'wysiwyg_enabled'   => false,
    'global'            => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_STORE
));

Later these attributes were removed in another setup script, I used the standard code which can be found in many tutorials:

$setup = $this;
$setup->startSetup();
$setup->removeAttribute('catalog_category', 'seo_text');
$setup->removeAttribute('catalog_category', 'cat_icon');
$setup->endSetup();

This deleted the attributes from my categories. I also checked in the database and attributes were also removed from the table eav_attribute.

But data of these attributes is still stored in other tables. E.g. in table catalog_category_entity_varchar there is still info about attribute "cat_icon" for many categories.

Shouldn't all this data be automatically removed when I delete the attributes with $setup->removeAttribute(...) method? Does it mean that I have some errors in my Magento so Magento doesn't fully remove the attribute data?

Or did I just delete the attributes incorrectly? If yes, then how to do it properly? How to delete eav attributes and all the data associated with deleted attributes?

6

If you got orphan attribute values it means that there is something wrong with your database.
For example the table catalog_category_int has a constraint on the attribute_id column.

CONSTRAINT `FK_CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,

This means that if the attribute is deleted, all the values for the attribute from the table catalog_category_int will be deleted.
It's the same for the rest of the tables (catalog_category_*).

From experience I saw that usually constraints are lost during a migration process. You export the database but without the constraints. If this is the case then remember in the future to use System->Tools->Backupto create database dumps for migration.

[EDIT]
Here is the code I've used. I've created a simple extension that adds an attribute to the category entity, I edited a category and filled in a value for that attribute, then deleted the attribute through an upgrade script.
Here are the extension files:

app/etc/module/Easylife_Attr.xml - declaration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Attr>
            <codePool>local</codePool>
            <active>true</active>
            <depends>
                <Mage_Catalog />
            </depends>
        </Easylife_Attr>
    </modules>
</config>

app/code/local/Easylife/Attr/etc/config.xml - the configuration file

<?xml version="1.0"?>
<config>
    <modules>
        <Easylife_Attr>
            <version>0.0.1</version>
        </Easylife_Attr>
    </modules>
    <global>
        <resources>
            <easylife_attr_setup>
                <setup>
                    <module>Easylife_Attr</module>
                    <class>Mage_Catalog_Model_Resource_Setup</class>
                </setup>
            </easylife_attr_setup>
        </resources>
    </global>
</config>

app/code/local/Easylife/Attr/sql/easylife_attr_setup/install-0.0.1.php - the install script

<?php

$this->addAttribute('catalog_category', 'dummy', array(
    'group'         => 'General',
    'input'         => 'textarea',
    'type'          => 'text', //can be any type
    'label'         => 'Custom attribute',
    'backend'       => '',
    'visible'       => true,
    'required'      => false,
    'visible_on_front' => true,
    'global'        => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL
));

Now add a value for this dummy attribute for a category.
You should see it in the table catalog_category_entity_text.

After that create an upgrade script to remove the attribute. app/code/local/Easylife/Attr/sql/easylife_attr_setup/upgrade-0.0.1-0.0.2.php

<?php
$this->removeAttribute('catalog_category', 'dummy');

and change in config.xml the version from 0.0.1 to 0.0.2.

Refresh any page and look in the same table catalog_category_entity_text. The value that was added for the dummy attribute is gone. (at least for me).

  • I tested this on a clean installation of Magento 1.7.0.2 and when I remove attribute by using removeAttribute, all the values for that attribute are not deleted and are still in the database. – kahirm Jul 9 '14 at 23:43
  • I've just tested on a clean install of 1.7.0.2 using removeAttribute and the values were deleted. Make sure that your catalog_category_* tables have the constraint I mentioned in my answer. Other than that, make sure you are deleting the correct attribute. – Marius Jul 10 '14 at 7:55
  • Thank you for your help. I'm testing this locally on XAMPP on Windows in clean Magento 1.7.0.2, and for me removeAttribute doesn't delete values. I checked table catalog_category_entity_varchar in phpMyAdmin, constraints are there, but this table still stores the value for attribute "cat_icon" which is of type varchar. Is it possbile that these constraints just doesn't work on XAMPP for some reason? Also, do we talk about the same tables? E.g. I don't see catalog_category_int, but there is catalog_category_entity_int. – kahirm Jul 10 '14 at 15:16
  • @kahirm. Yes we are talking about the same tables. I just spelled them wrong. Sorry for that. It is catalog_category_entity_*. I tested on WAMP, but I don't think this is important. I have no other idea. I give up. I wasn't able to reproduce this. – Marius Jul 10 '14 at 18:48
  • 2
    Doesn't $setup->startSetup(); disable the foreign key checking and all those constraint and cascade delete which you mentioned? In class Varien_Db_Adapter_Pdo_Mysql in startSetup() variable FOREIGN_KEY_CHECKS is set to zero. So maybe $setup->startSetup(); in my setup script is the problem? You don't have startSetup() in your example. – kahirm Aug 2 '14 at 11:08
2

Remove the call to the methods startSetup() and endSetup() from your script.

They are dispensable.

Explanation : By removing the call to those methods, you are preventing the variable FOREIGN_KEY_CHECKS to be disabled and that way the constraints DELETE CASCADE are still executed.

Here is the code of those both methods (Magento 1.9.2.1) :

$this->raw_query("SET SQL_MODE=''");
$this->raw_query("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
$this->raw_query("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");

--

$this->raw_query("SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
$this->raw_query("SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");
1

The removeAttribute function simply removes the attribute but leaves the values in place. What you can do is follow up the removeAttribute with another second query that removes the values.

/**
 * retrieve attribute id so that the records from the attribute table will be deleted, removing the attribute does not
 * cascade on the attribute table
 */
$attributeId = $this->getAttributeId('catalog_category', 'seo_text');

// retrieve attribute table to delete the leftover records after the attribute removal
$attributeTable = $this->getAttributeTable('catalog_category', 'seo_text');
if(!empty ($attributeId) && !empty ($attributeTable)) {
    // remove attribute first so that another record won't be added to its table values
    $this->removeAttribute('catalog_category', 'seo_text');

    // remove attribute values so that no data is leftover after attribute removal
    $installer->run('DELETE FROM ' . $attributeTable . ' WHERE attribute_id = ' . $attributeId);
}

$installer->endSetup();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.