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

Question

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

score 81 · Accepted Answer · answered Jul 09 '15 at 11:13

81

It means you don't have privileges to create the trigger with root@localhost user..

try removing definer from the trigger command:

CREATE DEFINER = root@localhost FUNCTION fnc_calcWalkedDistance

answered Jul 09 '15 at 11:13

Aman Aggarwal

17,619
9
53
81

2

I dump my database to sql file, I don't remember to create any function like this... Searching on dump file, I found on last line this commands, I remove and works, thanks. – rafaelfndev Mar 01 '18 at 11:57
1

Thanks, in my case I fixed it by renaming `root`@`localhost to `serverusername`@`localhost` – JWC May Jan 01 '22 at 14:48
Just replace root with database login name – David Bister Feb 13 '23 at 13:28

score 38 · Answer 2 · answered Jul 01 '18 at 08:41

38

Simply remove "DEFINER=your user name@localhost" and run the SQL from phpmyadminwill works fine.

answered Jul 01 '18 at 08:41

Shafiq

963
8
4

4

It worked, that's a perfect answer. And GoDaddy told me that I should purchase a dedicated server for this, but you saved my day. – Visal Varghese Jan 12 '19 at 20:04
Otherwise, change the username to already all permission granted username. – JWC May Jun 04 '23 at 13:13

score 22 · Answer 3 · edited Jan 08 '21 at 04:46

22

In case you are uploading an sql file on cpanel, then try and replace root with your cpanel username in your sql file.

in the case above you could write

CREATE DEFINER = control_panel_username@localhost FUNCTION fnc_calcWalkedDistance

then upload the file. Hope it helps

edited Jan 08 '21 at 04:46

SherylHohman

16,580
17
88
94

answered Sep 29 '16 at 06:06

Junior_swashluv

301
2
4

2

yep it was control_panel_user@localhost for me – FosAvance Jan 13 '17 at 22:55

score 6 · Answer 4 · answered Apr 02 '20 at 17:46

6

remove DEFINER=root@localhost from all calls including procedures

answered Apr 02 '20 at 17:46

Muhammad Waqas

511
6
9

score 0 · Answer 5 · answered Dec 30 '20 at 20:06

0

Change

CREATE DEFINER =  `root`@`localhost` FUNCTION  `fnc_calcWalkedDistance` (

By

FUNCTION  `fnc_calcWalkedDistance` (

answered Dec 30 '20 at 20:06

Rafael Veck

16
2

Harikrishnan · Answer 6 · 2022-12-16T05:31:16.077

0

Either remove the line CREATE DEFINER = root@localhost or change it to CREATE DEFINER = <mysqluser>@<mysqlhost>

If you delete the DEFINER attribute, the default will be the current user account.

Note: DEFINER is an optional attribute for defining a stored procedure or function.

edited Dec 16 '22 at 05:31

answered Sep 05 '22 at 07:28

Harikrishnan

9,688
11
84
127

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

6 Answers6

Linked