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.
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.
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
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.
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/DEFINER=root@localhost/DEFINER= aws DB master user@%/g’ DB.sql
