Galera multi-nodes Master-Master issue with READ right after INSERT
I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.
- Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
- Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.
It's worth noticing that:
- Both requests were made asynchronously.
- The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)
What am I dealing with here, guys?
replication galera master-master-replication
edited 7 mins ago
shgnInc
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
add a comment |
I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.
- Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
- Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.
It's worth noticing that:
- Both requests were made asynchronously.
- The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)
What am I dealing with here, guys?
replication galera master-master-replication
edited 7 mins ago
shgnInc
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
add a comment |
I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.
- Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
- Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.
It's worth noticing that:
- Both requests were made asynchronously.
- The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)
What am I dealing with here, guys?
replication galera master-master-replication
edited 7 mins ago
shgnInc
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
I have a multi-nodes Galera setup which is master-master. An API call to a web server A could write data to either database 1, 2, 3 or 4 (we are not using HaProxy). Another API call right after that could hit web server A again. However, the second API call couldn't find the record created in the previous request.
- Request 1: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Writes a record into a table with PDO transaction
- Request 2: Hits either WS A or B. It could connect to either DB 1,2,3, or 4. Tried to get the record created in Request 1 but it's not available.
It's worth noticing that:
- Both requests were made asynchronously.
- The record eventually showed up in all nodes (manually checked, so, not sure if it wasn't there when the second request was made)
What am I dealing with here, guys?
replication galera master-master-replication
replication galera master-master-replication
edited 7 mins ago
shgnInc
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
edited 7 mins ago
shgnInc
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
edited 7 mins ago
shgnInc
19011
edited 7 mins ago
shgnInc
19011
edited 7 mins ago
shgnInc
19011
19011
asked Jul 31 '17 at 19:52
ln9187ln9187
82
asked Jul 31 '17 at 19:52
ln9187ln9187
82
asked Jul 31 '17 at 19:52
ln9187ln9187
82
82
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."
Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/
You will need to set the "wsrep_sync_wait" session variable.
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client usingSET GLOBAL wsrep_sync_wait=1;You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line:wsrep_sync_wait=1
– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
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%2f182292%2fgalera-multi-nodes-master-master-issue-with-read-right-after-insert%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
This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."
Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/
You will need to set the "wsrep_sync_wait" session variable.
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client usingSET GLOBAL wsrep_sync_wait=1;You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line:wsrep_sync_wait=1
– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
add a comment |
This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."
Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/
You will need to set the "wsrep_sync_wait" session variable.
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client usingSET GLOBAL wsrep_sync_wait=1;You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line:wsrep_sync_wait=1
– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
add a comment |
This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."
Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/
You will need to set the "wsrep_sync_wait" session variable.
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
This answer provided by @Bill Karwin explains how this is one of the drawbacks of Galera Cluster.
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
"...Therefore there's still a small chance of reading stale data from another cluster node. There's an option you can set to force SELECT to wait until the queue of worksets has updated the database, but this means you have delays on SELECT..."
Refer to this article on how to force the SELECT to wait for the most up to date data. http://galeracluster.com/2015/06/achieving-read-after-write-semantics-with-galera/
You will need to set the "wsrep_sync_wait" session variable.
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
answered Jul 31 '17 at 20:40
Chad CahillChad Cahill
1145
1145
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client usingSET GLOBAL wsrep_sync_wait=1;You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line:wsrep_sync_wait=1
– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
add a comment |
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client usingSET GLOBAL wsrep_sync_wait=1;You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line:wsrep_sync_wait=1
– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
Hi @chad-cahill, do we need to restart the galera for the new setting taking effect?
– ln9187
Aug 1 '17 at 15:08
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client using
SET GLOBAL wsrep_sync_wait=1; You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line: wsrep_sync_wait=1– Chad Cahill
Aug 1 '17 at 21:43
@ln9187 wsrep_sync_wait is a dynamic variable, so it can be set without a restart. In your case you may want to set this variable = 1. You can set it dynamically from the mysql client using
SET GLOBAL wsrep_sync_wait=1; You should also specify it in your my.cnf file so that the change persists even after a restart. In my.cnf add the following line: wsrep_sync_wait=1– Chad Cahill
Aug 1 '17 at 21:43
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
FYI - Galera Cluster System Variables and a complete list of system variables with instructions on setting them.
– Chad Cahill
Aug 1 '17 at 21:46
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%2f182292%2fgalera-multi-nodes-master-master-issue-with-read-right-after-insert%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
