Database access problems from Kubernetes pod

10/28/2021

I am trying to get my second project(prm) into the cloud. Both the projects (pyp and prm) access the same database and with the same credentials. The first project succeed, and the second gets Access denied for user root.

Some excerpts from my definitions:

apiVersion: v1
kind: Secret
metadata:
name: pyp-secret
data:
mysql_password: "<password>"

apiVersion: v1
kind: Service
metadata:
  name: pyp-db
spec:
  type: ClusterIP
  ports:
  - port: 3306
    targetPort: 3306
  selector:
    app: pyp
    service: mysql
---

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pyp-db
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: pyp
      service: mysql
  template:
    metadata:
      labels:
        app: pyp
        service: mysql
    spec:
      containers:
      - image: mysql:8
        name: pyp-db
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: pyp-secret
              key: mysql_password
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: prm
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: prm
      service: cron
  template:
    metadata:
      labels:
        app: prm
        service: cron
    spec:
      containers:
      - image: prm-image-name
        imagePullPolicy: Always
        name: prm
        env:
        - name: MYSQL_HOST
          value: pyp-db
        - name: MYSQL_USER
          value: root
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: pyp-secret
              key: mysql_password

This is excerpts from the log where you can see the url for connecting the database, and the error I get:

The url and the error message

This is from my java-application:

static Connection  init(String host,String user, String password){
		Connection con = null;
		try {
			if (con == null) {
				Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
					String url = "jdbc:mysql://" + host + ":3306/PP_Master?user=" + user + "&password=" + password;
				logger.trace("DB url:" + url);
				con = DriverManager.getConnection(url);
			}

		} catch (Exception ex) {
			logger.error("init: "  ,  ex);
		}

		return con;
	}

My cloud is hosted on Minikube and the database is Mysql 8.0.27. It is accessible from my localhost when I give the same credentials. My other project(pyp) is running in Tomcat, and connecting with credentials from a connection pool defined in context.xml. They use the same driver to connect the database. That project access the database just fine.

This is the users defined in the database: Database users

I've also counted the number of characters in the url by url.length(). This gave 72 characters that match the actual size. So there are no extra invisible characters in the password.

About the project(pyp) that succeed in getting access to the database. Some days ago, I got an SqlSyntaxError from the first statement against the database, even if it was only "USE PP_Master", and it had worked before. There were no errors on the logs. I had to delete the Minikube container, and start a new one. That gave me access to the database from the pyp-project. I wonder if one project using a DataConnectionPool could reserve access to the database, so no other projects could access it ? I've tried now to change from connection pool to only one connection at time in the pyp-project. But that didn't solve the problems with the prm-project. I also tried simply to remove the deployment and the pyp-pod, but that didn't help the prm-project eigther. So that hypotesis seem to be wrong. I looked at the pyp-db log. This pod is containing the database. I don't know if some of this information could have an impact on my problem ? pyp-db log I have also tried to delete the minikube again. This time I only deployed the pyp-db and the prm pods, to avoid a possible conflict with the pyp pod. But to no avail. The error-message connected to the prm pod persists. So, it must be something wrong between the prm and the pyp-db, that has nothing to do with the pyp-pod. So I've testified that it is not due to a conflict with the pyp-pod.

I really hope someone is able to help me. I've been stuck for several days with this problem. If there are more information that could help, just ask.

-- Anne Helene Holmen
java
kubernetes
mysql

1 Answer

11/1/2021

Eventually, I managed to get rid of the "access denied" problem. I just changed the content in the java-code to this:

String url = "jdbc:mysql://" + host + ":3306/PP_Master";
con = DriverManager.getConnection(url, user, password);

Before it was :

String url = "jdbc:mysql://" + host + ":3306/PP_Master?user=" + user + "&password=" + password;
con = DriverManager.getConnection(url);
-- Anne Helene Holmen
Source: StackOverflow