0

I create a procedure to try to change host wildcard into 'localhost' but It return me an error when process the host error with wildcard

This is my code

DELIMITER //
CREATE OR REPLACE PROCEDURE wildcardprocedure()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE a CHAR(50);
DECLARE o CHAR(20);

DECLARE cur1 CURSOR FOR SELECT user FROM user WHERE Host='%';

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

OPEN cur1;

REPEAT

  FETCH cur1 INTO o;
  SELECT @a:= CONCAT(o,'@localhost');
  RENAME USER '@o' TO '@a';

UNTIL done END REPEAT;

CLOSE cur1;
END//

I need to check what users are using a wildcard, save the list of users in a variable and them RENAME USER to change the Host in a loop and schedule it with event_schedule.

I tried an easy form but It doesn't work UPDATE mysql.user set Host=localhost where Host='%';

Thanks!!

Paul T.
  • 4,703
  • 11
  • 25
  • 29
Alejandro Pérez Fuentes
  • 1
  • User-defined variables names won't replace with their values. SQL is not PHP. You must use prepared statement. – Akina Sep 21 '22 at 11:33
  • *I create a procedure to try to change host wildcard into 'localhost'* You'll damage all existing roles. – Akina Sep 21 '22 at 11:35

0 Answers0