Aurora-mysql cross-region replicas fail with views/procedures/functionsShow Slave Status not working from console, nor clientUpdate RDS db via mysqlbinlog: “you need (at least one of) the SUPER privilege(s)”MySQL binlog format dilemma?Mysql Multi slave replicationHow to implement table security without SUPER privilege?Can't connect MySQL EC2 master to RDS slaveMysql replication, master_log_pos sync but tables not updateAccessing a Google Cloud Replica of an external on-prem masterSlave replication stops with Last_SQL_Errno: 1032Master- Master Replication breaks after server Reboot
Of strange atmospheres - the survivable but unbreathable
Security vulnerabilities of POST over SSL
What is the intuition behind the term Transitive for verbs?
The disk image is 497GB smaller than the target device
Storing voxels for a voxel Engine in C++
How to politely tell someone they did not hit reply all in email?
Why A=2 and B=1 in the call signs for Spirit and Opportunity?
How to respond to an e-mail asking me to suggest a doctoral research topic?
Finding all files with a given extension whose base name is the name of the parent directory
Burned out due to current job, Can I take a week of vacation between jobs?
How did NASA Langley end up with the first 737?
USPS Back Room - Trespassing?
Do copyright notices need to be placed at the beginning of a file?
Where is Jon going?
Time complexity of an algorithm: Is it important to state the base of the logarithm?
Can you still travel to America on the ESTA waiver program if you have been to Iran in transit?
What would prevent living skin from being a good conductor for magic?
Python program for fibonacci sequence using a recursive function
Why did it take so long for Germany to allow electric scooters / e-rollers on the roads?
Why does the hash of infinity have the digits of π?
Why is 'additive' EQ more difficult to use than 'subtractive'?
3 prong range outlet
Which European Languages are not Indo-European?
Shorten or merge multiple lines of `&> /dev/null &`
Aurora-mysql cross-region replicas fail with views/procedures/functions
Show Slave Status not working from console, nor clientUpdate RDS db via mysqlbinlog: “you need (at least one of) the SUPER privilege(s)”MySQL binlog format dilemma?Mysql Multi slave replicationHow to implement table security without SUPER privilege?Can't connect MySQL EC2 master to RDS slaveMysql replication, master_log_pos sync but tables not updateAccessing a Google Cloud Replica of an external on-prem masterSlave replication stops with Last_SQL_Errno: 1032Master- Master Replication breaks after server Reboot
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
UPDATE 2 - AWS released Aurora-mysql platform update 1.19.1 which addresses this and other issue. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.1191.html
UPDATE - AWS Support confirmed this is a known issue with Aurora mysql 1.x platform cross-region replicas. The workaround is to call mysql.rds_skip_repl_error; on the replica after making view/procedure/function changes on the master until the replica is un-stuck and then make those view/procedure/function changes on the replica manually
When I set up cross-region replication with Amazon Aurora-mysql (1.x platform, 5.6 engine version), it works fine until I create a view, function, or procedure in the master and then the slave encounters an error and replication ceases until I call CALL mysql.rds_skip_repl_error;. The error is:
[ERROR] Slave SQL: Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: 'mydatabasename'. Query: 'CREATE DEFINER=`myusername`@`%` PROCEDURE `MyProcedureName`... (SNIP SQL HERE)'
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-changelog.000016' position 24293051
This is because the slave runs replication as rdsrepladmin user, but when I create my procedure on master, I'm logged in as myusername. I can't set DEFINER to anything other than that because Aurora won't let me have SUPER privilege (which you need in order to create a view/procedure/function as a different user).
I also don't seem to be able to ignore views/procedures/functions as part of the replications.
Is there any way around this besides calling CALL mysql.rds_skip_repl_error; on the slave every time I have to touch a view/proc/function?
amazon-web-services mysql-replication amazon-rds
asked May 9 at 21:37
chadmyerschadmyers
1011
add a comment |
UPDATE 2 - AWS released Aurora-mysql platform update 1.19.1 which addresses this and other issue. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.1191.html
UPDATE - AWS Support confirmed this is a known issue with Aurora mysql 1.x platform cross-region replicas. The workaround is to call mysql.rds_skip_repl_error; on the replica after making view/procedure/function changes on the master until the replica is un-stuck and then make those view/procedure/function changes on the replica manually
When I set up cross-region replication with Amazon Aurora-mysql (1.x platform, 5.6 engine version), it works fine until I create a view, function, or procedure in the master and then the slave encounters an error and replication ceases until I call CALL mysql.rds_skip_repl_error;. The error is:
[ERROR] Slave SQL: Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: 'mydatabasename'. Query: 'CREATE DEFINER=`myusername`@`%` PROCEDURE `MyProcedureName`... (SNIP SQL HERE)'
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-changelog.000016' position 24293051
This is because the slave runs replication as rdsrepladmin user, but when I create my procedure on master, I'm logged in as myusername. I can't set DEFINER to anything other than that because Aurora won't let me have SUPER privilege (which you need in order to create a view/procedure/function as a different user).
I also don't seem to be able to ignore views/procedures/functions as part of the replications.
Is there any way around this besides calling CALL mysql.rds_skip_repl_error; on the slave every time I have to touch a view/proc/function?
amazon-web-services mysql-replication amazon-rds
asked May 9 at 21:37
chadmyerschadmyers
1011
1
Check the value oflog_bin_trust_function_creatorsin the parameter group. It almost certainly needs to be enabled.
– Michael - sqlbot
May 9 at 23:35
Thank you! I do havelog_bin_trust_function_creatorsset to (1) on both the master and the slave.
– chadmyers
May 10 at 3:15
add a comment |
UPDATE 2 - AWS released Aurora-mysql platform update 1.19.1 which addresses this and other issue. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.1191.html
UPDATE - AWS Support confirmed this is a known issue with Aurora mysql 1.x platform cross-region replicas. The workaround is to call mysql.rds_skip_repl_error; on the replica after making view/procedure/function changes on the master until the replica is un-stuck and then make those view/procedure/function changes on the replica manually
When I set up cross-region replication with Amazon Aurora-mysql (1.x platform, 5.6 engine version), it works fine until I create a view, function, or procedure in the master and then the slave encounters an error and replication ceases until I call CALL mysql.rds_skip_repl_error;. The error is:
[ERROR] Slave SQL: Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: 'mydatabasename'. Query: 'CREATE DEFINER=`myusername`@`%` PROCEDURE `MyProcedureName`... (SNIP SQL HERE)'
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-changelog.000016' position 24293051
This is because the slave runs replication as rdsrepladmin user, but when I create my procedure on master, I'm logged in as myusername. I can't set DEFINER to anything other than that because Aurora won't let me have SUPER privilege (which you need in order to create a view/procedure/function as a different user).
I also don't seem to be able to ignore views/procedures/functions as part of the replications.
Is there any way around this besides calling CALL mysql.rds_skip_repl_error; on the slave every time I have to touch a view/proc/function?
amazon-web-services mysql-replication amazon-rds
asked May 9 at 21:37
chadmyerschadmyers
1011
UPDATE 2 - AWS released Aurora-mysql platform update 1.19.1 which addresses this and other issue. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Updates.1191.html
UPDATE - AWS Support confirmed this is a known issue with Aurora mysql 1.x platform cross-region replicas. The workaround is to call mysql.rds_skip_repl_error; on the replica after making view/procedure/function changes on the master until the replica is un-stuck and then make those view/procedure/function changes on the replica manually
When I set up cross-region replication with Amazon Aurora-mysql (1.x platform, 5.6 engine version), it works fine until I create a view, function, or procedure in the master and then the slave encounters an error and replication ceases until I call CALL mysql.rds_skip_repl_error;. The error is:
[ERROR] Slave SQL: Error 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation' on query. Default database: 'mydatabasename'. Query: 'CREATE DEFINER=`myusername`@`%` PROCEDURE `MyProcedureName`... (SNIP SQL HERE)'
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [Warning] Slave: Access denied; you need (at least one of) the SUPER privilege(s) for this operation Error_code: 1227
2019-05-09 02:29:10 8118 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin-changelog.000016' position 24293051
This is because the slave runs replication as rdsrepladmin user, but when I create my procedure on master, I'm logged in as myusername. I can't set DEFINER to anything other than that because Aurora won't let me have SUPER privilege (which you need in order to create a view/procedure/function as a different user).
I also don't seem to be able to ignore views/procedures/functions as part of the replications.
Is there any way around this besides calling CALL mysql.rds_skip_repl_error; on the slave every time I have to touch a view/proc/function?
amazon-web-services mysql-replication amazon-rds
amazon-web-services mysql-replication amazon-rds
asked May 9 at 21:37
chadmyerschadmyers
1011
asked May 9 at 21:37
chadmyerschadmyers
1011
edited May 13 at 15:11
chadmyers
asked May 9 at 21:37
chadmyerschadmyers
1011
asked May 9 at 21:37
chadmyerschadmyers
1011
asked May 9 at 21:37
chadmyerschadmyers
1011
1011
1
Check the value oflog_bin_trust_function_creatorsin the parameter group. It almost certainly needs to be enabled.
– Michael - sqlbot
May 9 at 23:35
Thank you! I do havelog_bin_trust_function_creatorsset to (1) on both the master and the slave.
– chadmyers
May 10 at 3:15
add a comment |
1
Check the value oflog_bin_trust_function_creatorsin the parameter group. It almost certainly needs to be enabled.
– Michael - sqlbot
May 9 at 23:35
Thank you! I do havelog_bin_trust_function_creatorsset to (1) on both the master and the slave.
– chadmyers
May 10 at 3:15
1
1
Check the value of
log_bin_trust_function_creators in the parameter group. It almost certainly needs to be enabled.– Michael - sqlbot
May 9 at 23:35
Check the value of
log_bin_trust_function_creators in the parameter group. It almost certainly needs to be enabled.– Michael - sqlbot
May 9 at 23:35
Thank you! I do have
log_bin_trust_function_creators set to (1) on both the master and the slave.– chadmyers
May 10 at 3:15
Thank you! I do have
log_bin_trust_function_creators set to (1) on both the master and the slave.– chadmyers
May 10 at 3:15
add a comment |
0
active
oldest
votes
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%2f966635%2faurora-mysql-cross-region-replicas-fail-with-views-procedures-functions%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f966635%2faurora-mysql-cross-region-replicas-fail-with-views-procedures-functions%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
Check the value of
log_bin_trust_function_creatorsin the parameter group. It almost certainly needs to be enabled.– Michael - sqlbot
May 9 at 23:35
Thank you! I do have
log_bin_trust_function_creatorsset to (1) on both the master and the slave.– chadmyers
May 10 at 3:15