Open mohamedkashifuddin94 opened 2 years ago
Hi @renecannao @pondix were you able to reproduce the issue ? or any more info needed ?
Have you ever been able to fix this issue ? I have the same, I can even login to the DB server from ProxySQL itself but not through ProxySQL.
This seems to be an issue I cannot resolve for 2-3 days now.
Anyone an idea?
Hi @mohamedkashifuddin94.
I would recommend to investigate if credentials on MySQL servers are correct, if 10.56.1.110 and 10.56.2.132 are able to connect.
What got my attention is that you have two entries in mysql_users
, same user (root
) but two distinct password and destination_hostgroup : please note that you can't have two users with the same username, this is not supported, and cannot be supported. ProxySQL authenticates the client, no matter the backend.
@di-rect : I don't know if you are doing the same mistake of @mohamedkashifuddin94 (two entries for the same username) , but I gave you the same recommendations. If you face an issue, please open a new ticket. Unless you know what is the issue in @mohamedkashifuddin94 setup, you can't assume you have "the same".
Thanks
@renecannao Thanks for responding!
I had the same issue; proxysq-admin synced the user in twice; even with the same password because I had a fqdn and a wildcard in MySQL but removed both and started from scratch with the same user and ended up here.
Credentials on SQL are OK as I can login using mysql -u -p on the ProxySQL server as that user, the user is a @% in my MySQL servers at the moment.
I can't get anything more logged about what is going on and why the clients are not accepted using a @% for my single user. I have tried default and sha2 passwords; both the same issue.
The issue which i m facing is i m connecting proxysql > pxc MySQL so when try to connect via proxysql to MySQL it shows a error
2021-10-23 17:39:57 mysql_connection.cpp:835:handler(): [ERROR] Failed to mysql_real_connect() on mysql-cluster-rg-1-haproxy.mysql1.svc.cluster.local:3306 , FD (Conn:32 , MyDS:32) , 1045: Access denied for user 'root'@'10.56.0.102' (using password: YES).
2021-10-23 17:40:23 MySQL_Session.cpp:5234:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'root'@'127.0.0.1' (using password: YES)
why does ha-proxy comes here from the above error ? It is because i m using a percona MySQL operator in K8s so when i spin up a percona operator i enable ha-proxy and then the ha-proxy monitors the pxc nodes and re-directs the connection to the active node. Thats the reason i use ha-proxy, In this use case i use a proxysql to redirect the incoming connections to the particular endpoint as mention in proxysql.cnf (mysql_servers,mysql_users) section.
----> i have added two percona operator in different namespace and deployed proxysql docker container as a pod in a namespace so all these 3 namespace have network connection between them.
Issues :
1.instead what really happen was it was showing a Access denied, but when i directly connect to the ha-proxy i m able to login and do all kind of query's. I have attached the image below
F.Y.I I have port forward it to local so it shows 127.0.0.1 instead of showing the actual IP in the error .
2.Initially i thought it was because of caching_sha2_password and i should set admin-hash_passwords=false but it was not the case since when i checked the password mechanism of pxc it was mysql_native_password so it shouldn't be a issue since proxysql supports mysql_native_password mechanism.
In mysql_servers details section in proxysql.cnf i try to give FQDN then later i gave the IP both it reaches endpoint of ha-proxy but doesn't connect and login it shows access denied.
while connecting to ha-proxy directly then using show processlist query i can see proxysql monitor user is able to connect to both the operator and able to monitor it. then why proxysql not connecting with root user in those operator. i even try to use different users but no luck same result only monitor user seems connecting to ha-proxy via proxysql
[ ] ProxySQL version I have used docker versions tag : latest, 2.3.2 ,2.3.0 ,2.0.16 all gave the same issue
[ ] The steps to reproduce the issue
using this link you can deploy two operators but in deploy/cr.yaml file just change the metadata as https://github.com/percona/percona-xtradb-cluster-operator/tree/main/deploy In one namespace
metadata: name: mysql-cluster-rg-1 and another namespace
metadata: name: mysql-cluster-rg-2
Just clone the code in two different folders and deploy it in different namespace
then run these three commands to install operator kubectl apply -f bundle.yaml -n namespace eg.mysql1 and mysql2 for another namespace kubectl apply -f cr.yaml -n namespace kubectl apply -f secrets.yaml -n namespace For testing change the root password in second operator so while connecting to proxysql you can identify whether your connecting so primary or secondary operator.
To deploy proxysql docker as a pod in K8s you can use this link : https://github.com/ProxySQL/kubernetes/tree/master/proxysql-cluster In the folder name files/ you will find the proxysql.cnf so you can replace those details with the my proxysql.cnf which i have added below then give a helm install proxysql-test proxysql-cluster/ ---> ( syntax ) helm install release name then the folder name
Then using kubectl port-forward command you can port-forward to your local using the svc
kubectl port-forward svc/proxysql-test 6033:6033 -n namespace same you can
proxysql.cnf sample file which i reference from below link : https://github.com/smartboxgroup/proxysql-docker/blob/master/proxysql.cnf.template
/var/lib/proxysql/proxysql.log
)The above error log is when i gave FQDN and below is when i gave IP for the endpoint :
Hope i have provided all the necessary details to reproduce the issue, the only thing i expect is proxysql should forward the request based on the details proved in proxysql.cnf .
Thanks in Advance
@renecannao @pondix @JavierJF