RDM and AWS Free RDM MySQL as a source.
Aloha,
I've setup a mysql database on AWS in the free tier. I used these instructions as the way to setup the database.
https://aws.amazon.com/getting-started/tutorials/create-mysql-db/
I am able to connect to the database from RDM. The test connects. But I run into problems when I try to setup the schema.
The error from RDM is:
You do not have the SUPER privilege and binary logging is enabled (you "might" want to use the less safe log_bin_trust_function_creators variable)
The detailed error message is:
MySql.Data.MySqlClient.MySqlException (0x80004005): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at MySql.Data.MySqlClient.MySqlScript.Execute()
at Devolutions.RemoteDesktopManager.Business.DataSources.MySQLConnectionDataSource.ExecuteScript(String script, IDbConnection dbConnection)
at Devolutions.RemoteDesktopManager.Business.DatabaseUpgradeAction.Execute(DatabaseConnectionDataSource dataSource)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.Upgrade(Boolean silent, LogMessageDelegate logMessage)
I found an aws article saying I should add a cutom parameter group and change the parameter
log_bin_trust_funtion_creators
to a '1'. But that is the default setting. So i changed it to a '0' and did not do a thing.
Any suggestions? The aws free tier has only forum support and i only see responses to the likes of "Did you follow the directions?"
All Comments (28)
Hello,
To better assist you, we require more information about the issue
What version of RDM are you using?
Are you starting RDM with specific options ? (32-64 bits run as administrator or standard)
Also the error looks like the user do not have the possibility to create table in MySQL server. Do you have full access to this database?
Regards,
David Grandolfo
13.6.7.0 64-bit I am using the trial version for enterprise.
About says I am "Administrator/SysAdmin/DbOwner"
I created the database instance using the instructions in a link provided in my previous post. My guess is the account I created isn't a full admin or there are option parameters I can set to i can have that capability to do that.
I see a few posts in the aws rds forum refering to removing the "definer".
"All you need to do is remove definer from the views/procedures that use them, that will cause the current user to become the definer.
Super privs are not given as a user could interrupt the automation in place with RDS. "
Is there a way for you to give me an sql script that will setup the database that is tailored to the above?
I see a few posts in the aws rds forum refering to removing the "definer".
"All you need to do is remove definer from the views/procedures that use them, that will cause the current user to become the definer.
Super privs are not given as a user could interrupt the automation in place with RDS. "
Is there a way for you to give me an sql script that will setup the database that is tailored to the above?
Or something like this? Another post from AWS.
"Thank you
i use the sed to replace all the stuff like 'DEFINER=xxx' with DEFINER=CURRENT_USER then it works
like this
sed -ie "s/DEFINER=\`a-z0-9A-Z*\`@\`a-z0-9A-Z*\`/DEFINER=CURRENT_USER /g" you sql file"
Odd, we don't have any DEFINER in the create script.
I will send you our SQL script via private message. Make sure you replace _DATABASE_NAME_ with your database name prior to executing the script.
Please let me know if any of the steps fail.
Best regards,
Stéfane Lavergne
So I used mysql bench to connect to the database on aws. Loaded the script and ran it after updating the database name in the script. So here's where the error get's generated:
-- Version 1.19
$$
CREATE TRIGGER tr_After_Insert_Connections AFTER INSERT ON Connections
FOR EACH ROW
BEGIN
CALL pr_UpdateConnectionCache();
END;
I've attached a screenshot.
AWS documentation has this explination about triggers.
https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/
I've added a parameter group and set log_bin_trust_function_creators=1
So i'm at a loss why the database cannot be initialized.
We are setting up an AWS MySQL for testing and will get back to you soon.
Stéfane Lavergne
Make sure you reboot your DB instance.
We just setup a new MySQL AWS instance, created a new RDM database and had the exact same issue you were seeing. Applied the log_bin_trust_function_creators=1 to a new parameter group. Still had the error. Rebooted the DB instance as stated in the KB (https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/) and all works now.
We will add this to our documentation.
Best regards,
Stéfane Lavergne
I didn't apply the parameter group to the database. Once i did that it worked. Well the database i had was half initialized. So instead of trying to figure out how to clear the database, I deleted the database. Then started from scratch and applied the new paramerter group with log_bin_trust_function_creators=1, following the KB instructions correctly this time. I then connected RDM, it initialized the schema on the first try. Now to actually finally try the enterprise features.
I have one more question with using the AWS mysql database. I do want to make sure I'm connecting with SSL or some type of encryption. AWS says it's mysql is capable.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html#MySQL.Concepts.SSLSupport
How do I configure that with RDM? This is will be a requirement for us. A secured connection will be required for implementation.