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

denitsa-cm · March 7, 2018, 8:58pm

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

halfer · March 7, 2018, 9:05pm

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.

denitsa-cm · March 7, 2018, 9:08pm

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.

halfer · March 7, 2018, 9:15pm

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

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

denitsa-cm · March 7, 2018, 9:25pm

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

halfer · March 7, 2018, 9:27pm

Um, I think you need to install it first?

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.

denitsa-cm · March 7, 2018, 9:29pm

Could you clarify what the first and second container is?

halfer · March 7, 2018, 9:31pm

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

denitsa-cm · March 7, 2018, 9:33pm

Thanks! So there is no relationship between the two 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

halfer · March 7, 2018, 9:48pm

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?

denitsa-cm · March 7, 2018, 10:01pm

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

halfer · March 7, 2018, 10:12pm

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

(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!)

denitsa-cm · March 7, 2018, 10:13pm

Note taken

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

halfer · March 7, 2018, 10:17pm

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

denitsa-cm · March 7, 2018, 10:22pm

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

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

halfer · March 7, 2018, 10:24pm

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

denitsa-cm · March 7, 2018, 10:26pm

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

halfer · March 7, 2018, 10:27pm

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

denitsa-cm · March 7, 2018, 10:27pm

so this is not some sort of placeholder?

halfer · March 7, 2018, 10:29pm

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.

Topic		Replies	Views
Connection refused MySQL Build Environment circle-yml	3	4124	November 27, 2018
Config CircleCI for mysql database Databases mysql , circle-yml	8	19096	May 22, 2019
MYSQL: SQLSTATE[HY000] [1045] Access denied for user 'forge'@'127.0.0.1' - Any suggestions? Databases docker , circle-yml	2	4996	June 18, 2018
Unable to connect to MySQL 5.7 and create database Databases	6	8935	May 3, 2019
Unable to connect to mysql - SQLSTATE[HY000] [2002] No such file or directory Build Environment mysql	2	7008	December 12, 2019

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

Related topics