AUTO_INCREMENT in MariaDB is kept in memory only
We're currently using MariaDB 10.1 for development. When initializing the database we run a migration script which sets the AUTO_INCREMENT value for a specific table:
ALTER TABLE table_name AUTO_INCREMENT=1000000;
Motivation
We run MariaDB in a Docker image for development purposes, i.e. we can destroy and rebuild the database at any time. However this specific table needs to be initialized with that starting value for the AUTO_INCREMENT row. So we copied the script to /docker-entrypoint-initdb.d upon building the Docker image.
However this does not have any affect until an INSERT would follow.
From the MariaDB knowledge base:
Until MariaDB 10.2.3, InnoDB and XtraDB used an auto-increment counter
that is stored in memory. When the server restarts, the counter is
re-initialized, which cancels the effects of any AUTO_INCREMENT = N
option in the table statements.
https://mariadb.com/kb/en/library/auto_increment-handling-in-xtradbinnodb/
Hwoever I could not find out how to handle this. Which statements or configurations do I have to make in order to be the above SQL statement effective (until we're ready to upgrade to MariaDB 10.2.4 or higher)?
mariadb
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
add a comment |
We're currently using MariaDB 10.1 for development. When initializing the database we run a migration script which sets the AUTO_INCREMENT value for a specific table:
ALTER TABLE table_name AUTO_INCREMENT=1000000;
Motivation
We run MariaDB in a Docker image for development purposes, i.e. we can destroy and rebuild the database at any time. However this specific table needs to be initialized with that starting value for the AUTO_INCREMENT row. So we copied the script to /docker-entrypoint-initdb.d upon building the Docker image.
However this does not have any affect until an INSERT would follow.
From the MariaDB knowledge base:
Until MariaDB 10.2.3, InnoDB and XtraDB used an auto-increment counter
that is stored in memory. When the server restarts, the counter is
re-initialized, which cancels the effects of any AUTO_INCREMENT = N
option in the table statements.
https://mariadb.com/kb/en/library/auto_increment-handling-in-xtradbinnodb/
Hwoever I could not find out how to handle this. Which statements or configurations do I have to make in order to be the above SQL statement effective (until we're ready to upgrade to MariaDB 10.2.4 or higher)?
mariadb
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
add a comment |
We're currently using MariaDB 10.1 for development. When initializing the database we run a migration script which sets the AUTO_INCREMENT value for a specific table:
ALTER TABLE table_name AUTO_INCREMENT=1000000;
Motivation
We run MariaDB in a Docker image for development purposes, i.e. we can destroy and rebuild the database at any time. However this specific table needs to be initialized with that starting value for the AUTO_INCREMENT row. So we copied the script to /docker-entrypoint-initdb.d upon building the Docker image.
However this does not have any affect until an INSERT would follow.
From the MariaDB knowledge base:
Until MariaDB 10.2.3, InnoDB and XtraDB used an auto-increment counter
that is stored in memory. When the server restarts, the counter is
re-initialized, which cancels the effects of any AUTO_INCREMENT = N
option in the table statements.
https://mariadb.com/kb/en/library/auto_increment-handling-in-xtradbinnodb/
Hwoever I could not find out how to handle this. Which statements or configurations do I have to make in order to be the above SQL statement effective (until we're ready to upgrade to MariaDB 10.2.4 or higher)?
mariadb
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
We're currently using MariaDB 10.1 for development. When initializing the database we run a migration script which sets the AUTO_INCREMENT value for a specific table:
ALTER TABLE table_name AUTO_INCREMENT=1000000;
Motivation
We run MariaDB in a Docker image for development purposes, i.e. we can destroy and rebuild the database at any time. However this specific table needs to be initialized with that starting value for the AUTO_INCREMENT row. So we copied the script to /docker-entrypoint-initdb.d upon building the Docker image.
However this does not have any affect until an INSERT would follow.
From the MariaDB knowledge base:
Until MariaDB 10.2.3, InnoDB and XtraDB used an auto-increment counter
that is stored in memory. When the server restarts, the counter is
re-initialized, which cancels the effects of any AUTO_INCREMENT = N
option in the table statements.
https://mariadb.com/kb/en/library/auto_increment-handling-in-xtradbinnodb/
Hwoever I could not find out how to handle this. Which statements or configurations do I have to make in order to be the above SQL statement effective (until we're ready to upgrade to MariaDB 10.2.4 or higher)?
mariadb
mariadb
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
edited Nov 22 '18 at 7:51
Robert Strauch
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
asked Nov 21 '18 at 16:56
Robert StrauchRobert Strauch
3,7481157103
3,7481157103
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The only use for that ALTER is to immediately INSERT a row and have it be given the value 1000000. Once that row is inserted, the clause has no further effect -- unless you delete all the rows and need to restart at that value.
An alternative to it is to explicitly provide the value for the first INSERT.
Since I have yet to find a 'valid' reason for ever using the clause, please explain what you are trying to do. Maybe we can think of a usable workaround.
In the future, I think there will be a change wherein the AI value is persisted in the index. This is already a feature in MySQL 8.0.
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53417052%2fauto-increment-in-mariadb-is-kept-in-memory-only%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
The only use for that ALTER is to immediately INSERT a row and have it be given the value 1000000. Once that row is inserted, the clause has no further effect -- unless you delete all the rows and need to restart at that value.
An alternative to it is to explicitly provide the value for the first INSERT.
Since I have yet to find a 'valid' reason for ever using the clause, please explain what you are trying to do. Maybe we can think of a usable workaround.
In the future, I think there will be a change wherein the AI value is persisted in the index. This is already a feature in MySQL 8.0.
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
add a comment |
The only use for that ALTER is to immediately INSERT a row and have it be given the value 1000000. Once that row is inserted, the clause has no further effect -- unless you delete all the rows and need to restart at that value.
An alternative to it is to explicitly provide the value for the first INSERT.
Since I have yet to find a 'valid' reason for ever using the clause, please explain what you are trying to do. Maybe we can think of a usable workaround.
In the future, I think there will be a change wherein the AI value is persisted in the index. This is already a feature in MySQL 8.0.
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
add a comment |
The only use for that ALTER is to immediately INSERT a row and have it be given the value 1000000. Once that row is inserted, the clause has no further effect -- unless you delete all the rows and need to restart at that value.
An alternative to it is to explicitly provide the value for the first INSERT.
Since I have yet to find a 'valid' reason for ever using the clause, please explain what you are trying to do. Maybe we can think of a usable workaround.
In the future, I think there will be a change wherein the AI value is persisted in the index. This is already a feature in MySQL 8.0.
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
The only use for that ALTER is to immediately INSERT a row and have it be given the value 1000000. Once that row is inserted, the clause has no further effect -- unless you delete all the rows and need to restart at that value.
An alternative to it is to explicitly provide the value for the first INSERT.
Since I have yet to find a 'valid' reason for ever using the clause, please explain what you are trying to do. Maybe we can think of a usable workaround.
In the future, I think there will be a change wherein the AI value is persisted in the index. This is already a feature in MySQL 8.0.
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
answered Nov 21 '18 at 22:37
Rick JamesRick James
68.3k559100
68.3k559100
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
add a comment |
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
Thanks, Rick. I added the motivation for this statement to the original question.
– Robert Strauch
Nov 22 '18 at 7:52
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53417052%2fauto-increment-in-mariadb-is-kept-in-memory-only%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
