MySQL: auto_increment's strange behaviour

Question

I need to load data from TSV files into a table which has 7 foreign keys. I found a suggestion online that it could be done through a proxy table that has columns matching the input files and a trigger on insert event that populates the data to the destination table and its parents with FK constraint. I started testing it and I noticed that the parent tables have their PK IDs incremented by more than 1, I mean instead of the expected sequence of 1, 2, 3, ... I'm getting something like 1, 63, 539, ... . Have a look at my trigger code:

create trigger trig1 before insert on temp_table
for each row
begin
  -- Set FKs
  declare ts_id BIGINT UNSIGNED;
  declare cluster_id BIGINT UNSIGNED;
  declare queue_id BIGINT UNSIGNED;
  declare service_class_id BIGINT UNSIGNED;
  declare project_id BIGINT UNSIGNED;
  declare job_group_id BIGINT UNSIGNED;
  declare user_id BIGINT UNSIGNED;
  insert into timeline (ts) values (NEW.ts) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into ts_id;
  insert into clusters (name) values (NEW.cluster_name) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into cluster_id;  
  insert into queues (name) values (NEW.queue_name) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into queue_id;
  insert into service_classes (name) values (NEW.service_class) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into service_class_id;
  insert into projects (code) values (NEW.project_code) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into project_id;
  insert into job_groups (name) values (NEW.job_group) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into job_group_id;
  insert into users (name) values (NEW.user_name) on duplicate key update id=last_insert_id(id);
  select last_insert_id() into user_id;
  -- Insert a new row to the dest table
  insert into dest_table values(ts_id, cluster_id, queue_id, service_class_id, project_id, job_group_id, user_id, NEW.job_count, NEW.slot_count, NEW.mem_req, NEW.mem_used);
end; 

It seems to me that in the statements 'insert into ... on duplicate key update id=last_insert_id(id);' whenever the duplicate insert occurs the 'update' affect the last insert id even though there is no change to the parent table. Please let me know what you think - is my guess about this auto_increment behaviour correct and how to prevent such issue. In production this issue may cause the PKs in parent tables to reach to max value quicker that expected, so I would like to avoid that.

Answer 1

These lost values are a known documented limitation.

See the following documentation: http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

I would look to a previous discussion in this issue that suggests a couple of alternative methods that avoid this issue: prevent autoincrement on MYSQL duplicate insert

Answer 2

In MySQL doc http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html pointed to me by @TrentLloyd I found that this behaviour depends on the value of the system variable innodb_autoinc_lock_mode:

• when innodb_autoinc_lock_mode=0 on-duplicate-key-updates do not increase the primary key;
• when innodb_autoinc_lock_mode=(1|2) on-duplicate-key-updates do increase the primary key.

So innodb_autoinc_lock_mode=0 does what I was looking for. Unfortunately for me this parameter's default value is 1 and it cannot be changed in the session, but only in my.cnf or as a start-up command param, so it requires reconf and restart of MySQL service.

The workaround I came up with was to drop auto_increment from IDs on all parent tables and rewrite the trigger:

create trigger jrun_trig before insert on jrun_tmp
for each row
begin
  -- Set FKs
  declare ts_id INT UNSIGNED;
  declare cluster_id INT UNSIGNED;
  declare queue_id INT UNSIGNED;
  declare service_class_id INT UNSIGNED;
  declare project_id INT UNSIGNED;
  declare job_group_id INT UNSIGNED;
  declare user_id INT UNSIGNED;
  if NEW.ts is NULL then set ts_id = NULL;
  else
      select id into ts_id from timeline where ts = NEW.ts limit 1;
      if ts_id is NULL then 
        select ifnull(max(id), 0) + 1 into ts_id from timeline;
        insert into timeline values (ts_id, NEW.ts);
      end if;
  end if;
  if NEW.cluster_name is NULL then set cluster_id = NULL;
  else
      select id into cluster_id from clusters where name = NEW.cluster_name limit 1;
      if cluster_id is NULL then 
        select ifnull(max(id), 0) + 1 into cluster_id from clusters;
        insert into clusters values (cluster_id, NEW.cluster_name);
      end if;
  end if;
  if NEW.queue_name is NULL then set queue_id = NULL;
  else
      select id into queue_id from queues where name = NEW.queue_name limit 1;
      if queue_id is NULL then 
        select ifnull(max(id), 0) + 1 into queue_id from queues;
        insert into queues values (queue_id, NEW.queue_name); 
      end if;
  end if;
  if NEW.service_class is NULL then set service_class_id = NULL;
  else
      select id into service_class_id from service_classes where name = NEW.service_class limit 1;
      if service_class_id is NULL then 
        select ifnull(max(id), 0) + 1 into service_class_id from service_classes;
        insert into service_classes values (service_class_id, NEW.service_class); 
      end if;
  end if;
  if NEW.project_code is NULL then set project_id = NULL;
  else
      select id into project_id from projects where code = NEW.project_code limit 1;
      if project_id is NULL then 
        select ifnull(max(id), 0) + 1 into project_id from projects;
        insert into projects (id, code) values (project_id, NEW.project_code); 
      end if;
  end if;
  if NEW.job_group is NULL then set job_group_id = NULL;
  else
      select id into job_group_id from job_groups where name = NEW.job_group limit 1;
      if job_group_id is NULL then 
        select ifnull(max(id), 0) + 1 into job_group_id from job_groups;
        insert into job_groups values (job_group_id, NEW.job_group); 
      end if;
  end if;
  if NEW.user_name is NULL then set user_id = NULL;
  else
      select id into user_id from users where name = NEW.user_name limit 1;
      if user_id is NULL then 
        select ifnull(max(id), 0) + 1 into user_id from users;
        insert into users values (user_id, NEW.user_name); 
      end if;
  end if;
  -- Insert a new row to the dest table
  insert ignore into jrun values(ts_id, cluster_id, queue_id, service_class_id, project_id, job_group_id, user_id, NEW.job_count, NEW.slot_count, NEW.mem_req, NEW.mem_used);
end; 

The trigger has way more lines now, but it fully solves my problem of gaps in PK columns.