MySql autoincrement column increases by 10 problem

Question

I am a user of a some host company which serves my MySql database. Due to their replication problem, the autoincrement values increses by 10, which seems to be a common problem.

My question is how can I simulate (safely) autoincrement feature so that the column have an consecutive ID?

My idea was to implement some sequence mechanism to solve my problem, but I do not know if it is a best option. I had found such a code snipset over the web:

DELIMITER ;;

DROP TABLE IF EXISTS `sequence`;;
CREATE TABLE `sequence` (
  `name` CHAR(16) NOT NULL,
  `value` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;;

DROP FUNCTION IF EXISTS `nextval`;
CREATE FUNCTION `nextval`(thename CHAR(16) CHARSET latin1)
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
  INSERT INTO `sequence` 
    SET `name`=thename, 
        `value`=(@val:=@@auto_increment_offset)+@@auto_increment_increment
   ON DUPLICATE KEY 
    UPDATE `value`=(@val:=`value`)+@@auto_increment_increment;
  RETURN @val;
END ;;

DELIMITER ;

which seems quite all correct. My second question is if this solution is concurrent-safe? Of course INSERT statement is, but what about ON DUPLICATE KEY update?

Thanks!

Answer 1

Why do you need to have it in the first place?

Even with auto_increment_increment == 1 you are not guaranteed, that the autoincrement field in the table will have consecutive values (what if the rows are deleted, hmm?).

With autoincrement you are simply guaranteed by the db engine, that the field will be unique, nothing else, really.

EDIT: I want to reiterate: In my opinion, it is not a good idea to assume things like concurrent values of an autoincrement column, because it is going to bite you later.

EDIT2: Anyway, this can be "solved" by an "on insert" trigger

create trigger "sequence_b_ins" before insert on `sequence`
for each row
begin
    NEW.id = select max(id)+1 from `sequence`;
end

Or something along these lines (sorry, not tested)

Answer 2

Another option would be to use a stored proc to do the insert and have it either select max id from your table or keep another table with the current id being used and update as id's are used.