`CREATE ALGORITHM=UNDEFINED DEFINER` requires super permissions

Question

I am trying to import one of my old databases to my new database.

To do this I created a user with the same privileges as before:

Almost every command seems to work except the following:

CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `wp_affiliate_wp_campaigns` AS select `wp_affiliate_wp_visits`.`affiliate_id` AS `affiliate_id`,`wp_affiliate_wp_visits`.`campaign` AS `campaign`,count(`wp_affiliate_wp_visits`.`url`) AS `visits`,count(distinct `wp_affiliate_wp_visits`.`url`) AS `unique_visits`,sum(if((`wp_affiliate_wp_visits`.`referral_id` <> 0),1,0)) AS `referrals`,round(((sum(if((`wp_affiliate_wp_visits`.`referral_id` <> 0),1,0)) / count(`wp_affiliate_wp_visits`.`url`)) * 100),2) AS `conversion_rate` from `wp_affiliate_wp_visits` group by `wp_affiliate_wp_visits`.`affiliate_id`,`wp_affiliate_wp_visits`.`campaign`;

Which gave me the following error:

Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

This is odd as this table was created with the a user with the same permissions as my old database (i.e. all permissions for table foo).

So my question is:

• What exactly does this command do?
• Why do I suddenly need super permission to execute it when my old database never did?

Answer 1

I ran into this identical problem. What worked for me was to take off the:

  ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER

You would then just have:

  CREATE VIEW `wp_affiliate_wp_campaigns'...

Worked like a charm. Best of luck!