2

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?

Tanoro
  • 871
  • 2
  • 10
  • 30
  • 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 Apr 01 '13 at 19:05
  • Under typical circumstances, yes. In this particular case, the probability of the MAX and COUNT being equal is a certainty. – Tanoro Apr 01 '13 at 19:07
  • So you never, *ever* delete rows? How is your `AUTO_INCREMENT` ending up in the wrong state, then? – tadman Apr 01 '13 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 Apr 01 '13 at 19:21
  • Just trying to understand your requirements better. – tadman Apr 01 '13 at 19:50

1 Answers1

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);
Display Name is missing
  • 6,197
  • 3
  • 34
  • 46
  • 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 – Tanoro Apr 01 '13 at 19:37
  • You should use the primary key instead of * on count statements for performance reasons. – hd1 Apr 01 '13 at 19:38
  • 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. – tadman Apr 01 '13 at 19:49
  • 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: http://stackoverflow.com/questions/3595584/updating-auto-increment-value-of-all-tables-in-a-mysql-database – Display Name is missing Apr 01 '13 at 19:53