Stored Procedure with ALTER TABLE

Question

I have a need to sync auto_increment fields between two tables in different databases on the same MySQL server. The hope was to create a stored procedure where the permissions of the admin would let the web user run ALTER TABLE [db1].[table] AUTO_INCREMENT = [num]; without giving it permissions (That just smells of SQL injection).

My problem is I'm receiving errors when creating the store procedure. Is this something that is not allowed by MySQL?

DROP PROCEDURE IF EXISTS sync_auto_increment;
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END;

Answer 1

The problem seems to be that you need to change the delimiter. It thinks that the Alter table line is the end of the function. Try this:

DROP PROCEDURE IF EXISTS sync_auto_increment;
DELIMITER //
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END//

DELIMITER ;

Sometimes mysql is still picky about letting you use stored procedures, so you can do try this if you still can't run it:

DROP PROCEDURE IF EXISTS sync_auto_increment;
DELIMITER //
CREATE PROCEDURE set_auto_increment (tableName VARCHAR(64), inc INT)
DETERMINISTIC
READS SQL DATA
BEGIN
ALTER TABLE tableName AUTO_INCREMENT = inc;
END//

DELIMITER ;

Answer 2

I think you'll find you can't put Data Definition Language statements into a stored procedure. A possible exception would be CREATE TEMPORARY TABLE. I have searched the MySQL manual for more information on that point; it says the stored procedure may contain a statement_list, but nowhere defines what that means. But I think that's the case.