It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

1 answer

1 accepted

1 vote
Answer accepted
DPK J Community Leader Jan 29, 2020

@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.

Hello @DPK J  

 

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 

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

@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'
Like Vivek_Modi likes this

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)

DPK J Community Leader Feb 02, 2020

@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
Like Vivek_Modi likes this

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

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!

DPK J Community Leader Feb 02, 2020

@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.
Like Vivek_Modi likes this

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 

DPK J Community Leader Feb 02, 2020

@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.

Like Vivek_Modi likes this

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

@DPK J 

Sir any solution for that

 

Thanks for response.

DPK J Community Leader Feb 15, 2020

@Vivek_Modi  Oh! I somehow missed your previous comment.

 

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

They all are quite easy to under stand.

Like Vivek_Modi likes this

Hello Sir 

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

Regards 

Hello @DPK J 

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Bitbucket Pipelines

What We Learned When We Researched Open Source Vulnerabilities in 7 Popular Coding Languages

...hey are a part of us, shaping how we interact with the world around us. The same holds true for programming languages when we think about how different kinds of vulnerabilities raise their heads in t...

1,049 views 0 3
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you