Connecting to an external mysql database

General Discussions
1

I am trying to connect my spring-boot api inside a kubernetes pod to an external database (a separate machine but within the local network), however when running, I get SQLNonTransientConnectionException, UknownHostException: mysql-svc, the api cannot connect to the database but whenever I ping the database address from inside the pod using kubectl exec -it, I can ping it successfully, anybody who has experienced the same error as mine?

here is my application.properties connection uri and kubernetes objects:

spring.datasource.url=jdbc:mysql://mysql-svc/test?autoReconnect=true

---
apiVersion: v1
kind: Service
metadata:
 name: mysql-svc
spec:
 type: ClusterIP
 ports:
 - port: 3306
   targetPort: 3306
---
apiVersion: v1
kind: Endpoints
metadata:
 name: mysql-svc
subsets:
 - addresses:
   - ip: 10.0.123.28
   ports:
   - port: 3306
     name: mysql-svc

Cluster information:
Kubernetes version: 1.16
Cloud being used: VMs on local network
Host OS: CentOS 7
CNI and version: Flannel latest
CRI and version: Docker 19.03.2

2

Hi,

Are you ping’ing the IP address, or the mysql-mvc name?

It seems to be a problem to dns-resolve the name mysql-mvc. Perhaps because your pod is not in the same namespace as your service?

Philippe

3

yes the ip address, I was able to ping the database server via ip address, I haven’t tried pinging with the mysql-svc name…

4

why would you need to create a service for a mysql server that’s outside of the kubernetes cluster? just provide the ip or the endpoint, i don’t think you do a service as that setups an ip inside the k cluster right?

5

If the database is external to the cluster, then m the service type cluster IP won’t help. That will try to match pods inside the cluster (there are none, as it is an external db).

You can use the IP to try to connect and make sure this works. Once this works, we can follow up with improvements. But let’s get started with the basic :slight_smile:

6

Hi,

Good day…
Have you got solution for this issue…? Am also trying to connect external oracle database in spring boot application deployed in kubernetes container. I created a service to connect the external oracle database and am able ping the oracle server inside the kubernetes container. But Application is not able to connect the oracle server and throwing socket time out exception.
Please help here to proceed.? thanks much.

application.yml
spring:
#profiles: dev
datasource:
jdbc-url: ‘jdbc:oracle:thin:@oracle-server:port/servicename’
username: —
password: —
driver-class-name: oracle.jdbc.OracleDriver

oracle-service.yml

kind: Service
apiVersion: v1
metadata:
name: oracle-server
spec:
type: ExternalName
externalName: oracle-server-hostnmae

Inside Kubernetes container

root@tomcat-springboot-pks-stateful-0:/# ping oracle-server-hostname
PING oracle-server-hostname (----ip----------) 56(84) bytes of data.
64 bytes from ----ip---------- (----ip----------): icmp_seq=1 ttl=49 time=31.5 ms
64 bytes from ----ip---------- (----ip----------): icmp_seq=2 ttl=49 time=30.9 ms
64 bytes from ----ip---------- (----ip----------): icmp_seq=3 ttl=49 time=31.0 ms
64 bytes from ----ip---------- (----ip----------): icmp_seq=4 ttl=49 time=31.1 ms

Kubernetes Pod Logs

18:16:18.405 [http-nio-8092-exec-5] ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path threw exception [Request processing failed; nested exception is org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLRecoverableException: IO Error: Socket read timed out] with root cause
oracle.net.ns.NetException: Socket read timed out
at oracle.net.ns.Packet.receive(Packet.java:350)
at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:159)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)

7

Did you find the solution for this? I’m also facing this issue.

8

Yes… I found… I created the external service for the oracle database server and connected with the springboot application with service name and it works… :slight_smile:

9

Hi @Sharanya_M, please share the service and application yaml details. Facing same issue.

10

sure… here you go…

kind: Service
apiVersion: v1
metadata:
name: /** oracle server name which is configured in springboot application.yml file … Ex - oracleserver /
spec:
type: ExternalName
externalName: / oracle hostname. Ex - oracleserver.xx.yyy.com **/

Springboot application.yml

spring:
#profiles: dev
datasource:
jdbc-url: ‘jdbc:oracle:thin:@oracleserver.xx.yyy.com:port/service_id’
username: xxxx
password: yyy
driver-class-name: oracle.jdbc.OracleDriver

11

Thank you.

12

your oracle server name…

13

Do I can use external for ip addr? I’m facing this problem when I try to connect external oracle database.

I can ping Database IP from inside container but the app cannot.

I create the Cluster Service and EndPoint for it but it doesn’t work.

14

You cannot use it with ip you have to have a dns name then only it will get resolved bt core DNS

15

Hi

I am trying to connect my external oracle database from the container using Proc C. I did the same step, it is not connecting getting tns destination host unreachable. Could any one help on this.

service.yaml
kind: “Service”
apiVersion: “v1”
metadata:
name: “external-mysql-service”
spec:
ports:
-
name: “mysql”
protocol: “TCP”
port: 1525
targetPort: 1525
nodePort: 0
selector: {}

Endpoint.yaml
kind: “Endpoints”
apiVersion: “v1”
metadata:
name: “external-mysql-service”
subsets:
-
addresses:
-
ip: hostipadd
ports:
-
port: 1525
name: “mysql”

proc application:
username : myuser
password: mypwd
connectionstring: external-mysql-service/servicename
(Actual connectionstring : ip:port/servicenamee")
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :connectionstring

Thanks

16 
To connect my app to the external db
## Is this service right for my app?

apiVersion: v1
kind: Service
metadata:
  name: my-service
spec:
  selector:
    app: MyApp
  ports:
    - name: http
      protocol: TCP
      port: 80
      targetPort: 9376    ## the port of the pod can be the same as that of the external db ?? ?
  externalIPs:
    - 80.11.12.10          ## can it be the ip of the db ???