Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Mar 2012
    Posts
    21
    Thanks
    5
    Thanked 0 Times in 0 Posts

    HELP: 1227 - Access denied; you need the SUPER privilege for this operation

    I am in the process of moving a database over from a Goadaddy shared account to a new hosting account. All of the other databases went in fine, except a command that is part of the database in question. Every time I try to run the command through, I get the error:

    #1227 - Access denied; you need the SUPER privilege for this operation

    Here is the original command/query from the database that as in Godaddy. In Godaddy, the usernames and database names are the same:

    Code:
    -- Stand-in structure for view `username2`
    --
    CREATE TABLE `username2` (
    `username2` varchar(255)
    );
    -- --------------------------------------------------------
    --
    -- Structure for view `username2`
    --
    DROP TABLE IF EXISTS `username2`;
    
    CREATE ALGORITHM=UNDEFINED DEFINER=`community55`@`%` SQL SECURITY DEFINER VIEW `community55`.`username2` AS select `community55`.`phpbb_users`.`username` AS `username2` from 
    
    `community55`.`phpbb_users`;

    Here is the command/query I am trying to run through phpmyadmin in my new hosting account:
    Code:
    -- Stand-in structure for view `username2`
    --
    CREATE TABLE `username2` (
    `username2` varchar(255)
    );
    -- --------------------------------------------------------
    --
    -- Structure for view `username2`
    --
    DROP TABLE IF EXISTS `username2`;
    
    CREATE ALGORITHM=UNDEFINED DEFINER=`mydatabaseuser`@`%` SQL SECURITY DEFINER VIEW `mydatabasename`.`username2` AS select `mydatabasename`.`phpbb_users`.`username` AS `username2` from `mydatabasename`.`phpbb_users`;
    I just can't figure out why I still keep getting the error when I try to run the query/command through in phpmyadmin. It's probably something simple and stupid I'm missing, but after hours, I still can't figure it out. I've seen before that older versions of MYSQL do not allow algorithm clauses, but I believe the MYSQL version of the new hosting account is 5.1.65, so I think that you should be recent enough. Any suggestions greatly appreciated.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Don't ask me why, but even if a user is granted CREATE TABLE privileges, MySQL doesn't then automatically grant them CREATE VIEW privileges. The system admin (super user) must grant CREATE VIEW separately. So tell your new host to grant that permission to you. And while you are at it, tell them to grant you SHOW VIEW privileges. Would you believe that those two don't come together, either?

    This whole topic is a noted bug in MySQL: Users can use mysqldump to save a database that they then aren't able to restore because if this. It's really a nutso limitation in MySQL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2012
    Posts
    21
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thank you. It was a godaddy thing. I had someone help me do a mysqldump with SSH, and the command didn't even show up. It went in just fine to the new hosting account.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •