14

When I run maven test locally is passed. But got this error when I run it on CI server. 

Error Message
Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Stacktrace
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Caused by: java.net.UnknownHostException: mysql

When running local test, they all passed, maven test default setting provided by IntelliJ IDEA is used.
Since the error complains about database connection, so I checked by Jenkins Audit to Database Plugin. Connection Successful!

enter image description here

The connection parameter in my application.properties also corresponds to this 

spring.datasource.url=jdbc:mysql://mysql:3306/database?useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=password
spring.datasource.maxActive=5

The MySQL in the URL is the MySQL docker container name. If change it with localhost or private IP in docker container inspect mysql the error message is the same, while the Stacktrace is a little different on last two lines.

for localhost

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
Caused by: java.net.ConnectException: Connection refused (Connection refused)

for private IP

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. 
Caused by: java.net.SocketTimeoutException: connect timed out

The different I think is the host in URL, localhost is used for the local test. While the Jenkins server used Docker bridge network.

The container status is: 

docker container ls
CONTAINER ID        IMAGE                 COMMAND                  CREATED             STATUS              PORTS
                                          NAMES
51ea7c7864a4        mysql:5.7             "docker-entrypoint.s…"   19 hours ago        Up 19 hours         0.0.0.0:3306->3306/tcp                             mysql
de364f7b5eaf        maven:3-jdk-8         "/usr/local/bin/mvn-…"   21 hours ago        Up 21 hours
                                          optimistic_stallman
a6545591e358        jenkinsci/blueocean   "/sbin/tini -- /usr/…"   43 hours ago        Up 43 hours         0.0.0.0:50000->50000/tcp, 0.0.0.0:2048->8080/tcp   frosty_cray

When I run the JUnit test in IntelliJ, it fails sometimes on the local environment. The error log is like:

Caused by: org.h2.jdbc.JdbcSQLException: Schema "DATABASE" not found; SQL statement:
TRUNCATE TABLE database.data_log

I have searched the issue, it's said h2 database use upper case by default. After run maven test, this issue will go if run JUnit test in IDE again. But this should be not related to the root cause.

Search on the error message, find some similar question but with different nested exception:

Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException

SpingREST: Could not open JPA EntityManager for transaction; nested exception is org.hiberna

Could not open JPA EntityManager for transaction; org.hibernate.exception.GenericJDBCException: Could not open connection

Could not open JPA EntityManager for transaction in spring

All of them is about nested exception is javax.persistence.PersistenceException
But nested exception is org.hibernate.exception.JDBCConnectionException: is my situation. Read Connect Java to a MySQL database
however since that plugin connects OK, means the connection from Jenkins container to MySQL container is fine.

Summarise:
1. local test with maven passed
2. Jenkins plugin connect to MySQL success
3. Integration test fails when run from Jenkins
4. local test environment is WIN10 64bit; Jenkins run in docker container on Ubuntu 16.04 64bit server, with MySQL 5.7 container connects to the same bridge network.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Shihe Zhang
  • 2,641
  • 5
  • 36
  • 57
  • 1
    If you are using Spring boot and running your test DB in docker, I would recommend you to have a look at https://www.testcontainers.org/ , and here is instruction how to use it in the best way: https://areguig.github.io/test-springboot-apps-using-testContainers-and-spock/ (you can skip Spock part) – Max Farsikov Aug 06 '18 at 14:39
  • @MaxFarsikov the `testcontainer` seems cool, however, after reading their document and sample, I don't feel confident enough to use it in the current project. Maybe I will try it on some side project later. – Shihe Zhang Aug 07 '18 at 01:35
  • 1
    The error is stating that `no packets were sent`, indicating that your application and mySQL is not talking to each other....Also you have mentioned that Jenkins and mySQL is talking to each other... that's fine ...But have you confirmed if `mySQL and your application` (where is it deployed on the remote server) is talking to each other ?? check this for more info https://stackoverflow.com/questions/2983248/com-mysql-jdbc-exceptions-jdbc4-communicationsexception-communications-link-fai – rohit thomas Aug 07 '18 at 03:23
  • @rohitthomas I agree with you. The problem possibly lying between MySQL and the application. I have read that post and referred it in mine. Still, no clue where to check. Local test sometimes fails but will pass after I run `maven test` and will pass later on for a while. – Shihe Zhang Aug 07 '18 at 03:43
  • couple things I need to understand .... The application is running in your local and working fine?? the application is hosted on another server and it fails only there or also in local ?? finally when does it fail sometimes have you debugged that scenario ?? also maven test will pass --> have you written the connection only for this scenario ?? – rohit thomas Aug 07 '18 at 03:48
  • Running locally is fine. Always fail on another server. I will update the local error log when it fails. I don't think the connection is only for the scenario but not totally sure. – Shihe Zhang Aug 07 '18 at 03:54
  • where is mySQL located local or another server apart from where the application is located ...local then it's that your not connected/exposed to the internet... same thing with the other server mostly or its that they haven't spoken to each i.e. the servers/firewall – rohit thomas Aug 07 '18 at 04:02
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177538/discussion-between-shihe-zhang-and-rohit-thomas). – Shihe Zhang Aug 07 '18 at 05:07
  • @ShiheZhang I am having few queries: 1. Are both part of same swarm network? 2. Did you try using --it mode and try to ping mysql from your container 3. If both are not part of the same network, Are you using attachable networks where your containers are running while communicating with each other? – Vivek Shukla Aug 08 '18 at 07:20
  • @VivekShukla I tried to ping from another container is work. They are on the same bridge network. My problem solved by changing the host in JDBC URL to host machine private IP. Trying to get a reasonable explanation to post as an answer. – Shihe Zhang Aug 08 '18 at 09:01
  • @ShiheZhang You treat docker container as a 'special node/machine'. If you put database configuration as localhost, it tries to look inside of the container to find out if any such service is running. That is the reason I asked you to use --it to know whether you are able to ping. Basically, docker needs to discover the services by ip or service name. – Vivek Shukla Aug 08 '18 at 09:41
  • `ping mysql from your container` I'm not sure what do you mean. `docker exec -it jenkinsContainer /bin/bash` then I ping the mysql container there is working. Do you mean `exec -it mysqlContainer` and ping itself? – Shihe Zhang Aug 08 '18 at 09:45

2 Answers2

2

Thanks to @rohit-thomas. We narrow down the question to something related to the host of the URL.
The simple answer is changing the host of the JDBC URL in the spring boot application.properties to the docker host IP address. From
spring.datasource.url=jdbc:mysql://mysql:3306/database?
to
spring.datasource.url=jdbc:mysql://172.17.0.1:3306/database?

From inside of a Docker container, how do I connect to the localhost of the machine?
This post also helps as the final solution. 

ip addr show docker0
4: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default        
    ...   
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       ...

My conclusion is:
The Jenkins container builds from an image is able to communicate with the MySQL container with its container name or private address on docker bridge network. However, as the application built by the Jenkins is not able to do that. Since the MySQL container port has bound to the host machine, the application could communicate with MySQL container through the host port.

If the conclusion is wrong, comments are welcome.

Shihe Zhang
  • 2,641
  • 5
  • 36
  • 57
0

Some things you can check that might help you resolve this issue.

  • In application.properties try to use docker host IP address.

    from

    spring.datasource.url = jdbc:mysql://mysql:3306/DATABASE_URI_PATH

    to

    spring.datasource.url = jdbc:mysql://192.168.99.100:33060/DATABASE_URI_PATH

    Note: You will need to map your IP and port when you do docker run or ports in docker file.And use the same docker network among your containers.

  • Verify if you server app can reach your mysql or vice versa. Go inside the docker container and try to ping.

Winnnn
  • 56
  • 4
  • @ShiheZhang i just edited my answer. Should be from jdbc:mysql://mysql:3306/DATABASE_URI_PATH to jdbc:mysql://192.168.99.100:3306/DATABASE_URI_PATH – Winnnn Aug 13 '18 at 02:22