Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1764
  • Last Modified:

Auto increment

when I restart mysql server, in some tables the auto increment fields are reseted back to 1. how can I prevent it?
0
Buffon
Asked:
Buffon
  • 8
  • 7
  • 4
  • +2
1 Solution
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now