Mysql auto_increment reset WITHOUT rebuilding index?Best way to index this very large tableHow does rebuilding partitioned table index affect table access?How to create a table in MySQL with automatic numeration of rows?Mysql: Working With 192 Trillion Records… (Yes, 192 Trillion)Index rebuild stored procedure fails while calling temporary tablemysql INNODB inserts very slowCan I backup a mysql index? On an innodb table?MySQL Database Problems - Growing too quick, PerformanceMySQL Table not repairingDropping Index on 10GB+ InnoDB table takes over 4 hours
Does the growth of home value benefit from compound interest?
Is it legal in the UK for politicians to lie to the public for political gain?
Credit card offering 0.5 miles for every cent rounded up. Too good to be true?
Old black and white movie: glowing black rocks slowly turn you into stone upon touch
What are the words for people who cause trouble believing they know better?
PhD student with mental health issues and bad performance
Working in the USA for living expenses only; allowed on VWP?
Humans meet a distant alien species. How do they standardize? - Units of Measure
You've spoiled/damaged the card
Initialize an std::array algorithmically at compile time
The ring of global sections of a regular scheme
Comma Code - Ch. 4 Automate the Boring Stuff
How to split a string in two substrings of same length using bash?
Does Peach's float negate shorthop knockback multipliers?
Avoiding cliches when writing gods
How bad would a partial hash leak be, realistically?
In this example, which path would a monster affected by the Dissonant Whispers spell take?
Count down from 0 to 5 seconds and repeat
What is the purpose of building foundations?
What do we gain with higher order logics?
Short story written from alien perspective with this line: "It's too bright to look at, so they don't"
Is it OK to bring delicacies from hometown as tokens of gratitude for an out-of-town interview?
Accidentally renamed tar.gz file to a non tar.gz file, will my file be messed up
What are the words for people who cause trouble believing they know better?
Mysql auto_increment reset WITHOUT rebuilding index?
Best way to index this very large tableHow does rebuilding partitioned table index affect table access?How to create a table in MySQL with automatic numeration of rows?Mysql: Working With 192 Trillion Records… (Yes, 192 Trillion)Index rebuild stored procedure fails while calling temporary tablemysql INNODB inserts very slowCan I backup a mysql index? On an innodb table?MySQL Database Problems - Growing too quick, PerformanceMySQL Table not repairingDropping Index on 10GB+ InnoDB table takes over 4 hours
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it near 4.2 billion. From past experience I know that trying to set the auto_increment value back to what it should be will force mysql to rebuild the entire index, which would probably take 24 hours on a table this size. To be honest, I can't believe that this is just "how" it works by default. There's absolutely no need to rebuild an entire table index just beacuse you want to change this value.
There has to be a way. But I can't find one anywhere. IDEAS PLEASE!
(Rebuilding indexes, I know that myisamchk can do it 100x faster than the mysql process itself can. But I can't tell mysql to use myisamchk instead of itself to rebuild the index after changing the auto increment ID. There has to be a way!!!)
mysql database indexing
edited May 18 at 22:30
window.document
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
add a comment |
There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it near 4.2 billion. From past experience I know that trying to set the auto_increment value back to what it should be will force mysql to rebuild the entire index, which would probably take 24 hours on a table this size. To be honest, I can't believe that this is just "how" it works by default. There's absolutely no need to rebuild an entire table index just beacuse you want to change this value.
There has to be a way. But I can't find one anywhere. IDEAS PLEASE!
(Rebuilding indexes, I know that myisamchk can do it 100x faster than the mysql process itself can. But I can't tell mysql to use myisamchk instead of itself to rebuild the index after changing the auto increment ID. There has to be a way!!!)
mysql database indexing
edited May 18 at 22:30
window.document
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34
add a comment |
There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it near 4.2 billion. From past experience I know that trying to set the auto_increment value back to what it should be will force mysql to rebuild the entire index, which would probably take 24 hours on a table this size. To be honest, I can't believe that this is just "how" it works by default. There's absolutely no need to rebuild an entire table index just beacuse you want to change this value.
There has to be a way. But I can't find one anywhere. IDEAS PLEASE!
(Rebuilding indexes, I know that myisamchk can do it 100x faster than the mysql process itself can. But I can't tell mysql to use myisamchk instead of itself to rebuild the index after changing the auto increment ID. There has to be a way!!!)
mysql database indexing
edited May 18 at 22:30
window.document
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it near 4.2 billion. From past experience I know that trying to set the auto_increment value back to what it should be will force mysql to rebuild the entire index, which would probably take 24 hours on a table this size. To be honest, I can't believe that this is just "how" it works by default. There's absolutely no need to rebuild an entire table index just beacuse you want to change this value.
There has to be a way. But I can't find one anywhere. IDEAS PLEASE!
(Rebuilding indexes, I know that myisamchk can do it 100x faster than the mysql process itself can. But I can't tell mysql to use myisamchk instead of itself to rebuild the index after changing the auto increment ID. There has to be a way!!!)
mysql database indexing
mysql database indexing
edited May 18 at 22:30
window.document
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
edited May 18 at 22:30
window.document
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
edited May 18 at 22:30
window.document
1436
edited May 18 at 22:30
window.document
1436
edited May 18 at 22:30
window.document
1436
1436
asked Mar 4 '10 at 13:20
SeanSean
219238
asked Mar 4 '10 at 13:20
SeanSean
219238
asked Mar 4 '10 at 13:20
SeanSean
219238
219238
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34
add a comment |
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34
add a comment |
1 Answer
1
active
oldest
votes
Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f119157%2fmysql-auto-increment-reset-without-rebuilding-index%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
add a comment |
Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
add a comment |
Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
Based on this article, it looks like you could reset the accidental ID to a proper value, or drop the row and then restart the server as the auto-increment value for innodb tables is held in memory. I haven't tried this before but it may work.
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
answered Apr 4 '11 at 18:31
sreimersreimer
1,885917
1,885917
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
add a comment |
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
Thanks, but I'm using MyISAM. Still don't know of a solution. I ended up doing the "copy to a new table a million rows at a time, ignoring the rows with bogus IDs". Same method I use when I need to transform a gigantic table, e.g. adding a new field to a table with 2B rows. That's obviously not something I need to do often but when I do, it's the only thing I know of that works in a reasonable amount of time.
– Sean
Apr 5 '11 at 7:03
1
1
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
This guy did it with myisam by editing the myi file. Not elegant, but I guess the only possible way stackoverflow.com/questions/2686032/…
– sreimer
Apr 5 '11 at 13:55
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f119157%2fmysql-auto-increment-reset-without-rebuilding-index%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I presume with rebuilding indexes you refer to the obvious command ALTER TABLE t2 AUTO_INCREMENT = value ?
– Antti Rytsölä Circles Consult
Nov 9 '11 at 9:34