1

I have an attribute in use on Magento where the data has been split between catalog_product_entity_varchar and catalog_product_entity_decimal due to the datatype being changed in the database.

The correct data is stored in the majority in catalog_product_entity_varchar, however products added whilst the datatype was changed to decimal are always referencing the catalog_product_entity_decimal table for this attribute, even when that entry is deleted and then a new value saved from inside magento.

This leads me to believe there is a setting somewhere that tells magento which table to use for each attribute, per product. Does anyone know where this is?

  • What is your attribute input type? – Lord Skeletor Jan 29 '15 at 17:01
  • it was text, but was changed to price. I saw no reason why price couldn't remain as I believe it's only used to tell Magento what type out display to use in the admin area. – Duncan Wardlaw Jan 29 '15 at 17:41
  • Actually it's also used to determine backend type and entity table. – Lord Skeletor Jan 29 '15 at 18:59
2

Whenever you save attribute from the admin panel, Magento will run validation on it and it will change your backend type based on input type. You can see that in getBackendTypeByInput method inside Mage_Adminhtml_Catalog_Product_AttributeController class.

   /**
     * Detect backend storage type using frontend input type
     *
     * @return string backend_type field value
     * @param string $type frontend_input field value
     */
    public function getBackendTypeByInput($type)
    {
        $field = null;
        switch ($type) {
            case 'text':
            case 'gallery':
            case 'media_image':
            case 'multiselect':
                $field = 'varchar';
                break;

            case 'image':
            case 'textarea':
                $field = 'text';
                break;

            case 'date':
                $field = 'datetime';
                break;

            case 'select':
            case 'boolean':
                $field = 'int';
                break;

            case 'price':
                $field = 'decimal';
                break;
        }

        return $field;
    }

You can use this as a guideline when adding new attributes via install script. If you are adding attribute via admin you don't have to worry about that. Of course, if you modify backend or frontend type directly from DB, Magento will validate and 'fix' the attribute on next save.

In other words you should never, ever change attribute properties directly from DB. Especially if the attribute already has some values saved. Doing this will result in attribute values being written in different entity type tables, which is what happened in your case. This will result in various issues, e.g. attribute disappearing from layered navigation, not searchable or filterable, cannot be saved, etc.

Fix for this is simple. First you need to determine what your backend/input types are. Then you need to make sure that the combination is valid using the code above and that it won't be changed in the future by manual editing.

Let's assume you want decimal/price. This means that your values should be saved in catalog_product_entity_decimal table. You will have to check all the other catalog_product_entity_* tables and remove all entries associated with your attribute_id. If you need to preserve the data you can also export rows before you delete them (without value_id) and import them in catalog_product_entity_decimal table.

This should fix your attribute. Remember to reindex after this is done.

0

That is defined in the backend_type field in the eav_attribute table

PS : For anyone reading this, this is obviously not recommended to change this value directly in the database.

  • Thats where the attribute type was edited, and it's been changed back. However what I'm trying to do is tell the products that were created when the backend_type was decimal to instead save their data into the varchar table and read the data from this location as well. – Duncan Wardlaw Jan 29 '15 at 15:50
  • There is no value per product. Try flushing the two Magento caches, and give it another try. – Mayers Jan 29 '15 at 15:55
  • I've flushed all the cache's and indexed the site but still no joy. The entries in the catalog_product_entity_varchar and catalog_product_entity_decimal tables for each product are the same except for the value_id, which is why I thought there must be some kind of table telling Magento where to look. – Duncan Wardlaw Jan 29 '15 at 16:00
0

I found that the best way to resolve this was to create a new, replacement attribute on Magento with a default value of something unique.

I then created a PHP script that would call both sets of values from the two tables catalog_product_entity_decimal and catalog_product_entity_varchar.

A few if statements that allowed the script to alternate between empty and not empty columns and create a single column as output, which was then written into the new attribute

The corrupted attribute was then deleted after making sure that the data was verified and working

This provided the best and safest solution and I can definately recommend not altering the type of data as previously mentioned, but if it does happen you're best off getting the data correctly restored instead of trying to do what I was planning initially and lie to Magento about the location of the data!

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.