CODEWITHSUNDEEP
mysql
L. Cornelius Dol
L. Cornelius Dol

Reputation: 64065

Auto-increment is not resetting in MySQL

I am trying to set up a script to generate a particular set of test data into my database, at the beginning of which I want to clear the tables concerned without dropping constraints (because the test data is not the appropriate place to be rebuilding constraints) and reset the AUTO_INCREMENT for each table since setting up the test data is much, much simpler if I can hard-code many of the IDs.

For example, I have two statements like this (there's a pair for nearly every table):

DELETE FROM AppointmentAttr
ALTER TABLE AppointmentAttr AUTO_INCREMENT = 1

and while the records are deleted, the auto-increment value is not reverting to 1, even though all the documentation and SO answers I can find indicate that this should work.

If I do the same statement in MySQL Workbench it also does not revert it.

This is on an INNODB database.

What am I missing?

(Note: I cannot use TRUNCATE due to the presence of constraints).

Upvotes: 19

Views: 46976

Answers (11)

Liron
Liron

Reputation: 555

I had a similar issue where after altering the table and modifying the auto increment value to a higher value, the auto increment was set on the table as null.

I fixed it, by renaming the table:

ALTER TABLE `table1` RENAME `table1_old`;

Afterwards I've created the table again from using the create statement, while changing the auto increment value on the create.

You can get the old table create statement using the following:

SHOW CREATE TABLE `table1_old`;

Last, after creating the table, I copied the values from the old table to the new using the following statement:

 INSERT INTO `table1`
 SELECT * FROM `table1_old`;

Now after doing that, I have the old data with the auto increment value to the value it got, and all new records will start with my new auto increment value.

Upvotes: 0

needHelpInTomcatAndSpring
needHelpInTomcatAndSpring

Reputation: 1

Adding the schema to the query fixes it.

ALTER TABLE schema.table AUTO_INCREMET=0;

Upvotes: -1

Mara Ormston
Mara Ormston

Reputation: 1856

MySQL does not permit you to decrease the AUTO_INCREMENT value, as specified here: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

Even with your constraints, I would try one of the following:

  1. Explicitly insert your identities for your test data. MySQL doesn't have problems with this, unlike some other database engines
  2. Delete and recreate your identity column (or just change it from being an identity), if the constraints aren't on it itself.
  3. Not use an Identity column and use another method (such as a procedure or outside code) to control your Identity. This is really a last resort and I wouldn't generally recommend it...

Note from OP: It was (1) that was what I needed.

Note from @Alf47: If the correct AUTO_INCREMENT value is not being reflected in information_schema.tables, run ANALYZE TABLE on the table in question.

Upvotes: 20

Suspended
Suspended

Reputation: 1190

In non-problematic circumstances you can do

ALTER TABLE tbl AUTO_INCREMENT = 0;

which brings auto_increment value down to the lowest allowed at the time.

Upvotes: 0

Tomas
Tomas

Reputation: 21

This worked for me hope it helps. SET @autoid = 0; UPDATE users set id = @autoid := (@autoid+1); ALTER TABLE users AUTO_INCREMENT = 1;

Upvotes: 2

caro
caro

Reputation: 892

I'm sure this has been long answered but when i need to truncate and can't I just do a set foreign_key_checks = 0 then run my truncate and then set foreign_key_checks = 1.

Upvotes: 2

dennis
dennis

Reputation: 1

ALTER TABLE table_name AUTO_INCREMENT = value;
This worked for me, I had to set it to the last record in my database while going through the operations panel never worked for me.

Upvotes: 0

Null Mastermind
Null Mastermind

Reputation: 1094

ALTER TABLE tbl DROP COLUMN id;
ALTER TABLE tbl ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
in your phpMyAdmin

Upvotes: 0

Luke359
Luke359

Reputation: 457

I've run into this problem when I've deleted middle rows from my table. My answer would be to INSERT NEW DATA TO NOT EXISTING ID. I expect that my answer still be usefull even if it's PHP not MYSQL.

  1. First fetch your data.
  2. if found not existing row Insert values and exit;

  3. else if not found in whole loop then do insertion for default value;

    $rez = mysql_query("SELECT * FROM users");
$exists = 1;
while($row = mysql_fetch_array($rez)){
        if ( $exists != $row{'id'} ){
               echo "found not existing id: ".$exists;
               INSERT INTO users VALUES($exists,.. ,..); 
               exit;
         } $exists += 1;
 }
 INSERT INTO users VALUES(NULL,.. ,..);  ##auto_inc column converts NULL to latest

I HOPE it will help somehow.

Upvotes: 1

hd1
hd1

Reputation: 34677

Can you not drop the relevant, auto increment column and recreate it? Example follows:

;;assuming your column is called id and your table is tbl
ALTER TABLE tbl DROP COLUMN id;
ALTER TABLE tbl ADD COLUMN id BIGINT UNSIGNED DEFAULT 1 PRIMARY KEY FIRST;

This should work, but I don't use MySQL, just going off the docs. If you need further help, leave a comment and I'll do my best to help out.

Upvotes: 4

Namphibian
Namphibian

Reputation: 12221

From what I can see about the alter table statement.

You can reset auto increment value by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset auto increment value is as follows:

ALTER TABLE table_name AUTO_INCREMENT = value;

You specify the table name after the ALTER TABLE clause and the value which we want to reset to in the expression AUTO_INCREMENT = value.

Notice that the value must be greater than or equal to the current maximum value of the auto-increment column.

Which is where your problem lies I suspect. So basically you are left with a couple of options as follows:

  1. TRUNCATE TABLE: which according to our discussion is not a option
  2. DROP and RECREATE the table: A long and painful experience

  3. ALTER auto number column: I have not tried this but you could theoretically alter the primary key column from auto number to a int and then make it a auto number again. Something like:

    ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT NOT NULL;
ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT AUTONUMBER NOT NULL;

Hope these help a little.

Upvotes: 10

Related Questions