I really hope I'm not overlooking something simple...
I'm writing a cleanup script to remove database items created by my
application. One of the things I would like to remove are all user
accounts created through my application... This is proving to be
harder than it sounds.
I should note that it's a mysql script I plan to have the
administrator run as 'mysql -u root -p < uninstall.txt' - therefore
I'm limited in what I can do (to some degree)
After unsuccessfully trying to solve this through a variety of other
means, I've come to the conclusion that if I record all of the
usernames I create in a table, then I might be able to iterate over
that table and drop my users. This means creating a stored procedure
temporarily for the sole purpose of getting the benefits of CURSORs..
So I've come up with this:
DELIMITER //
CREATE PROCEDURE deleteAllUsers() DETERMINISTIC MODIFIES SQL DATA
BEGIN
DECLARE name VARCHAR(12);
DECLARE done INT DEFAULT 0;
DECLARE allUsers CURSOR FOR SELECT username FROM user WHERE active = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN allUsers;
REPEAT
FETCH allUsers INTO name;
DROP USER name;
UNTIL done END REPEAT;
CLOSE allUsers;
END;
//
DELIMITER ;
It looks simple enough, however DROP USER tries to drop 'name' rather
than the next username in the list :(
I feel like I'm overlooking something really simple - however for the
life of me I can't figure it out. Is it possible to use DROP USER with
a variable name in a stored procedure? If not, is there some way to
drop a bunch of users given a table of usernames? Perhaps this problem
has only ever been tackled using a higher level language - but I was
hoping to solve it in a little mysql script.
Thanks. Hope you have a great 2008!
--
Eddie Cornejo
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GIT d? s: a- C+++ UL+++ P++ L++ E- W+ N- o K- w++
O M-- V PS+ PE Y PGP++ t 5 X+ R tv-- b+ DI++++ D++
G e++ h r+++ y+++
------END GEEK CODE BLOCK------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
