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

[andrew55]

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:

-- 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:

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

[Old Pedant]

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.

[andrew55]

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.