DB user requires SUPER privilege to create attributes (SQLSTATE[42000])

Question

When I try to create an attribute for this one EE 1.13.1.0 installation, it gives me the following error. myuser is the MySQL user for the Magento installation with table, mytable.

SQLSTATE[42000]: Syntax error or access violation: 1227 Access denied; you need the SUPER privilege for this operation

This user has the following GRANTS just like any other MySQL users for different development instances I have.

GRANT ALL PRIVILEGES ON `mytable`.* TO 'myuser'@'192.%' 

I can create products and anything else, but I just can't create attributes. If I set Super_priv to Y then FLUSH PRIVILEGES, the user can perform attribute creation, but none of the other users I have set up for different instances require Super_priv to be Y.

What's going on?

Answer 1

This is because of the way that EE 1.13+ are handling reindexing jobs. The attribute index is being kicked off by a MySQL Trigger - this function creation/execution requires SUPER privilege to execute.

Here is an example of the AFTER INSERT trigger fired on catalog_eav_attribute

BEGIN
CASE (NEW.is_searchable = 1) OR (NEW.is_visible_in_advanced_search = 1) OR (NEW.is_filterable > 0) OR (NEW.is_filterable_in_search = 1) OR (NEW.used_for_sort_by = 1) OR (NEW.is_used_for_promo_rules = 1) OR (NEW.used_in_product_listing = 1) WHEN TRUE THEN BEGIN UPDATE `enterprise_mview_metadata` AS `mm`
 INNER JOIN `enterprise_mview_metadata_event` AS `me` ON mm.metadata_id = me.metadata_id
SET `mm`.`status` = 2
WHERE (mview_event_id = '5'); END; ELSE BEGIN END; END CASE;
END

Potential workarounds require some guessing on my part:

• drop triggers and recreate them with the current user (perhaps you imported as root?)
• delete the eav triggers altogether and rely on manual reindexing
• obviously you could just grant SUPER to the MySQL user