Auto increment

when I restart mysql server, in some tables the auto increment fields are reseted back to 1. how can I prevent it?
LVL 11
BuffonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

todd_farmerCommented:
This isn't standard behavior.  Do you have anything special in your options files or an initialization file that is being executed?
0
BuffonAuthor Commented:
How can I tell if something is special?
0
todd_farmerCommented:
How is MySQL started?  If it is a Windows service, check the path to the executable and see what it is.  You're generally looking for something like "init-file".
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

BuffonAuthor Commented:
it runs on Linux server.
0
todd_farmerCommented:
Can you post the SHOW CREATE TABLE table_name for a couple of the affected tables?
0
BuffonAuthor Commented:
CREATE TABLE `tbl_battles` (
  `battle_id` int(10) unsigned NOT NULL auto_increment,
  `start_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `lowest_level` tinyint(3) unsigned NOT NULL default '0',
  `highest_level` tinyint(3) unsigned NOT NULL default '0',
  `timeout_limit` smallint(5) unsigned NOT NULL default '0',
  `initiator_character_id` int(10) unsigned NOT NULL default '0',
  `battle_category_id` tinyint(3) unsigned NOT NULL default '0',
  `win_team_number` tinyint(3) unsigned NOT NULL default '3',
  `map_id` int(4) unsigned default NULL,
  `archive_id` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`battle_id`),
  KEY `start_time` (`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
0
NovaDenizenCommented:
If I remember correctly, InnoDB tables with auto_increment columns reset their auto_increment value to max(column)+1 (or 1 if the table is empty) whenever the server is restarted.  You can get around this by writing an initialization script, and putting the script filename in an "init_file=myinitfile.sql" section of your my.ini (or using --init_file=myfile.sql)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BuffonAuthor Commented:
can u explain more how can I get around this, I didnt understand this script thing. thanks.
0
NovaDenizenCommented:
What do you want the auto_increment value to be when you restart your server?
0
BuffonAuthor Commented:
the one it should be, the last id. the table is empty most of the time by the way.
0
NovaDenizenCommented:
I think the easiest way to fix it would be to just change your table to MyISAM.

ALTER TABLE mytable ENGINE=MyISAM;

MyISAM works how you expect.  It sounds like you don't keep many records in this table, so the change should have very little impact.

Another way would be to leave a dummy record in the table with the most recently used auto_increment value in it, so that default InnoDB initialization will set up the auto_increment correctly.  Instead of deleting normal records from this table, update them so that they become dummy records.  Every once in a while, delete all dummy records but the most recent one.  
0
BuffonAuthor Commented:
I need it to be InnoDB because of transactions, can it be done without dummy record?
0
NovaDenizenCommented:
I don't think you can use the auto_increment feature then.  You can manually do something equivalent by creating a table and using it in a transaction-safe manner to generate auto_increment style values.

create table sequences (name varchar(32) primary key, nextvalue bigint) engine=InnoDB;

to create a sequence:
insert into sequences (name, nextvalue) ('tbl_battles_serial', 100001);

Then, to safely get a serial number and increment the sequence:

START TRANSACTION;
SELECT @ser=nextvalue FROM sequences WHERE name = 'tbl_battles_serial' FOR UPDATE;
-- use the nextvalue returned from SELECT as your serial number
UPDATE sequences SET nextvalue = nextvalue+1 WHERE name = 'xyzserial';
INSERT INTO tbl_battles (id, ...other stuff...) VALUES (@ser, ...other stuff...);
COMMIT;

It's annoying, but it works.
0
NovaDenizenCommented:
That should have been "SELECT @ser:=nextvalue" with the colon.
0
NovaDenizenCommented:
And that 'xyzserial' in the UPDATE statement should have been 'tbl_battles_serial'
0
BuffonAuthor Commented:
I see, I'll wait for a couple of days for may be someone else will come up with better solution, if not I'll give you the points.
0
NovaDenizenCommented:
Here's an alternate way that uses init scripts to set up the auto_increment at every restart.

First, set up a sequences table as above:
create table sequences (name varchar(32) primary key, nextvalue bigint) engine=InnoDB;
insert into sequences (name, nextvalue) ('tbl_battles_serial', 100001);

Then, in the transaction that inserts into tbl_battles, add these lines to the end:
SELECT @maxid:=MAX(battle_id) FROM tbl_battles_serial;
UPDATE sequences SET nextvalue = GREATEST(nextvalue, @maxid+1) WHERE name = 'tbl_battles_serial';

Then, in an init script (say, "myinit.sql") do this:
USE yourdatabase;
SELECT @nextid := nextvalue FROM sequences WHERE name = 'tbl_battles_serial';
ALTER TABLE tbl_battles SET AUTO_INCREMENT=@nextid;

And in the [mysqld] section of your my.ini (or my.cnf) file, add this:
init_file="myinit.sql"
0
todd_farmerCommented:
I sure would be interested to see some documentation around the auto_increment behavior of InnoDB that is discussed here.  Can anybody point me towards references that discuss this?  I use InnoDB, and have not experienced the problems that are discussed here.
0
Austin_HastingsCommented:
Todd,

Do you clean out your tables on a regular basis?

The problem is that Inno tables store the max autoincrement value in memory and on disk. But deleting the records from disk leaves just the in-memory version. And stopping the server loses the memory version.

There's really not much hope for the OP, since he's unwilling to switch table types, and also unwilling to work around the problem.
0
NovaDenizenCommented:
The mysql website folks have cleverly tweaked their search box so that it hides this page, even if you search on "innodb auto_increment".

http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html

0
butterhookCommented:
I made a solution to this today:

Created a database 'idproof'.

Table: DummyQueue (InnoDB)
Table: Config (rows: ConfigId, LastInsertedId)
Made a trigger on DummyQueue:

BEGIN
	/*
		When a new DummyQueue item is inserted:
		Log it's ID value into the Config table.
	*/
	UPDATE Config SET LastInsertedId = new.DummyQueueId WHERE ConfigId = 1;

END

Open in new window


Then amended my.cnf on the test server to include a reference to a startup .sql script:

init_file=/etc/my.startup.sql

Open in new window


Then made a stored procedure called BuildIDFix as follows:

BEGIN
	SELECT (LastInsertedId+1) INTO @NewIncrementValue FROM Config;
	SET @StatementValues = CONCAT ('ALTER TABLE DummyQueue AUTO_INCREMENT = ', @NewIncrementValue);
	
	PREPARE stmt FROM @StatementValues;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;

END

Open in new window


Then called this procedure from inside the my.startup.sql:
USE idproof;
CALL BuildIdFix();

Open in new window


And tested it in the following circumstances:

Without the startup script running : When table was emptied and mysqld restarted: ID returned to 1 : expected behaviour
With the startup script running : When table was emptied and mysqld restarted: ID for a new record was incremented from the last highest value - as required.

n.b. this is an initial proof of concept, hasn't been tested on huge tables, and has been designed to work on a table that's flushed regularly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.