0

I have a MySQL 5.5 (5.5.45-1+deb.sury.org~trusty+1-log) in Ubuntu and I am hitting a strange behaivior when executing a migration query.

The query is like this:

insert into db1.table1 (col2)
select col2
from db2.table1 as a
inner join db1.table2 as d on a.id = d.uuid
inner join db1.table3 as c on a.id = c.uuid
where c.id = 2;

After that query I get data like this:

1 xxx
2 xxx
3 xxx

After that I execute single inserts like this:

insert into db1.table1 (col2) values('xxx');
insert into db1.table1 (col2) values('xxx');
insert into db1.table1 (col2) values('xxx');

And I get a jump in the autoincrement:

1 xxx
2 xxx
3 xxx
34 xxx
35 xxx
36 xxx

That jump is always deterministic after executing the first query.

Does anyone knows why is this happening?

How can I ensure that after the migration of data the next registries get the correct Id?

Thanks!

I found that is a known issue of mysql 5. Mysql auto increment jumps when insert-select

So I think this is a bug.

Community
  • 1
  • 1
rsan
  • 1,887
  • 2
  • 17
  • 23
  • Usually this means that the auto_increment has been called and the records have either been deleted or never committed. As far as for the migration of data: Turn of the auto_increment columns until the import of data is finished and then turn on the auto_increment columns again. – Norbert Dec 30 '15 at 00:14
  • I've reproduced your dbs and tables minimal schema and tried the same selects and inserts, and everything behaves as expected, I'm not getting the jump in the auto increments. – palako Dec 30 '15 at 00:19
  • Do you want the table1 to have same values with db2? Or start over from 1. If same values, you can add related column to your insert, I remember MySql does not need auto increments to be turned off to insert value into it. – Ramazan Binarbasi Dec 30 '15 at 01:04
  • I dont need to turn off autoincrements because I dont have the autoincrement column in db2, what I am expecting is that the database generate the numbers in that column (and that is working just fine). The problem is after executing the migration I expect that if the bulck insert inserted 10 rows the autoincrement index stays in 11. But for some reason after the bulk insert it ends in 32. – rsan Dec 30 '15 at 18:08
  • Possible duplicate of [Mysql auto increment jumps when insert-select](http://stackoverflow.com/questions/12226105/mysql-auto-increment-jumps-when-insert-select) – rsan Jan 04 '16 at 21:29

1 Answers1

0

For first question: auto increment primary leaving gaps in counting

For second:

  • If you want db1's autoincrement field to match db2, include it in insert (assuming fields name is id). As I remember, MySql allows inserts like this (unlike MS SQL which needs set identity_insert on command before insert)

    insert into db1.table1 (id, col2) select id, col2 from db2.table1 as a inner join db1.table2 as d on a.id = d.uuid inner join db1.table3 as c on a.id = c.uuid where c.id = 2;

  • If not, either you can write your own increment like:

    insert into db1.table1 (id, col2) select (select max(id) FROM db1.table1) + 1, col2 from db2.table1 as a inner join db1.table2 as d on a.id = d.uuid inner join db1.table3 as c on a.id = c.uuid where c.id = 2;

  • Or check answers on the link I posted..

Community
  • 1
  • 1
Ramazan Binarbasi
  • 777
  • 6
  • 14
  • Hi, regarding the other question regarding gaps created by concurrency. I think thats not the problem here. I'm running single user, and every query is being executed sequentially. About "if you want db1's autoincrement field to match db2", i'm not expecting it. db2 does not have the autoincrement column. – rsan Dec 30 '15 at 18:14