The Rename Table command in MySQL does not update user privileges for the renamed table and its columns (in SQL Server it does, in Oracle it does not). Here is a simple MySQL procedure that does so.
drop procedure if exists rename_table;
delimiter go
create procedure rename_table(
dbold varchar(64), tblold varchar(64),
dbnew varchar(64), tblnew varchar(64)
)
begin
declare exit handler for sqlexception
begin
get diagnostics condition 1
@sqlstate = returned_sqlstate, @sqlmessage = message_text;
select @sqlstate state, @sqlmessage message;
end;
set @sql = concat( 'rename table ', '`', dbold, '`.`', tblold,
'` to `', dbnew, '`.`', tblnew,'`');
-- select @sql;
prepare stmt from @sql;
execute stmt;
drop prepare stmt;
select 'Rename complete' as 'Rename result';
update mysql.tables_priv
set db=dbnew,table_name=tblnew
where db=dbold and table_name=tblold;
select concat( 'Updated ', row_count(), ' mysql.tables_priv rows.' )
as 'tables_priv result';
update mysql.columns_priv
set db=dbnew,table_name=tblnew
where db=dbold and table_name=tblold;
select concat( 'Updated ', row_count(), ' mysql.columns_priv rows.' )
as 'columns_priv result';
flush privileges;
select 'Privileges flushed' as 'Final result';
end;
go
delimiter ;
Here's a simple test case for the procedure:
drop table if exists test.t, test.t9; create table test.t(i int); show create table test.t; call rename_table( 'test', 't', 'test', 't9' ); show create table test.t9;Remember that any stored routine that references the table by name needs to be updated. Last updated 18 Sep 2024 |