1

I have the same problem as Justin, (Can't change MySQL AUTO_INCREMENT) ie trying to change the auto_increment value is ignored.

I have used the:

ALTER TABLE tableName AUTO_INCREMENT = 123;

before and it worked fine. I am also using InnoDB and have a primary key on the column that is set to Auto_increment.

CREATE TABLE `people` (
  `PerRef` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `Surname` varchar(40) NOT NULL,
  PRIMARY KEY (`PerRef`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have added some dummy entries to a table during testing. I have deleted these entries and want to reset the auto_inc value to where it should be.

Surely you don't have to remove the key before being able to reset the Auto_inc value?

What are we missing here?

Community
  • 1
  • 1
David G
  • 337
  • 6
  • 17
  • this may help you http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql – Prasad Khode Nov 10 '14 at 04:28
  • Thanks @Prasad - I had read that post. What seems to be happening is that I can put the Auto_inc value up but never back down again. The highest value in my table is currently 35. I changed Auto_inc to 40 and all good. Then I tried to put it back to 36 and no go ?? – David G Nov 10 '14 at 05:25
  • As I said, I had done some testing and want to return the table to its pretesting state. I don't want gaps in the PerRef numbers. – David G Nov 10 '14 at 06:42

1 Answers1

1

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.

MySQL reset auto increment value by using TRUNCATE TABLE statement:

The TRUNCATE TABLE statement removes all the data of a table and reset auto-increment value to zero. The following illustrates the syntax of the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name;

By using the TRUNCATE TABLE statement, you can only reset the auto-increment value to zero. In addition, all the data in the table is wiped out so you should use the TRUNCATE TABLE statement with extra caution.

MySQL reset auto increment value using DROP TABLE and CREATE TABLE statements:

You can use a pair of statements DROP TABLE and CREATE TABLE to reset the auto-increment column. Like the TRUNCATE TABLE statement, those statements removes all the data and reset the auto-increment value to zero.

DROP TABLE table_name;
CREATE TABLE table_name(...);
Prasad Khode
  • 6,602
  • 11
  • 44
  • 59
  • 1
    I am not trying to set Auto_increment to a value lower than one that is in use! My highest value is 36. I then set Auto_inc to 40 using ALTER TABLE (highest value is still 36) but I cannot set Auto_inc back to 37 ?? – David G Nov 10 '14 at 09:10
  • @DavidG why do you want to do that?? what are you trying to achieve by changing the auto_increment value to 40 and then to 37 when you have the records in your table?? Above its clearly saying that it will not assign a value less than or equal to the current value that it holds. so when you change it to 40 then you cannot decrease the value in auto_increament – Prasad Khode Nov 10 '14 at 09:16
  • What it says is you can't set it lower than the maximum value in the table - which is 36. I only set it to 40 as a test to see if I could then set it back to 37. The reason I want to do this is after I put say 5 dummy records into the table, then I delete them and want to reset the Auto_inc to where it was so that the next real added record will get a consecutive PerRef number. – David G Nov 10 '14 at 10:07
  • This seems to be an InnoDB thing. When I changed the table to MyISAM I can change the Auto_inc back. ?? – David G Nov 11 '14 at 20:50