wercker.com connect to MySQL database

Question

Background

I'm trying to build some automated tests for a Yii2 app using http://wercker.com. I have created my own Docker repo https://hub.docker.com/r/consynki/yii2/ that provides a simple LAMP stack.

I'm using that Docker repo as the box in my wercker.yml file with a few simple steps to setup my app, initialized the database, then run my phpunit tests

box: consynki/yii2

build:
  steps:

- script:
    name: Update enviroment dependencies
    code: |-
      curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/bin/ --filename=composer

- script:
    name: Install Composer dependencies
    code: |-
      rm -rf ./vendor
      /usr/bin/composer install --no-interaction --prefer-source

- script:
    name: Apache site install
    code: |-
      sudo chmod -R 755 /var/www
      sudo cp -r ./ /var/www/example.lan/
      sudo chown -R $USER:$USER /var/www/example.lan/
      sudo cp ./config/example.lan.conf /etc/apache2/sites-available/example.lan.conf
      sudo a2ensite example.lan.conf
      sudo cp -fr ./config/hosts /etc/hosts
      sudo service apache2 restart

- script:
    name: Create database
    code: |-
      mysql -uroot -e "SHOW DATABASES;"
      mysql -uroot -e "CREATE DATABASE IF NOT EXISTS test"

- script:
    name: PHPUnit integration tests
    code: |-
      ./vendor/bin/phpunit --configuration phpunit.xml

Problem

The problem is, when I run my build using the wercker CLI wercker build, it fails to connect to mysql. I keep getting the following connection error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to run the 'Create database' step.

 - script:
    name: Create database
    code: |-
      mysql -uroot -e "SHOW DATABASES;"
      mysql -uroot -e "CREATE DATABASE IF NOT EXISTS test"

I know the docker container has a mysql connection as I can ssh into it and run mysql -uroot -e "SHOW DATABASES;"

edit My current work in progress wercker lamp template is available at https://github.com/levi-putna/php-cli-template

Request

I know wercker.com is relatively new, and doesn't have a lot of documentation. But I was hoping someone could provide me with an example of how to use mysql within a wercker test run.

Edit - Using Wercker Service

I have also tries connecting using a Wercker MariaBD service, it's not exactly MySQL so not a true test of my production environment, but should be good enough to get testing started.

I updates my wercker.yml to add a the mariadb service. Simplified my steps to just get the DB connection stuff working.

box: consynki/yii2

services:
    - id: mariadb

      # your credentials for Docker Hub
      username: $USERNAME
      password: $PASSWORD
      tag: latest

      # set the required environment variable
      env:
        MYSQL_ROOT_PASSWORD: mypassword
        MYSQL_DATABASE: test_database
        MYSQL_USER: admin
        MYSQL_PASSWORD: test123

build:

  steps:

    - script:
        name: Create database
        code: |-
          mysql -h $MARIADB_PORT_3306_TCP_ADDR -P $MARIADB_PORT_3306_TCP_PORT -u $MYSQL_USER -p test123 -v
          mysql -h $MARIADB_PORT_3306_TCP_ADDR -P $MARIADB_PORT_3306_TCP_PORT -u $MYSQL_USER -p test123 -e "SHOW DATABASES;"

I still seem to be getting the same error as my original approach. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I'm also seeing some additional log messages after the wercker steps exit.

WARNING Box container has already stopped.
Initializing database
2016-03-01 22:51:00 140429748197312 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 51 ...
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2016-03-01 22:51:00 140429748197312 [Note] InnoDB: Database physically writes the file full: wait...
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2016-03-01 22:51:01 140429748197312 [Warning] InnoDB: New log files created, LSN=45883
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Doublewrite buffer not found: creating new
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Doublewrite buffer created
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:01 140429748197312 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Foreign key constraint system tables created
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Creating tablespace and datafile system tables.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Tablespace and datafile system tables created.
2016-03-01 22:51:01 140429748197312 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:01 140429748197312 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 0
2016-03-01 22:51:01 140428963710720 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-03-01 22:51:03 140216194230208 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 80 ...
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:03 140216194230208 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:03 140216194230208 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 1616799
2016-03-01 22:51:04 140215410722560 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-03-01 22:51:06 140659046377408 [Note] /usr/sbin/mysqld (mysqld 10.1.12-MariaDB-1~jessie) starting as process 109 ...
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: The InnoDB memory heap is disabled
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Memory barrier is not used
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using Linux native AIO
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Using SSE crc32 instructions
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Completed initialization of buffer pool
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Highest supported file format is Barracuda.
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: 128 rollback segment(s) are active.
2016-03-01 22:51:06 140659046377408 [Note] InnoDB: Waiting for purge to start
2016-03-01 22:51:06 140659046377408 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.28-76.1 started; log sequence number 1616809
2016-03-01 22:51:06 140658262116096 [Note] InnoDB: Dumping buffer pool(s) not yet started
ERROR: 1049  Unknown database 'test'
2016-03-01 22:51:06 140659046377408 [ERROR] Aborting

I'm not sure if Wercker actually executes the final CMD in the Docker file before it runs the Wercker steps. If I manually run the final docker command, in my case /run.sh (The script that starts the database). I can connect to mysql within Wercker. Not sure if this is a real fix, or just a workaround. — Levi Putna, Feb 25 '16 at 03:34

score 2 · Answer 1 · answered Feb 28 '16 at 15:25

2

You can use a ready made MySQL server prepared by Wercker instead of setting up your own. This will ensure it persists between calls.

Check out this document.

answered Feb 28 '16 at 15:25

Tal

7,827
6
38
61

This seems to be a better approach. I stuck with the MariaBD form the example, not exactly MySQL but should be good to test. I seem to be getting the same error when trying to connect from within the wercker.ylm. I'm also not sure how I should be connecting to the service from within php. Should I still be using localhost port 3306? – Levi Putna Mar 01 '16 at 23:04
when using that service, the following environment variables become available to you: `$MARIADB_PORT_3306_TCP_ADDR`, `$MARIADB_PORT_3306_TCP_PORT` – Tal Mar 02 '16 at 06:53

Levi Putna · Accepted Answer · 2016-03-02T23:32:19.323

Explanation

Ok, I managed to get a database connection working using @Tal's suggestion. The Wercker docks only provide an example using MariaDB, luckily MySQL has a docker container that works almost exactly the same as the MariaDB version.

I added the new container as a service to my wercker.yml.

services:
  - id: mysql

    # your credentials for Docker Hub
    username: $USERNAME
    password: $PASSWORD
    tag: latest

    # set the required environment variable
    env:
      MYSQL_ROOT_PASSWORD: mypassword
      MYSQL_DATABASE: test_database
      MYSQL_USER: admin
      MYSQL_PASSWORD: test123

Then I was able to connect to the database within my Wercker steps using the environmental variables the MySQL service exposed.

  - script:
    name: Create database
    code: mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p$MYSQL_ENV_MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS test;"

I could also use the environmental variables within PHP to connect to the database.

class SampleTest extends PHPUnit_Framework_TestCase
{

    public function testSetupWorking()
    {
        $this->assertEquals(true, true);
        $this->assertFalse(false);
    }

    public function testDatabase()
    {

        $hostname = $_SERVER["MYSQL_PORT_3306_TCP_ADDR"];
        $username = 'root';
        $password = $_SERVER["MYSQL_ENV_MYSQL_ROOT_PASSWORD"];
        $database = $_SERVER["MYSQL_ENV_MYSQL_DATABASE"];

        try {
            $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
            $this->assertFalse(false);

        } catch (PDOException $e) {
            echo $e->getMessage();
            $this->assertFalse(true, $e->getMessage());
        }
    }

}

My complete wercker.yml looks like this, you can see my full template at https://github.com/levi-putna/php-cli-template. This is just an example, you would want to clean up the steps and the way PHP is connecting to the database, but its a good starting point.

Answer - Full wercker.yml

services:
        - id: mysql

          # your credentials for Docker Hub
          username: $USERNAME
          password: $PASSWORD
          tag: latest

          # set the required environment variable
          env:
            MYSQL_ROOT_PASSWORD: mypassword
            MYSQL_DATABASE: test_database
            MYSQL_USER: admin
            MYSQL_PASSWORD: test123

    box: consynki/yii2

    build:

      steps:

        - script:
          name: Show Enviroment
          code: printenv

        - script:
          name: Update enviroment dependencies
          code: |
              sudo DEBIAN_FRONTEND=noninteractive apt-get --no-install-recommends -y install curl php5-cli php5-curl git
              curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/bin/ --filename=composer
              cd $WERCKER_SOURCE_DIR

        - script:
            name: Install Composer dependencies
            code: |
              rm -rf ./vendor
              /usr/bin/composer install --no-interaction

        - script:
            name: Serve application
            code: php -S localhost:8000 >> /dev/null &

        - script:
            name: Create database
            code: mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p$MYSQL_ENV_MYSQL_ROOT_PASSWORD -e "CREATE DATABASE IF NOT EXISTS test;"

        - script:
            name: PHPUnit integration tests
            code: ./vendor/bin/phpunit --configuration phpunit.xml

`Create database` step assumes you have `mysql` available on your host — Sash, Apr 17 '16 at 16:31
@sash, I'm adding MYSQL as a part of the Wercker services step, so I expect that is should be available on the host, if not the `Create database` should fail and fail the entire test run. — Levi Putna, Apr 19 '16 at 12:31
@LeviPutna when adding the script "Create database" I get the following error: `mysql: command not found`, any idea why? I have addded the mysql service — MatayoshiMariano, Apr 18 '17 at 12:18

score 0 · Answer 3 · answered Apr 18 '17 at 13:58

I have acomplished to connect to mysql using the following configuration:

box: ruby:2.1.9
build:
    services:
      - id: mysql
        # your credentials for Docker Hub
        username: $USERNAME
        password: $PASSWORD
        tag: 5.5.55
        # set the required environment variable
        env:
          MYSQL_ROOT_PASSWORD: roo
          MYSQL_USER: testuser
          MYSQL_PASSWORD: testuser
          MYSQL_DATABASE: testdb
    steps:
      - bundle-install
      - rails-database-yml
      - script:
          name: echo ruby information
          code: |
              echo "ruby version $(ruby --version) running!"
              echo "from location $(which ruby)"
              echo -p "gem list: $(gem list)"
      - script:
          name: Set up db
          code: |
              touch db/schema.rb
              bundle exec rake db:migrate RAILS_ENV=test
      - script:
          name: rspec
          code: bundle exec rspec

Information about rails-database-yml step, can be found here