Summary of sections seen below.
Section 1 mimicks a table that has gaps in id like your question
Section 2 shows a fast way to put a 4 million row table together with incrementing pk.
completely not used for this but perhaps useful.
if it seems left like a half-thought it is because it was not useful entirely
Section 3 creates a table inspired by section 2 to leave you with a table of
it is just a worktable where ordering in important, both to insert into it and processing it
The new id to use (the pk)
Your current id (the one that is gap-prone)
and a column that says whether or not has been processed so you can do them in batches
section 3 is where the action is
xxxxxxxxxxxxxxxxxxxxxxxxxx
Section 1:
create table tbl1
( // this mimicks your current table. naturally you have one already
id bigint not null auto_increment primary key,
thing varchar(100) -- whatever columns you have
)engine=MyISAM;
insert tbl1(thing) values('a'),('a'),('b');
show table status from test; -- auto_increment=4, max is 3
alter table tbl1 auto_increment=2000;
insert tbl1(thing) values('a'),('a'),('b');
alter table tbl1 auto_increment=100000; -- 100k
insert tbl1(thing) values('a'),('a'),('b');
alter table tbl1 auto_increment=110000; -- 110k
insert tbl1(thing) values('a'),('a'),('b');
alter table tbl1 auto_increment=2000000; -- 2m
insert tbl1(thing) values('a'),('a'),('b');
show table status from test; -- auto_increment=2000003, max is 2000002
select count(*) from tbl1 -- 15 rows
xxxxxxxxxxxxxxxxxxxxxxxxxx
Section 2:
create table idFix
( colIs bigint auto_increment primary key, -- Is your Key
colShouldBe bigint null -- Should be your new Key
processedYet tinyint null -- 1 if processed
)engine=myisam;
insert into idFix(colIs) values(null); -- prime it with 1 row
-- this is pretty fast, don't laugh
-- run the following line 22 times
insert into idFix(colIs) select null from idFix;
-- you now have 4.2m rows in tbl2 (4,194,304)
select count(*) from idFix
select count(*) from idFix
where colIs not in (select id from tbl1)
-- 4,194,289
xxxxxxxxxxxxxxxxxxxxxxxxxx
Section 3:
Backup data first. Then perform tests in a scratch database of the following
create table idFix2
( yourIdShouldBe bigint auto_increment primary key, -- This becomes what your new key should be
yourIdIs bigint null, -- This is what your gap-prone id is right now
processedYet tinyint null -- 1 if processed, null otherwise
)engine=myisam;
-- the order by is important
insert into idFix2(yourIdIs,processedYet)
select id,null from tbl1 order by id
-- again order by in above stmt is important
Now you have a table that what your key should be, what your key is, and processedYet is null.
Do them in batches in a stored proc or front end code (say java/c#, whatever)
It is important to do them top to bottom. any other way will screw up your data
Did i mention it is important to do it top to bottom?
I will leave my thought out of it about getting everyone out of system and requiring a table lock
only you know your system not us.
select *
from idFix2
where processedYet is null and yourIdShouldBe<>yourIdIs
order by yourIdShouldBe -- order is important
limit 2 -- you might want to choose a bigger number :>
Did i mention it is important to do it top to bottom ??
Here is the flow using result set from above select stmt
(a) get next row in result set
(b) insert new parent record using data from tbl1 data back into tbl1 using
data from row yourIdIs but the insert will be pk=yourIdShouldBe
The insert will guarantee you won't have foreign key constraints in children tweaked below
(c) update children that use the old yourIdIs to hang under the new yourIdShouldBe
in their tables (there can be scads of these tables). the children's foreign key constraints
will be honored because the new parent row is in place already from step(b)
(d) delete the parent row from tbl1 where pk is yourIdIs. fear not that this will cause even more
gaps because those will be filled based on looping thru (a) which will slot fill them
(e) update idFix2 set processedYet=1 for the row your are processing from step (a) result set
(f) GoTo (a)
When you have no more processedYet=null you are almost done
Set new auto_increment value to what it should be (1 more than max(id) in tbl1, let's call that number nnnn)
alter table tbl1 auto_increment=nnnn;
xxxxxxxxxxxxxxxxxxxxxxxxxx
Note the following
show table status from test where name like 'tbl1%'; -- auto_increment=2000003
I have nothing in slot4, 2000 will become slot 4
insert tbl1(id,thing) values(4,'stuff from record2000 you get the drift');
show table status from test where name like 'tbl1%'; -- auto_increment=2000003 is left as is
So you are free to fill the gaps without screwing with auto_increment until the end
There it is and your gaps go away. If it fails, consider taking a vacation day.
Oh, I forgot, you were testing this first in a scratch database anyway.
Good luck!
