Setting up mysql 5.7 environment variables - Access denied for user (Laravel project)

mysql

#1

I have been trying to setup my build environment for a couple of hours and now hitting a wall with a

Access denied for user 'homestead'@'127.0.0.1' (using password: YES) error.

My config file currently looks like this

# PHP CircleCI 2.0 configuration file
#
# Check https://circleci.com/docs/2.0/language-php/ for more details
#
version: 2
jobs:
  build:
    docker:
      # specify the version you desire here
      - image: circleci/php:7.2-apache-stretch-node-browsers
      - image: circleci/mysql:5.7
        environment:
          MYSQL_ROOT_PASSWORD: homestead

      # Specify service dependencies here if necessary
      # CircleCI maintains a library of pre-built images
      # documented at https://circleci.com/docs/2.0/circleci-images/
      # - image: circleci/mysql:9.4

    steps:
      - checkout

      - run: sudo apt-get install -y libzip-dev
      - run: sudo docker-php-ext-install zip
      - run: sudo apt-get update
      - run: sudo apt-get install -y zlib1g-dev libicu-dev g++
      - run: sudo docker-php-ext-configure intl
      - run: sudo docker-php-ext-install intl
      - run: sudo docker-php-ext-install pdo_mysql

      # Download and cache dependencies
      - restore_cache:
          keys:
          - v1-dependencies-{{ checksum "composer.json" }}
          # fallback to using the latest cache if no exact match is found
          - v1-dependencies-

      - run: composer install -n --prefer-dist

      - save_cache:
          paths:
            - ./vendor
          key: v1-dependencies-{{ checksum "composer.json" }}

      # run tests!
      - run: ./vendor/bin/phpunit

And I’ve got some environment variables like so:

The ones above are from my Laravel .env file.

I’ve also tried adding

MYSQL_DATABASE: homestead
MYSQL_HOST: 127.0.0.1
MYSQL_ALLOW_EMPTY_PASSWORD: true

to the config.yml with no luck. I also tried adding all of it to the environment settings in the app.

Could someone lend a hand with this? Feeling very stupid at this point :slight_smile:


#2

Do you have a bootstrap or something in PHPUnit to create your database or run some migrations? I’m not understanding where the homestead database should be created - your server will not have any databases in it to start with every time your build runs.


#3

Yes, migrations are run in the app after phpunit starts running. Got a trait that migrates the database(s) before all tests start. And then each test is in a transaction.


#4

This feels like one of those things where readers don’t know what config/code to ask you for, but would be able to resolve if it were available in a shell console in front of them :wink:

Can you use an SSH console after your failing build to see if you can connect to MySQL from the command line?


#5

I actually can’t even run a mysql command while ssh-ed:

image


#6

Um, I think you need to install it first? :blush:

You need something like sudo apt-get install -i mysql-client (may need to tweak a bit). And remember that when you have an SSH session, you’re logged into the first container, which does not have any of the binaries in the second container.


#7

Could you clarify what the first and second container is? :slight_smile:


#8

Oh yeah, sure. It’s these:

docker:
  # specify the version you desire here
  - image: circleci/php:7.2-apache-stretch-node-browsers
  - image: circleci/mysql:5.7

In this configuration, you’ve asked the platform to run your steps in a container based on the first image, and then also spin up the other images. So your tests will run in a container based on the first image, and you will also have a separate container running based on the second image (and so on for any other images).


#9

Thanks! So there is no relationship between the two :thinking: Why would I need the second container then? If my tests are running in the first?

I ran

sudo apt-get install mysql-client

and got

I then continued with

sudo apt-get install mysql-server
service mysql restart

which resulted in

don’t know what I’m doing at this point :blush:


#10

Mostly that’s correct, see here. They are separate containers, but CircleCI merges their networking stacks together, so localhost is shared.

However, as you rightly say, you can indeed install MySQL in the first container and just run the one. Both approaches will work - use whatever you can get working, or whatever results in the fastest jobs.

If you drop the second container reference and just add a run step to install MySQL server, does that get you any further? Either way, you will need to get an SSH session to see whether your migrations have created a database for you. Are there logs for your migrations?


#11

DId that. It has gotten me somewhere else:

SQLSTATE[HY000] [2002] Connection refused (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE')

#!/bin/bash -eo pipefail
./vendor/bin/phpunit
PHPUnit 7.0.2 by Sebastian Bergmann and contributors.

EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE  63 / 158 ( 39%)
EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE 126 / 158 ( 79%)
EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE                                158 / 158 (100%)

Time: 12.51 seconds, Memory: 102.00MB

There were 158 errors:

1) Tests\Unit\Forms\FormServiceTest::getFormForSubscription_FormCreatedForProduct_ReturnsAformOfType
Illuminate\Database\QueryException: SQLSTATE[HY000] [2002] Connection refused (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE')

/home/circleci/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
/home/circleci/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624
/home/circleci/project/vendor/laravel/framework/src/Illuminate/Database/Connection.php:333

Then while ssh-ed:

circleci@140f73468e43:~$ mysql --version
mysql  Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using rea                           dline 5.2
circleci@140f73468e43:~$ mysql -u homestead
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run                           /mysqld/mysqld.sock' (2 "No such file or directory")

Then this happened

circleci@140f73468e43:~/project$ sudo service mysql status
[info] MariaDB is stopped..
circleci@140f73468e43:~/project$ sudo service mysql start
[ ok ] Starting MariaDB database server: mysqld.
circleci@140f73468e43:~/project$ mysql -u homestead
ERROR 1698 (28000): Access denied for user 'homestead'@'localhost'
circleci@140f73468e43:~/project$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Also config file now looks like this:

      - image: circleci/php:7.2-apache-stretch-node-browsers
        environment:
          MYSQL_ROOT_PASSWORD: homestead

:slight_smile:


#12

Would you supply a new copy of your entire configuration file? I suspect it has had a number of updates, and I’ve lost track of the state of it :smiley_cat:

(Aside: would you provide your console logs as formatted text please? Images of text are not compatible with clipboards, screen-readers and search engines, and can make it harder to help. Thanks!)


#13

Note taken :slight_smile:

New config file:

# PHP CircleCI 2.0 configuration file
#
# Check https://circleci.com/docs/2.0/language-php/ for more details
#
version: 2
jobs:
  build:
    docker:
      # specify the version you desire here
      - image: circleci/php:7.2-apache-stretch-node-browsers
        environment:
          MYSQL_ROOT_PASSWORD: homestead

      # Specify service dependencies here if necessary
      # CircleCI maintains a library of pre-built images
      # documented at https://circleci.com/docs/2.0/circleci-images/
      # - image: circleci/mysql:9.4

    steps:
      - checkout

      - run: sudo apt-get install -y libzip-dev
      - run: sudo docker-php-ext-install zip
      - run: sudo apt-get update
      - run: sudo apt-get install -y zlib1g-dev libicu-dev g++
      - run: sudo docker-php-ext-configure intl
      - run: sudo docker-php-ext-install intl
      - run: sudo docker-php-ext-install pdo_mysql
      - run: sudo apt-get install mysql-server
      - run: sudo apt-get install mysql-client

      # Download and cache dependencies
      - restore_cache:
          keys:
          - v1-dependencies-{{ checksum "composer.json" }}
          # fallback to using the latest cache if no exact match is found
          - v1-dependencies-

      - run: composer install -n --prefer-dist

      - save_cache:
          paths:
            - ./vendor
          key: v1-dependencies-{{ checksum "composer.json" }}

      # run tests!
      - run: ./vendor/bin/phpunit

#14

Aha, I bet it’s this. From this search, I found this assistance.

The installer normally opens an interactive session to get a root password from you, but it will fail because an interactive shell is not available. You have to bypass that.

(Edit: running the separate MySQL container was not wrong, so it’s up to you which avenue to persist with. However, I suspect this fix will get MySQL running in the same container, so give it a try).


#15

So I should replace

sudo apt-get install mysql-server

with

sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password password your_password'
sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password your_password'
sudo apt-get -y install mysql-server

but not sure what to replace the root_password password your_password and root_password_again password with :thinking:

Something in the lines of

sudo debconf-set-selections <<< 'mysql-server mysql-server/homestead'
sudo debconf-set-selections <<< 'mysql-server mysql-server/homestead'
sudo apt-get -y install mysql-server

:thinking:


#16

No, I’d just set your root password to password. You can then use root and password in your settings to log onto the database. It does not have to be a good password since the database will only live for a few minutes.

So, something like:

sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password password password'
sudo debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password password'
sudo apt-get -y install mysql-server

#17

Does that mean that in the config I will replace this with password?


#18

Yep! Or, of course, you can use homestead in the other thing - as long as they match.


#19

so this is not some sort of placeholder?


#20

No, I think that’s a name for the property you’re specifying. It’s confusing, since there seem to be three things, and you only need two things for a key-value pair! But, hey ho, just try amending your_password as the instructions suggest, and let us know how you get on.