MySQL does not accept this procedure. Can you help, please?

Question

CREATE PROCEDURE reset_xyz_autoincrement
BEGIN

  SELECT @max := MAX(ID)+ 1 FROM ABC; 

  PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?'
  EXECUTE stmt USING @max 

  DEALLOCATE PREPARE stmt;

END $$

I took the code from here: How to Reset an MySQL AutoIncrement using a MAX value from another table?

This is the MySQL output:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN

  SELECT @max := MAX(ID)+ 1 FROM ABC' at line 2

echo_Me · Answer 1 · 2012-12-17T12:03:12.643

1

try this

     CREATE PROCEDURE reset_xyz_autoincrement
          (username varchar(64),password varchar(64))     // here you use your columns
      BEGIN

EDIT:

try this

     DROP PROCEDURE IF EXISTS reset_xyz_autoincrement;
     CREATE PROCEDURE reset_xyz_autoincrement (userid INT)
      BEGIN

edited Dec 17 '12 at 12:03

answered Dec 17 '12 at 11:55

echo_Me

37,078
5
58
78

Still no luck. I get error near '' on line 3 `SELECT @curID := MAX(movies.id) FROM movies;` – Marti Markov Dec 17 '12 at 12:14
now the error is not near of the begin , so it fixed the first error , then error now is in SELECT – echo_Me Dec 17 '12 at 12:19
The query runs normally when not in the procedure. I mean it outputs the correct thing but in the procedure it fails... – Marti Markov Dec 17 '12 at 12:22
make sure that `reset_xyz_autoincrement` and `ABC` and `XYZ` in your real table are three diffrents tables – echo_Me Dec 17 '12 at 12:25

score 1 · Answer 2 · answered Dec 17 '12 at 13:27

You should do follow:

Use function brace
Use semicolon every statement.

delimiter $$
CREATE PROCEDURE reset_xyz_autoincrement() BEGIN

      SELECT @max:=MAX(ID)+ 1 FROM Company_Master; 

      PREPARE stmt FROM 'ALTER TABLE Dept_Master AUTO_INCREMENT = ?';
      EXECUTE stmt USING @max ;

      DEALLOCATE PREPARE stmt;

    END$$

Cynical · Answer 3 · 2012-12-17T13:13:38.137

0

Try adding delimiter $$ before the CREATE PROCEDURE statement.

EDIT:

I lost track in the comments on what is the current version of your procedure, but there is an error on your SELECT statement:

SELECT @yourVar := MAX(ID)+ 1 FROM ABC;

should become

SELECT MAX(ID) + 1 INTO @yourVar FROM ABC;

Plus, I think you should DECLARE myVar INT (assuming it's an Integer) before, and please notice that max is also a MySQL keyword, so I would avoid using that name in my procedure.

edited Dec 17 '12 at 13:13

answered Dec 17 '12 at 11:46

Cynical

9,328
1
15
30

I tried it even before posting but still no luck. I get and error again: `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SELECT @max := MAX(id)+ 1 FROM movies; PREPARE stmt FROM 'ALTER TABL' at line 2` I also tried it with `delimiter ;` no luck too... – Marti Markov Dec 17 '12 at 11:49

MySQL does not accept this procedure. Can you help, please?

3 Answers3