How to use Mysql in bitbucket pipeline to test my test cases in cloud.

[Vivek_Modi]

Hey Everyone ! 

I am new in BitBucket pipelines. My Problem is to test Database in pipelines. I am using java gradle to integrate pipeline. I searched so many things to use mysql but i cannot understand how to connect the database my pipelines and run into the cloud to check my test case . I am confusing how to use services of bitbucket. Please anyone Help me how to learn these things and implement on my assignement.

Thanks

[DPKJ · Accepted solution]

@Vivek_ModiWelcome to the Community!

You will need to define service to use database in your pipeline.

You will get all information here - https://confluence.atlassian.com/bitbucket/test-with-databases-in-bitbucket-pipelines-856697462.html

It also include MySQL example.

[Vivek_Modi]

Hello @DPKJ  

Thanks for replying me. I already done this steps but how to access the database resources. I mean i need to test my database test case of CRUD functions. So how can i use all services in pipelines. Its simple like dummy local database in cloud. Sorry For inappropriate questions. Below is my pipeline File code. 

Thanks 

[Vivek_Modi]

image: openjdk:8

pipelines:
default:
- step:
name: Test only
script:
- bash PipeExample/scripts/build-projects.sh
services:
- mysql
definitions:
services:
mysql:
image: mysql:5.7
environment:
MYSQL_DATABASE: pipelines
MYSQL_ROOT_PASSWORD: root_user_password
MYSQL_USER: test_user
MYSQL_PASSWORD: test_user_password

[DPKJ]

@Vivek_Modi 

As you have defined service and you know details of database connections, I don't think you should have any problems in next steps.

• Set your connection values to use defined database
  • You can also pass them to `build-projects.sh`
  • Or if you have config file, set these value in it
• Create some dummy data,
  • you can write a simple script to do that (or load fixtures)
  • you can load it from predefined sql file
• Run tests.

I tried this pipeline and I think it will give you idea of using it.

image: debian:stretch

pipelines:
  pull-requests:
    '*':
      - step:
        script:
          - apt-get update && apt-get install -y mysql-client
          - mysql -h 127.0.0.1 -u root -ptest_user_password -e "SHOW DATABASES"
        services:
          - mysql
definitions:
  services:
    mysql:
      image: mysql:5.7
      variables:
        MYSQL_DATABASE: 'pipelines'
        MYSQL_ROOT_PASSWORD: 'test_user_password'

[Vivek_Modi]

I got the this error

mysql -h 127.0.0.1 -u root -ptest_user_password -e "SHOW DATABASES"

ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

[DPKJ]

@Vivek_Modi  Please try this one, above was just an example.

image: debian:stretch

pipelines:
  default:
    - step:
        name: MySQL Test
        script:
          - apt-get update && apt-get install -y mysql-client
          - mysql -h 127.0.0.1 -u root -proot_user_password -e "SHOW DATABASES"
        services:
          - mysql
        
definitions:
  services:
    mysql:
      image: mysql:5.7
      environment:
        MYSQL_DATABASE: pipelines
        MYSQL_ROOT_PASSWORD: root_user_password
        MYSQL_USER: test_user
        MYSQL_PASSWORD: test_user_password

[Vivek_Modi]

Hello 

Thanks its work for me. Can you please example me what is -proot_user_password, -e is working ? 

Another thing what the the work of environment variable like MYSQL_... ... What is doing actually those 4 variables .. 

Thanks for supporting me to learn all these things.

Thanks

[Vivek_Modi]

Hello 

mysqladmin -u root -proot_user_password -p create databasename

I am using this command to create database but this gives error to me

error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")'Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

[DPKJ]

@Vivek_Modi  On your first question,

• -proot_user_password
  • This is '-p' and then the password of user that is trying to accessing.
• -e
  • This is to execute SQL mentioned after this. In above case it was 'SHOW DATABASES'
• Coming to environment variables,
  
  • MYSQL_DATABASE - Name of database I wish to have in defined MySQL server
  • MYSQL_ROOT_PASSWORD - Password for root access to database
  • MYSQL_USER - A non root user for your database
  • MYSQL_PASSWORD - Password to that non root user
• Take a look at this page https://hub.docker.com/_/mysql/ (Environment Variable section) for more details about MySQL image environment.

Coming to second part,

• This is not a great idea to create database using command (in case of pipeline)
  • Still if you want to do,
    • Your are missing '-e' before query which has to be quotes
    • Also, add host information '-h'
• To create database, define 'MYSQL_DATABASE' environment variable.
  • Like above I have created 'pipelines' database.

[Vivek_Modi]

Hello sir

Thanks for solving my all queries. Its really helpful information for me. This is my assignment work for my agile subject to create database test Design. I need to learn all agile Process for CI/CD.

You have mention above . This is not a great idea to create database using command (in case of pipeline). I want to know that any other idea to use of database to test my test case like connection test and other CRUD Function. 

I am just asking can we insert data in pipelines database and fetch all the details. I am using java in my test case. Do you have any idea? Sorry for my irrelevant questions .

Thanks Sir 

[DPKJ]

@Vivek_ModiGreat assignment problem. Now, coming to your questions,

We can create database in definition of MySQL service (the recommended way) 'MYSQL_DATABASE' environment variable is just doing that.

Now, once we are done with this, we have all the details about our database,

• Host of database (i.e. 127.0.0.1)
• Name of database (provided using MYSQL_DATABASE environment variable to service)
• Username and password of database (provided using MYSQL_USER and MYSQL_PASSWORD environment variables to service)

Now let us say you are trying to make connection to this database using JDBC in your simple hello world program, you will do it like this,

Class.forName("com.mysql.jdbc.Driver") //loading MySQL JDBC drivers
Connection connection = DriverManager.getConnection("jdbc:mysql//127.0.0.1:3306/<DB_NAME>", "<DB_USER>", "<DB_PASSWORD>");

Here our program need information, <DB_NAME>, <DB_USER>, and <DB_PASSWORD>, as we have this information in our pipeline (as defined in MySQL service), we can pass this to program using multiple ways,

• As command line arguments, which we can read in 'main' function of our program
• We can generate a config file (in xml, json, yaml or any other format) in pipeline and read that in program

Now, choice is up to you.

For CRUD, you can use ORM like Hibernate, Spring Persistence, JOOQ etc for ease.

[Vivek_Modi]

Hello Sir

Can you tell me some examples link of usinh hybernate with running in server of all crud function.

Thanks for your help. I am really appreciate of your support

Thanks

[Vivek_Modi]

@DPKJ 

Sir any solution for that

Thanks for response.

[DPKJ]

@Vivek_Modi  Oh! I somehow missed your previous comment.

You can find many example for Hibernate. Simple search on Github resulted these,

• https://github.com/bykowski/hibernate-crud-example
• https://github.com/anicetkeric/HibernateDemo
• https://github.com/jgpreetham/basic-hibernate-example

They all are quite easy to under stand.

[Vivek_Modi]

Hello Sir 

Thank you so much for helping me and providing the resource of material stuffs. Thanks a lot.

Regards 

[Vivek_Modi]

Hello @DPKJ 

I am running the class in pipelines. i have problem in some class regarding hibernate libary cannot find the hibernate libaray . I put all my hibernate jar in my lib folder

rm -rf PipeExample/bin
mkdir PipeExample/bin
find PipeExample/src -type f -name '*.java' -exec javac -d PipeExample/bin -cp "PipeExample/lib/*" {} + 

Always show error to compile hibernate library not found

can you tell how to compile all these 

Thanks and Regards