MySQL sudden loss of data
It is second time that records are lost in MySQL specific table.
I am having Master-Slave MySQL replication.
For the 1st time, one of my tables partially lost records on 6 May.
The weird part is, the Master instance records loss is not replicated to the Slave. It couldn't be SQL injection or script issue. I can't really remember much details as it is 2 months ago.
And recently (2nd time), the data loss happens again in another table. The data lost for records after 2015-06-05, and about 60% of the records gone. This time, the Slave has the data loss too.
I checked the Slave mysqlbinlog file, there is no DELETE FROM table command executed during that period.
Both tables are InnoDB, running MySQL server in Ubuntu instance.
I realised the data loss issue only after multiple users reporting the software behaving incorrectly.
May I know what happen to MySQL? This is frightening if the data can be lost out of nowhere. I managed to recover the data with daily backup, but this is definitely not a good option.
mysql
edited Aug 1 '17 at 8:15
joanolo
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
add a comment |
It is second time that records are lost in MySQL specific table.
I am having Master-Slave MySQL replication.
For the 1st time, one of my tables partially lost records on 6 May.
The weird part is, the Master instance records loss is not replicated to the Slave. It couldn't be SQL injection or script issue. I can't really remember much details as it is 2 months ago.
And recently (2nd time), the data loss happens again in another table. The data lost for records after 2015-06-05, and about 60% of the records gone. This time, the Slave has the data loss too.
I checked the Slave mysqlbinlog file, there is no DELETE FROM table command executed during that period.
Both tables are InnoDB, running MySQL server in Ubuntu instance.
I realised the data loss issue only after multiple users reporting the software behaving incorrectly.
May I know what happen to MySQL? This is frightening if the data can be lost out of nowhere. I managed to recover the data with daily backup, but this is definitely not a good option.
mysql
edited Aug 1 '17 at 8:15
joanolo
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
1
Is it possible that you have some table with aREFERENCEScontraint withON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's anON DELETE CASCADE.
– joanolo
Aug 1 '17 at 8:17
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I'dCHECKtables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.
– joanolo
Aug 1 '17 at 13:26
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07
add a comment |
It is second time that records are lost in MySQL specific table.
I am having Master-Slave MySQL replication.
For the 1st time, one of my tables partially lost records on 6 May.
The weird part is, the Master instance records loss is not replicated to the Slave. It couldn't be SQL injection or script issue. I can't really remember much details as it is 2 months ago.
And recently (2nd time), the data loss happens again in another table. The data lost for records after 2015-06-05, and about 60% of the records gone. This time, the Slave has the data loss too.
I checked the Slave mysqlbinlog file, there is no DELETE FROM table command executed during that period.
Both tables are InnoDB, running MySQL server in Ubuntu instance.
I realised the data loss issue only after multiple users reporting the software behaving incorrectly.
May I know what happen to MySQL? This is frightening if the data can be lost out of nowhere. I managed to recover the data with daily backup, but this is definitely not a good option.
mysql
edited Aug 1 '17 at 8:15
joanolo
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
It is second time that records are lost in MySQL specific table.
I am having Master-Slave MySQL replication.
For the 1st time, one of my tables partially lost records on 6 May.
The weird part is, the Master instance records loss is not replicated to the Slave. It couldn't be SQL injection or script issue. I can't really remember much details as it is 2 months ago.
And recently (2nd time), the data loss happens again in another table. The data lost for records after 2015-06-05, and about 60% of the records gone. This time, the Slave has the data loss too.
I checked the Slave mysqlbinlog file, there is no DELETE FROM table command executed during that period.
Both tables are InnoDB, running MySQL server in Ubuntu instance.
I realised the data loss issue only after multiple users reporting the software behaving incorrectly.
May I know what happen to MySQL? This is frightening if the data can be lost out of nowhere. I managed to recover the data with daily backup, but this is definitely not a good option.
mysql
mysql
edited Aug 1 '17 at 8:15
joanolo
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
edited Aug 1 '17 at 8:15
joanolo
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
edited Aug 1 '17 at 8:15
joanolo
9,68842152
edited Aug 1 '17 at 8:15
joanolo
9,68842152
edited Aug 1 '17 at 8:15
joanolo
9,68842152
9,68842152
asked Aug 1 '17 at 7:52
neobieneobie
10614
asked Aug 1 '17 at 7:52
neobieneobie
10614
asked Aug 1 '17 at 7:52
neobieneobie
10614
10614
1
Is it possible that you have some table with aREFERENCEScontraint withON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's anON DELETE CASCADE.
– joanolo
Aug 1 '17 at 8:17
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I'dCHECKtables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.
– joanolo
Aug 1 '17 at 13:26
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07
add a comment |
1
Is it possible that you have some table with aREFERENCEScontraint withON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's anON DELETE CASCADE.
– joanolo
Aug 1 '17 at 8:17
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I'dCHECKtables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.
– joanolo
Aug 1 '17 at 13:26
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07
1
1
Is it possible that you have some table with a
REFERENCES contraint with ON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's an ON DELETE CASCADE.– joanolo
Aug 1 '17 at 8:17
Is it possible that you have some table with a
REFERENCES contraint with ON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's an ON DELETE CASCADE.– joanolo
Aug 1 '17 at 8:17
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I'd
CHECK tables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.– joanolo
Aug 1 '17 at 13:26
I'd
CHECK tables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.– joanolo
Aug 1 '17 at 13:26
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07
add a comment |
1 Answer
1
active
oldest
votes
Passo pelo mesmo problema sem solução a não ser migrar para outro SGDB.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f182324%2fmysql-sudden-loss-of-data%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
Passo pelo mesmo problema sem solução a não ser migrar para outro SGDB.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
add a comment |
Passo pelo mesmo problema sem solução a não ser migrar para outro SGDB.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
add a comment |
Passo pelo mesmo problema sem solução a não ser migrar para outro SGDB.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Passo pelo mesmo problema sem solução a não ser migrar para outro SGDB.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 mins ago
PatrickPatrick
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 12 mins ago
PatrickPatrick
1
answered 12 mins ago
PatrickPatrick
1
1
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Patrick is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
add a comment |
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
Not really an answer. There are lots of functioning MySQL replication installs so it just requires expertise in debugging. Migrating to a different implementation is just going to get a different set of things to learn.
– danblack
2 mins ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f182324%2fmysql-sudden-loss-of-data%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
1
Is it possible that you have some table with a
REFERENCEScontraint withON DELETE CASCADE? See 13.1.18.6 Using FOREIGN KEY Constraints. Deleting from a parent (referenced) table might have deleted from some children (referencing) tables, if there's anON DELETE CASCADE.– joanolo
Aug 1 '17 at 8:17
I did not assign any Foreign Key.
– neobie
Aug 1 '17 at 13:24
I'd
CHECKtables... this looks like something is corrupted, and this corruption is made visible when some row is inserted or updated.– joanolo
Aug 1 '17 at 13:26
Perhaps add a trigger to record the date-time and username whenever a delete-row action is taken on that table, as a way to debug. While I don't know about the capabilities of MySQL, certainly you could do this in Postgres or other such databases.
– Basil Bourque
Aug 7 '17 at 5:07