Ошибка 1227 mysql

For me the issue was ( for a very strange reason ) the fact that root had Host of % instead of localhost

I received the above error when trying to DROP USER;
Privileges as suggested in the answer above — I already had, so the solution wasn’t suitable for me.

The DB looked like this:

mysql> drop user 'testuser'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> select Host,User,drop_priv from user;
+------+------------------+-----------+
| Host | User             | drop_priv |
+------+------------------+-----------+
| %    | mysql.infoschema | N         |
| %    | mysql.session    | N         |
| %    | mysql.sys        | N         |
| %    | root             | Y         |
+------+------------------+-----------+

And

mysql> SHOW GRANTS FOR 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I tried many things, but eventually I changed the Host from % to localhost for security concerns, nothing else.

mysql> UPDATE user SET Host='localhost' WHERE user='root' LIMIT 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I don’t know why, but it worked.

mysql> quit

$ mysql -u root -p
.. ENTER (NO PASSWORD) ..

mysql> drop user 'testuser'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

No idea why, but hope it will help others …
I’m using CentOS which has SELinux and other stuff, which maybe other components are correlated with this. don’t know.

Hello, I am currently having an issue with MySQL!

What’s going wrong here? I am a cPanel user, and yes I have searched this and found no definitive answers. It appears this is more specific than other people with the same error codes issues. Please add a detailed response that I can follow along with! P.s I am using a shared hosting account.

DELIMITER $$--
-- Functions
--
CREATE DEFINER =  `root`@`localhost` FUNCTION  `fnc_calcWalkedDistance` (

`steamid64` BIGINT UNSIGNED
) RETURNS INT( 10 ) UNSIGNEDNO SQL BEGIN DECLARE finished INTEGER DEFAULT 0;

DECLARE distance INTEGER DEFAULT 0;

DECLARE x1, x2, z1, z2 FLOAT;

DECLARE curs CURSOR FOR SELECT x, z
FROM log_positions
WHERE  `steamid` = steamid64
ORDER BY  `timestamp` DESC ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished =1;

OPEN curs;

FETCH curs INTO x1, z1;

SET x2 = x1;

SET z2 = z1;

calculate : LOOPFETCH curs INTO x1, z1;

IF finished =1 THEN LEAVE calculate;

END IF ;

SET distance = distance + SQRT( POW( x2 - x1, 2 ) + POW( z2 - z1, 2 ) ) ;

-- SET distance = distance + 1;
SET x2 = x1;

SET z2 = z1;

END LOOP calculate;

CLOSE curs;

RETURN distance;

END$$

Here is the error code:

MySQL said: Documentation

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation 

Skip to content