MySQL: Resetting AUTO_INCREMENT

Question

I am trying to reset the auto increment value in one of my tables based on the number of rows currently in it. Here is the code I have so far.

SET @numrows = 0; SELECT COUNT(*) total, @numrows := COUNT(*) + 1 numrows FROM maj_user ; ALTER TABLE `maj_user` AUTO_INCREMENT = @numrows ;

This works great if I execute it in MySQL Workbench. However, I need to save this as an SQL file and execute it as part of a database import script. If I do this, I get this:

ERROR 1064 (42000) at line 39: 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 '@numrows' at line 1

Line 39 is the ALTER TABLE statement. Any ideas?

Shouldn't you be setting the AUTO_INCREMENT value to IFNULL(MAX(id),0)+1 or whatever your auto-incrementing column is? By fluke it might be the same as the number of rows, but this is by no means reliable. — tadman
– tadman, Commented Apr 1, 2013 at 19:05
Under typical circumstances, yes. In this particular case, the probability of the MAX and COUNT being equal is a certainty. — Tanoro
– Tanoro, Commented Apr 1, 2013 at 19:07
So you never, ever delete rows? How is your AUTO_INCREMENT ending up in the wrong state, then? — tadman
– tadman, Commented Apr 1, 2013 at 19:08
Earlier parts of this application shave the last x number of records from the end of the table, leaving a special set of records from id #1 up that never get removed. Yes, it does delete, but always the same set. I could use a solution to my question as presented. Let's keep to questions intended to get to one. — Tanoro
– Tanoro, Commented Apr 1, 2013 at 19:21

Display Name is missing · Accepted Answer · 2013-04-01 19:31:25Z

0

Can you change your syntax to skip actually setting @numrows? I'm not sure what the problem is but a workaround seems to be something like:

ALTER TABLE `maj_user` AUTO_INCREMENT = (SELECT COUNT(*) + 1 from maj_user);

answered Apr 1, 2013 at 19:31

Display Name is missing

6,2573 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tanoro Over a year ago

Error Code: 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 '(SELECT COUNT(*) + 1 from maj_user)' at line 1

hd1 Over a year ago

You should use the primary key instead of * on count statements for performance reasons.

tadman Over a year ago

You could probably do this in two queries, one to fetch, one to adjust, if you're not too worried about race conditions where a record might be inserted before you get to twiddling it, or you're prepared to lock the table briefly.

Display Name is missing Over a year ago

This claims setting it to 0 will actually make it one higher than the largest value in the table. I'd create a test table before doing it to your actual table as I cannot verify: stackoverflow.com/questions/3595584/…

Collectives™ on Stack Overflow

MySQL: Resetting AUTO_INCREMENT

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related