Magento Database – How to Delete Custom EAV Attributes

attributescustom-optionsdatabaseeav

I want to remove unused EAV attributes directly from the database before I move my store live. Attributes can be found in eav_attribute table, can I delete attributes from this table? Is it safe? Or do I also need to edit other EAV tables?

Best Answer

See Mage_Eav_Model_Entity_Setup::removeAttribute(). It takes two arguments - the first is the entity code, and the second is the attribute code.

Edit - to run from a non-installation scope:

<?php
include 'app/Mage.php';
Mage::app();
$setup = Mage::getResourceModel('catalog/setup','catalog_setup');
$setup->removeAttribute('catalog_product','attr_code');

Related Solutions

Magento – How to completely delete EAV attribute and all its data from the database

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

Database Optimization – How to Reduce DB File Size

Log tables if not cleared frequently can build up and make big database sizes. As pointed out by others this should really be handled by magentos cron if log cleaning is enabled in admin, but for the purpose of reducing your backup any tables prefixed with log_ can pretty safely be truncated.

Also sales and customer data is unlikely needed in a development environment so you could skip their data in your backup.

Have a look at the magerun cli tool, specifically the db:dump command. The --strip and --compression flags can be quite useful, you should be able to reduce your dump file size significantly.

e.g ./n98-magerun.phar db:dump --compression="gzip" --strip="@development" db.sql will skip the data in all sales, customer and log tables and also gzip the sql file. Behind the scenes it will do a mysqldump command similar to this:

mysqldump --single-transaction --quick 
--ignore-table=magento_db.catalogsearch_fulltext 
--ignore-table=magento_db.catalogsearch_query 
--ignore-table=magento_db.catalogsearch_result 
--ignore-table=magento_db.core_session 
--ignore-table=magento_db.customer_address_entity 
--ignore-table=magento_db.customer_address_entity_datetime 
--ignore-table=magento_db.customer_address_entity_decimal 
--ignore-table=magento_db.customer_address_entity_int 
--ignore-table=magento_db.customer_address_entity_text 
--ignore-table=magento_db.customer_address_entity_varchar 
--ignore-table=magento_db.customer_entity 
--ignore-table=magento_db.customer_entity_datetime 
--ignore-table=magento_db.customer_entity_decimal 
--ignore-table=magento_db.customer_entity_int 
--ignore-table=magento_db.customer_entity_text 
--ignore-table=magento_db.customer_entity_varchar 
--ignore-table=magento_db.dataflow_batch 
--ignore-table=magento_db.dataflow_batch_export 
--ignore-table=magento_db.dataflow_batch_import 
--ignore-table=magento_db.dataflow_import_data 
--ignore-table=magento_db.dataflow_session 
--ignore-table=magento_db.log_url 
--ignore-table=magento_db.log_url_info 
--ignore-table=magento_db.log_visitor 
--ignore-table=magento_db.log_visitor_info 
--ignore-table=magento_db.log_visitor_online 
--ignore-table=magento_db.newsletter_problem 
--ignore-table=magento_db.newsletter_queue 
--ignore-table=magento_db.newsletter_queue_link 
--ignore-table=magento_db.newsletter_queue_store_link 
--ignore-table=magento_db.newsletter_subscriber 
--ignore-table=magento_db.newsletter_template 
--ignore-table=magento_db.report_compared_product_index 
--ignore-table=magento_db.report_event 
--ignore-table=magento_db.report_viewed_product_aggregated_daily 
--ignore-table=magento_db.report_viewed_product_aggregated_monthly 
--ignore-table=magento_db.report_viewed_product_aggregated_yearly 
--ignore-table=magento_db.report_viewed_product_index 
--ignore-table=magento_db.sales_bestsellers_aggregated_daily 
--ignore-table=magento_db.sales_bestsellers_aggregated_monthly 
--ignore-table=magento_db.sales_bestsellers_aggregated_yearly 
--ignore-table=magento_db.sales_flat_creditmemo 
--ignore-table=magento_db.sales_flat_creditmemo_comment 
--ignore-table=magento_db.sales_flat_creditmemo_grid 
--ignore-table=magento_db.sales_flat_creditmemo_item 
--ignore-table=magento_db.sales_flat_invoice 
--ignore-table=magento_db.sales_flat_invoice_comment  
--ignore-table=magento_db.sales_flat_invoice_grid 
--ignore-table=magento_db.sales_flat_invoice_item 
--ignore-table=magento_db.sales_flat_order 
--ignore-table=magento_db.sales_flat_order_address 
--ignore-table=magento_db.sales_flat_order_grid 
--ignore-table=magento_db.sales_flat_order_item 
--ignore-table=magento_db.sales_flat_order_payment 
--ignore-table=magento_db.sales_flat_order_status_history 
--ignore-table=magento_db.sales_flat_quote 
--ignore-table=magento_db.sales_flat_quote_address 
--ignore-table=magento_db.sales_flat_quote_address_item 
--ignore-table=magento_db.sales_flat_quote_item 
--ignore-table=magento_db.sales_flat_quote_item_option 
--ignore-table=magento_db.sales_flat_quote_payment 
--ignore-table=magento_db.sales_flat_quote_shipping_rate 
--ignore-table=magento_db.sales_flat_shipment 
--ignore-table=magento_db.sales_flat_shipment_comment 
--ignore-table=magento_db.sales_flat_shipment_grid 
--ignore-table=magento_db.sales_flat_shipment_item 
--ignore-table=magento_db.sales_flat_shipment_track 
--ignore-table=magento_db.sales_order_aggregated_created 
--ignore-table=magento_db.sales_order_aggregated_updated 
--ignore-table=magento_db.sales_order_tax 
--ignore-table=magento_db.sales_order_tax_item 
--ignore-table=magento_db.sales_payment_transaction 
--ignore-table=magento_db.sales_recurring_profile 
--ignore-table=magento_db.sales_recurring_profile_order 
--ignore-table=magento_db.sales_refunded_aggregated 
--ignore-table=magento_db.sales_refunded_aggregated_order 
-h'host' -u'username' -p'password' 'magento_db' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip -c  >> 'db.sql.gz'`

Best Answer

Related Solutions

Magento – How to completely delete EAV attribute and all its data from the database

Database Optimization – How to Reduce DB File Size

Related Topic