External database works fine but can't do setup upgrade in CLI

My store is in AWS lightsail largest server tier.

Exported DB from localserver to its own external server, connect with M2 = works like a charm. Later wanted to install an extension and when type in CLI setup upgrade getting this error:

SQLSTATE[HY000]: General error: 1419 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), query was: CREATE TRIGGER trg_catalog_category_product_after_insert AFTER INSERT ON catalog_category_product FOR EACH ROW
BEGIN
INSERT IGNORE INTO

catalogrule_product_cl

(
entity_id
) VALUES (NEW.
product_id
);
END

spent many days researching and found nothing. am not expert in Mysql obviously but I understand is something about permissions… any idea?

my thoughts are:
my M2 is based on craigs tutorial. so I have an user with permissions for magento2 inside the /html folder. aws logs you in with ubuntu user. so am not sure if just changing the user to perform the export have to do with this? the weird thing is, the DB works perfectly. is just I can’t do the commands setup:upgrade. with my M2 user.

I doubt this has anything to do with your filesystem. I would hazard a guess that you’re executing a mysql command as a user with insufficient privileges.

I recently discovered that a change was made in MySQL security where you have to give a user (e.g. magento user) the Global Privilege of Processing in order to do run mysqldump from the CLI.

And I’d also ensure that the user has all Database Privileges checked too.

What command are you attempting to execute? (strip out names/passwords when you reply)

hmm I used a guide that was not related to Magento. that was a month ago and I don’t remember the link. How can I give the magneto 2 user this privileges in mysql?
I have 3 users in the server: ubuntu, magento user, the mysql user.

Log into phpMyAdmin as root or superuser and apply what I’ve done to the magento user.

Thanks i got to add my magento user to the DB with all privileges, but not to the external DB.

Maybe I should had mention that this is a lightsail (aws) problem I found. I been trying to create a load balancer (2 servers connected to 1 database) and in the process after duplicating the 2 M2 instances , even connecting them to the external DB. I did the setup upgrade to and then I found the problem.

When creating that database even providing the user and password they require it does not have all privileges. this is killing me because I was planning to complete that load balancer this weekend. it seems the only option is to move everything away from lightsail to Ec2 and RDS

1 Like

Sorry. I don’t have any experience with AWS or Load Balancer environments. But as this isn’t a Magento issue (it’s a symptom) and more likely a MySQL issue then you’d probably be better off asking stackexchange.com or serverfault.com.

Thanks I will check those. I just can’t handle more than 500 users purchasing in the website without starting to crash the server.

Let me know how you get on.

ok I still don’t know why I can’t use Key pairs in this server. even creating an snapshot and assigning a new Kp it does not work. Even exporting to ec2 and changing every setting, KP do not work on it. I might have changed something in the system half asleep and now am paying the price idk.

The problem: The magento user can not run setup upgrade with an external Database in AWS even if is working with magento.

create external DB and aws lightsail give you a master user. However that user Can’t give grant all access to magento user. it lacks full roles too somehow.

A server that can’t be accessed with key pairs.

How I got it fix.
created 2 users and added to sudoers with password. 2 just in case.

Created an snapshot and exported to EC2.

Create external Db and its masteruser.

Login to ssh with sudoer, swith to root.

create an empty db in the external DB and leave \q

mysqldump the DB from local. So here is the trick: if you don’t do this you can’t import the db to external DB in AWS. and in lightsail you can import but won’t have full access even as masteruser:

sed -i -e ‘s/[email protected]/DEFINER= aws DB master [email protected]%/g’ DB.sql

THIS WAS IT.

NOW export the DB to External DB:

mysql -h rds.amazonaws.com -u masteruser -p db < db.sql

Now change env.php have a drink and thank God.

1 Like

Congratulations on the win! Really strange how the external DB MySQL user doesn’t have all permissions to do what you need.

Thank you for sharing your resolution :+1:

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.